Too many MySQL queries

Nextcloud version (eg, 20.0.5): 23.0.0
Operating system and version (eg, Ubuntu 20.04): Ubuntu 20.04
Apache or nginx version (eg, Apache 2.4.25): Apache 2.4.41
PHP version (eg, 7.4): 7.4

The issue you are facing:

Summary: Clicking on the Calendar button on the main bar seems to make 250 MySQL queries. Is this normal?

Details:

This is a new installation that I’m making on a development environment (on a docker container), with just one user, admin. I’m turning on MySQL query logging by adding these two lines in /etc/mysql/mysql.conf.d/mysqld.conf:

general_log_file        = /var/log/mysql/query.log
general_log             = 1

I then login on the site and click on the Calendar button on the main menu/bar. I actually ignore the first click, to ensure that caches are warm. While the (empty) calendar is showing, I click the Calendar button again.

Result: query.log indicates that about 250 queries are made on the second click.

I have Redis configured for caching and locking.

What I am actually trying to accomplish:

I’m investigating why the production server is very slow. It does have a very slow disk, but we are talking about only a handful of users and extreme delays that occasionally cause timeouts. I examined with iotop and saw that, under modest load (i.e. one or two users), MySQL is writing to the disk at an average rate of 0.5 M/s (with general_log off) and I’ve been trying to understand why. I then turned on general_log (in development) and saw the number of queries and it seemd strange to me.

Other details

Is this the first time you’ve seen this error? (Y/N): N/A; this is my first Nextcloud installation

The output of your Nextcloud log in Admin > Logging:

Warning	fulltextsearch	Exception while cronIndex: OCA\FullTextSearch\Exceptions\PlatformNotSelectedException - Admin have not selected any IFullTextSearchPlatform	
2022-02-08T18:25:02+0200
Warning	fulltextsearch	Exception while cronIndex: OCA\FullTextSearch\Exceptions\PlatformNotSelectedException - Admin have not selected any IFullTextSearchPlatform	
2022-02-08T18:10:01+0200
Warning	fulltextsearch	Exception while cronIndex: OCA\FullTextSearch\Exceptions\PlatformNotSelectedException - Admin have not selected any IFullTextSearchPlatform	
2022-02-08T17:55:01+0200
Warning	fulltextsearch	Exception while cronIndex: OCA\FullTextSearch\Exceptions\PlatformNotSelectedException - Admin have not selected any IFullTextSearchPlatform	
2022-02-08T17:40:01+0200
Warning	fulltextsearch	Exception while cronIndex: OCA\FullTextSearch\Exceptions\PlatformNotSelectedException - Admin have not selected any IFullTextSearchPlatform	
2022-02-08T17:25:02+0200

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

<?php
$CONFIG = array (
  'passwordsalt' => '[...]',
  'secret' => '[...]',
  'trusted_domains' => 
  array (
    0 => 'nextcloud.test',
  ),
  'datadirectory' => '/var/www/nextcloud.test/nextcloud/data',
  'dbtype' => 'mysql',
  'version' => '23.0.0.10',
  'overwrite.cli.url' => 'https://nextcloud.test/',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'mysql.utf8mb4' => true,
  'dbuser' => 'oc_admin',
  'dbpassword' => '[...]',
  'installed' => true,
  'default_phone_region' => 'GR',
  'instanceid' => 'ocl1sarrxn88',
  'memcache.local' => '\\OC\\Memcache\\Redis',
  'memcache.locking' => '\\OC\\Memcache\\Redis',
  'redis' => 
  array (
    'host' => 'localhost',
    'port' => 6379,
  ),
  'mail_from_address' => 'noreply',
  'mail_domain' => 'grnet.gr',
  'mail_smtpmode' => 'smtp',
  'mail_sendmailmode' => 'smtp',
  'mail_smtphost' => 'localhost',
  'mail_smtpport' => '25',
  'htaccess.RewriteBase' => '/',
);

The output of your Apache/nginx/system log in /var/log/____ (I include the log only from clicking the Calendar button once):

172.20.0.1 - - [08/Feb/2022:16:31:11 +0000] (1644337871193) "GET /apps/calendar/ HTTP/1.1" 200 11436 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:96.0) Gecko/20100101 Firefox/96.0" 0 443 nextcloud.test
172.20.0.1 - - [08/Feb/2022:16:30:51 +0000] (1644337851537) "GET /apps/logreader/poll?lastReqId=PI4tQ6cWZPswBxjcATBa HTTP/1.1" 200 887 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:96.0) Gecko/20100101 Firefox/96.0" 20 443 nextcloud.test
172.20.0.1 - - [08/Feb/2022:16:31:12 +0000] (1644337872188) "GET /index.php/apps/photos/service-worker.js HTTP/1.1" 200 6508 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:96.0) Gecko/20100101 Firefox/96.0" 0 443 nextcloud.test
172.20.0.1 - - [08/Feb/2022:16:31:12 +0000] (1644337872384) "GET /ocs/v2.php/apps/notifications/api/v2/notifications HTTP/1.1" 200 1500 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:96.0) Gecko/20100101 Firefox/96.0" 0 443 nextcloud.test
172.20.0.1 - - [08/Feb/2022:16:31:12 +0000] (1644337872394) "GET /ocs/v2.php/search/providers?from=%2Fapps%2Fcalendar%2FdayGridMonth%2Fnow HTTP/1.1" 200 1762 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:96.0) Gecko/20100101 Firefox/96.0" 0 443 nextcloud.test
172.20.0.1 - - [08/Feb/2022:16:31:12 +0000] (1644337872393) "PROPFIND /remote.php/dav/ HTTP/1.1" 207 1616 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:96.0) Gecko/20100101 Firefox/96.0" 0 443 nextcloud.test
172.20.0.1 - - [08/Feb/2022:16:31:12 +0000] (1644337872394) "PUT /apps/user_status/heartbeat HTTP/1.1" 200 1510 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:96.0) Gecko/20100101 Firefox/96.0" 0 443 nextcloud.test
172.20.0.1 - - [08/Feb/2022:16:31:12 +0000] (1644337872537) "PROPFIND /remote.php/dav/principals/users/admin/ HTTP/1.1" 207 1766 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:96.0) Gecko/20100101 Firefox/96.0" 0 443 nextcloud.test
172.20.0.1 - - [08/Feb/2022:16:31:12 +0000] (1644337872602) "PROPFIND /remote.php/dav/calendars/admin/ HTTP/1.1" 207 2315 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:96.0) Gecko/20100101 Firefox/96.0" 0 443 nextcloud.test
172.20.0.1 - - [08/Feb/2022:16:31:12 +0000] (1644337872727) "REPORT /remote.php/dav/calendars/admin/personal/ HTTP/1.1" 207 935 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:96.0) Gecko/20100101 Firefox/96.0" 0 443 nextcloud.test
172.20.0.1 - - [08/Feb/2022:16:31:12 +0000] (1644337872786) "REPORT /remote.php/dav/calendars/admin/personal/ HTTP/1.1" 207 935 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:96.0) Gecko/20100101 Firefox/96.0" 0 443 nextcloud.test
172.20.0.1 - - [08/Feb/2022:16:31:13 +0000] (1644337873358) "GET /index.php/apps/photos/service-worker.js HTTP/1.1" 200 6508 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:96.0) Gecko/20100101 Firefox/96.0" 0 443 nextcloud.test

I took a look at this and can confirm it. (Well, at least that there are a lot of requests, my nc database is way smaller than my mysql buffer, therefore its in memory anyway)

But, at least it seems to me that a lot of requests are related to Deck and Circles, are you also using those two apps?

Regarding your Warnings in the Logfile, it seems you have the fulltext search app enabled but not configured.

Thanks!

Yes, I have Deck and Circles installed. Uninstalling them seems to reduce the number of queries for Calendar from about 250 to 190. It isn’t dramatic, but I start getting a feeling about why there are so many queries, and about the fact that it’s probably normal.

Regarding the performance issues I’m having, I’ll take a look at increasing the innodb buffer (although I don’t think this is part of the problem, because I have so much RAM that the OS has practically cached the entire disk, but obviously at a lower level than MySQL would), and at increasing write caching (I’ll look for something equivalent to PostgreSQL’s synchronous_commit).