Since update to 13 massive increase of mysql queries

[HeikoBe]

Nextcloud version (eg, 12.0.2): 13.0.0
Operating system and version (eg, Ubuntu 17.04): Ubuntu 16.04.04 LTS
Apache or nginx version (eg, Apache 2.4.25): Apache 2.4.18-2
PHP version (eg, 7.1): 7.0.25

The issue you are facing:
Since I updated from NC 11 to NC 13 I can see a massive increase of mysql queries on the server, which lead to an increase of the server’s load.
Running NC 11 there were 400 queries per second. Now with Nextcloud 13 there are more than 1700 queries per second. There are no error messages in the log and also the apache log does not show any errors.

Are there any major changes in database handling since version 11 which could be responsible for this behavior?

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

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

<?php
$CONFIG = array (
  'instanceid' => 'abcdefg',
  'passwordsalt' => '',
  'secret' => '',
  'trusted_domains' => 
  array (
    0 => 'XXX.XX',
    4 => '*.xxx.xx',
  ),
  'datadirectory' => '/data/cloud/data',
  'overwrite.cli.url' => 'https://xxx.xx',
  'dbtype' => 'mysql',
  'version' => '13.0.0.14',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'user',
  'dbpassword' => 'XXXXXXXXXXX',
  'default_language' => 'de_DE',
  'installed' => true,
  'loglevel' => 1,
  'logdateformat' => 'F d, Y H:i:s',
  'log_rotate_size' => 104857600,
  'mail_from_address' => 'admin',
  'mail_smtpmode' => 'sendmail',
  'mail_domain' => 'mail.domain',
  'preview_libreoffice_path' => '/usr/bin/libreoffice',
  'appstore.experimental.enabled' => true,
  'maintenance' => false,
  'skeletondirectory' => '',
  'theme' => '',
  'enable_certificate_management' => true,
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'memcache.distributed' => '\\OC\\Memcache\\Memcached',
  'memcached_servers' => 
  array (
    0 => 
    array (
      0 => 'localhost',
      1 => 11211,
    ),
  ),
  'trashbin_retention_obligation' => 'disabled',
  'version_retention_obligation' => 'disabled',
  'activity_expire_days' => '4748',
  'singleuser' => false,
  'logtimezone' => 'Europe/Berlin',
);

[tflidd]

Did you skip NC 12? Between NC 11 and NC 12 there should be some improvement.

Judging from your config, you don’t use redis as file-locking cache which means all the locking is handled via mysql which is much slower. And it is very likely that this is the culprit for so many queries:
https://docs.nextcloud.com/server/13/admin_manual/configuration_server/caching_configuration.html?highlight=redis#id3

[HeikoBe]

I’ve updated using the update assistent and ‘occ upgrade’ from 11.0.4 to 12.0.5 and immediately after that to NC 13 without doing any tests in NC 12.

Right after the update the mysql-queries rose from an average of 330 queries per second to an average of 1300 queries per second. No new users, no new synchronization, I only updatet the server.

You are right I didn’t use redis so far but I configured it two days ago.
That didn’t change much.
Further investigation showed that most of the quries are triggered by desktop sync clients.
The apache log says that there are also older versions of desktop sync clients (2.2.4) amongst them.

Could this be the problem ?

[tflidd]

If you have a large sharing table, this new index can help to speed it up:

Speed up sharing in NC 13

In NC13, create an additional index in the sharing table for performance improvements (put your setup in maintenance mode before): sudo -u www-data php occ db:add-missing-indice There will be a mechanism to check this in future versions to notify users about it. It was not in the default update routine because it can take much time and should be executed in a terminal where no timeouts apply. ref: https://docs.nextcloud.com/server/13/admin_manual/maintenance/upgrade.html#upgrading-to-nextclou…

I don’t really know why the number of queries got up that much, the clients should be able to do this, however, it would be better to run newer versions of it.

@rullzer can you help how to debug this problem?

[Schmu]

There is an advice in the docs to profit from performance improvements by making changes to the DB via occ command:
https://docs.nextcloud.com/server/13/admin_manual/maintenance/upgrade.html?highlight=convert%20filecache%20bigint#manual-steps-during-upgrade

Did you follow these steps? At least I hope, this brings some improvement for you.

[rullzer]

going from 330 to 1300 queries per second is way to much.

If no external parameters changed really then there is no reason for a >300% increase.

You could try to enable the query logger for a short time and see what kind of queries are exectued.

[HeikoBe]

Schmu:
I missed this, I will try this as soon as possible.

rullzer:
If the reccomendations of Schmu don’t help then the next step will be to analyze the sql queries.

[HeikoBe]

Alright I made the changes from here:
https://docs.nextcloud.com/server/13/admin_manual/maintenance/upgrade.html?highlight=convert%20filecache%20bigint#manual-steps-during-upgrade

Unfortunately that didn’t change anything.

Then I ran mysqltuner and tuned the databases as proposed from the script, which had also no effect.

Right now I’m checking the queries and apache accesses again.
I don’t have any logs of the mysql queries from version 11 therefore I can’t compare them, but maybe the apache accesses can help.

Here’s a graph which shows the increase:

The left part of the graph (Thu and Fri) shows the number of queries before the update to NC 13. Right after the update the number of queries rose. On Monday I enabled query caching which helped a little but still didn’t fix this issue.
The system load also rose from an average of 0.4 to an average of 1.2.

[HeikoBe]

A comparison of the apache log before and after the update showed some interesting numbers:

Total apache accesses before the update average 10.000 per hour, after the update between 11.000 and up to 82.000 per hour.
But even more interesting is how many accesses come from the server itself.
Before the Update there were like 24 % - 35 % of the entries in the apache log from the ip address of the server, now after the update the percentage is at an average of 50 % and sometimes up to 71 %.

[HeikoBe]

Finally I have found out what caused the problem.

The high load was produced by federated shares used on the same host and re-sharing them and re-sharing them and then sync them with the desktop client.

The idea was to share a structure of folders to users so that they could re-share these folders to other users and of course use them for their own needs.
But we didn’t want users to see who shared this structure with them nor did we want them to see to whom this structure was shared to.
We found out that in version 11 this was possible by using federated shares on the same server.
True, this is not what federation is meant for, but this was the way to go in our environment and it fulfilled all our needs.

So we used federated shares, shared them with the local users and they reshared them and reshared them again and so on.
Most of the users also use the desktop-client to sync their data to their client/server.
All worked well until we updated to version 13.

To find the reason of the high load I analyzed the apache-log and the mysql-query-log and found out that most of the mysql queries where caused by users like ‘Mbf5bYbgHCIfqlQ’.
Then I have searched the database for this ‘user’ and found out that this is a share-token in table oc_share_external.

In the end we changed all the federated shares which where used on the same server to the newly implemented group-sharing and suddenly the load decreased dramatically.
Since then the load returned to normal.

In short using federated shares with large folder structures on one server, reshare them and synchronize these shares with the desktop-client causes high mysql-load.

[tflidd]

@rullzer Do you want an issue on github? Could be something to check before running updates. Also the question if this is a wanted behavior.

Thanks for the investigations and the feedback @HeikoBe