Upgrade to Nextcloud 23.0.7 very, very slow

Nextcloud version (eg, 20.0.5): 23.0.7
Operating system and version (eg, Ubuntu 20.04): Debian 4.19.67-2
Apache or nginx version (eg, Apache 2.4.25): nginx/1.14.2
PHP version (eg, 7.4): 7.3

The issue you are facing:
Since upgrading, Nextcloud runs very, very slowly.

A simple opening of the home directory opens the following urls (amongst many others - these are the slowest) - times in seconds after each one:

  1. ocs/v2.php/apps/text/workspace?path=%2F (10.25s)
  2. /remote.php/dav/files/ddat/ (9.9s)

Is this the first time you’ve seen this error? (Y/N): Y

The output of your Nextcloud log in Admin > Logging:

There is nothing in the nextcloud log for the time the page load was happening.

The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):

$CONFIG = array (
  'passwordsalt' => 'removed',
  'secret' => 'removed',
  'trusted_domains' => 
  array (
    0 => 'localhost',
    1 => 'removed',
    2 => 'removed',
  ),
  'datadirectory' => '/data/nextcloud/ncdata',
  'dbtype' => 'mysql',
  'version' => '23.0.7.2',
  'overwrite.cli.url' => 'http://localhost',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'nextcloud',
  'dbpassword' => 'removed',
  'installed' => true,
  'instanceid' => 'oco7zw7q011j',
  'activity_expire_days' => 1,
  'memcache.locking' => '\\OC\\Memcache\\Redis',
  'redis' => 
  array (
    'host' => '/var/run/redis/redis-server.sock',
    'port' => 0,
    'timeout' => 0.0,
    'password' => '',
  ),
  'memcache.distributed' => '\\OC\\Memcache\\Redis',
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'mail_from_address' => 'ddat+cloud',
  'mail_smtpmode' => 'smtp',
  'mail_sendmailmode' => 'smtp',
  'mail_domain' => 'removed',
  'mail_smtphost' => '127.0.0.1',
  'mail_smtpport' => '25',
  'maintenance' => false,
  'default_locale' => 'en_GB',
  'default_language' => 'en-GB',
  'defaultapp' => 'files',
  'loglevel' => 2,
  'updater.release.channel' => 'stable',
  'theme' => '',
  'app_install_overwrite' => 
  array (
    0 => 'calendar',
    1 => 'socialogin',
  ),
  'simpleSignUpLink.shown' => false,
  'sort_groups_by_name' => true,
  'mysql.utf8mb4' => true,
);

The output of your Apache/nginx/system log in /var/log/____:

Nothing here for the time period

Output errors in nextcloud.log in /var/www/ or as admin user in top right menu, filtering for errors. Use a pastebin service if necessary.

Nothing here

We have 5026 users (all with quota 0 and no files), and 772 group folders (the users each belong to 1 or more groups, where they can access files).

We are running on a (virtual) server assigned 16 cpus and 48GB RAM.

We are using nginx. The config file is:

upstream php-handler {
    server unix:/var/run/php/php7.3-fpm.sock;
}

server {
    listen 443      ssl http2;
    server_name cloud.removed;
    error_log /var/log/nginx/cloud.error.log;
    access_log /var/log/nginx/cloud.access.log;
    ssl_session_cache       builtin:1000 shared:SSL:10m;
    ssl_protocols   TLSv1 TLSv1.1 TLSv1.2;
    ssl_ciphers     HIGH:!aNULL:!eNULL:!EXPORT:!CAMELLIA:!3DES:!MD5:!PSK:!RC4;
    ssl_prefer_server_ciphers on;

    add_header Strict-Transport-Security "max-age=31536000; includeSubDomains" always;
    error_page 502 503 /custom_50x.html;
    location = /custom_50x.html {
        root /var/www/html;
        internal;
    } 
    client_max_body_size 512M;
    fastcgi_buffers 64 64k; 
    fastcgi_buffer_size 64k;

    gzip on;
    gzip_vary on;
    gzip_comp_level 4;
    gzip_min_length 256;
    gzip_proxied expired no-cache no-store private no_last_modified no_etag auth;
    gzip_types application/atom+xml application/javascript application/json application/ld+json application/manifest+json application/rss+xml application/vnd.geo+json application/vnd.ms-fontobject application/x-font-ttf application/x-web-app-manifest+json application/xhtml+xml application/xml font/opentype image/bmp image/svg+xml image/x-icon text/cache-manifest text/css text/plain text/vcard text/vnd.rim.location.xloc text/vtt text/x-component text/x-cross-domain-policy;


    add_header Referrer-Policy                      "no-referrer"   always;
    add_header X-Content-Type-Options               "nosniff"       always;
    add_header X-Download-Options                   "noopen"        always;
    add_header X-Frame-Options                      "SAMEORIGIN"    always;
    add_header X-Permitted-Cross-Domain-Policies    "none"          always;
    add_header X-Robots-Tag                         "none"          always;
    add_header X-XSS-Protection                     "1; mode=block" always;

    fastcgi_hide_header X-Powered-By;

    root /data/nextcloud/html;

    index index.php index.html /index.php$request_uri;

    expires 1m;

    location = / {
        if ( $http_user_agent ~ ^DavClnt ) {
            return 302 /remote.php/webdav/$is_args$args;
        }
    }

    location = /robots.txt {
        allow all;
        log_not_found off;
        access_log off;
    }

    location ^~ /.well-known {

        rewrite ^/\.well-known/host-meta\.json  /public.php?service=host-meta-json  last;
        rewrite ^/\.well-known/host-meta        /public.php?service=host-meta       last;
        rewrite ^/\.well-known/webfinger        /public.php?service=webfinger       last;
        rewrite ^/\.well-known/nodeinfo         /public.php?service=nodeinfo        last;

        location = /.well-known/carddav     { return 301 /remote.php/dav/; }
        location = /.well-known/caldav      { return 301 /remote.php/dav/; }

        try_files $uri $uri/ =404;
    }

    location ~ ^/(?:build|tests|config|lib|3rdparty|templates|data)(?:$|/)  { return 404; }
    location ~ ^/(?:\.|autotest|occ|issue|indie|db_|console)              { return 404; }

    location ~ \.php(?:$|/) {
        fastcgi_split_path_info ^(.+?\.php)(/.*)$;
        set $path_info $fastcgi_path_info;

        try_files $fastcgi_script_name =404;

        include fastcgi_params;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        fastcgi_param PATH_INFO $path_info;
        fastcgi_param HTTPS on;

        fastcgi_param modHeadersAvailable true;         # Avoid sending the security headers twice
        fastcgi_param front_controller_active true;     # Enable pretty urls
        fastcgi_pass php-handler;

        fastcgi_intercept_errors on;
        fastcgi_request_buffering off;
    }

    location ~ \.(?:css|js|svg|gif)$ {
        try_files $uri /index.php$request_uri;
        expires 6M;         # Cache-Control policy borrowed from `.htaccess`
        access_log off;     # Optional: Don't log access to assets
    }

    location ~ \.woff2?$ {
        try_files $uri /index.php$request_uri;
        expires 7d;         # Cache-Control policy borrowed from `.htaccess`
        access_log off;     # Optional: Don't log access to assets
    }

    location / {
        try_files $uri $uri/ /index.php$request_uri;
    }
    ssl_certificate /etc/letsencrypt/live/cloud.removed/fullchain.pem; # managed by Certbot
    ssl_certificate_key /etc/letsencrypt/live/cloud.removed/privkey.pem; # managed by Certbot

}
server {
	listen 80;
	listen [::]:80;
	server_name cloud.removed;
	root /var/www/html;
        return 301 https://$host$request_uri;
}

Any clues where I should look for the problem? Any idea who we could employ to fix it?

Hello nikkilocke, welcome back on the forums. You’re running quite a large instance. If you get an enterprise licence from nextcloud.com the support team can probably help you

I am afraid I don’t have EUR 250,000 per year in my budget!

I could probably afford a thousand or two for a consultant to come in and tune the installation.

I have a testing installation of my Nextcloud server (with the same data on it). Just out of interest, I turned on query logging, and logged in (I was the only person using this server).

The login process made 57,000 queries, just to get me logged in, and the Files page displayed.

This is a serious issue with Nextcloud. Why on earth does it have to make 57 thousand queries to the database to display a single page!

Worse, most of the queries are of the form

SELECT `path` FROM `oc_filecache` WHERE (`storage` = 8) AND (`fileid` = 806913)

and each one is repeated 231 times!