High SQL load since Update

ℹ️ Support
,
1

Nextcloud version (eg, 14.0.4): 14.0.4
Operating system and version :Debian 9.5
Apache or nginx version (eg, Apache 2.4.25): 2.4.25
PHP version (eg, 7.1): 7.0

Since the update to version 14.0.4 we see very high CPU load caused by MYSQL daemon.

“mysqladmin processlist” shows the following result repeatedly after the background jobs start:

updating | DELETE FROM oc_jobs WHERE (class = ‘OCA\LookupServerConnector\BackgroundJobs\RetryJob’) AND ( |

The table “oc_jobs” has currently 228303 (and growing) rows with similar entries:

| 961522 | OCA\LookupServerConnector\BackgroundJobs\RetryJob | {“dataArray”:{“message”:{“data”:{“federationId”:“SOMEUSER”},“type”:“lookupserver”,“signer”:“SOMEUSER”,“timestamp”:1544839972},“signature”:“SIGNATURE STRING”},“retryNo”:0}

I also repeatedly see this query:
Creating sort index | SELECT * FROM oc_jobs WHERE (reserved_at <= 1545192635) AND (last_checked <= 1545235835) ORDER

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

I’d appreciate if someone could help me with this :slight_smile:

2

How did you configure cronjobs, do they run as as system cronjob?

You can as well run the cronjobs from the command line, so you see potential errors and if there are large jobs, you are sure they finish properly. Go inside the Nextcloud folder on your server (e.g. /var/www/nextcloud) and run:

sudo -u www-data php -f cron.php

3

Hi,

thanks for your reply. Yes the background jobs run as system cron.

As you suggested I ran the command above. It took quite some time (~20 minutes) but eventually finished without any command line output.

During the execution of the task, the CPU usage of the mysql process was around 95-100% and now seems to stay here.

4

Perhaps there were just many things to clean up. If it keeps up taking so much time, you should perhaps have a closer look, what jobs takes so much resources. Do you use redis for file locking?

5

Hi tflidd,

unfortunately the CPU load has been (and still is) very high since a few feeks. (Probably after upgrading from 13.x to 14.x)
We do not use redis for file locking, however we use ist as memcache.

To me it looks like a background job is in some kind of loop.

Here is the full config by the way:

<?php
$CONFIG = array (
  'instanceid' => 'replaced',
  'passwordsalt' => 'replaced',
  'secret' => 'replaced',
  'trusted_domains' =>
  array (
    0 => 'replaced',
    1 => 'replaced',
    2 => 'replaced',
  ),
  'datadirectory' => 'replaced',
  'overwrite.cli.url' => 'replaced',
  'dbtype' => 'mysql',
  'version' => '14.0.4.2',
  'dbname' => 'replaced',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'mysql.utf8mb4' => true,
  'dbuser' => 'nextcloud',
  'dbpassword' => 'replaced',
  'installed' => true,
  'mail_smtpmode' => 'smtp',
  'mail_smtpauthtype' => 'LOGIN',
  'mail_from_address' => 'noreply',
  'mail_domain' => 'replaced',
  'mail_smtpauth' => 1,
  'mail_smtpport' => '25',
  'mail_smtphost' => 'replaced',
  'mail_smtpname' => 'replaced',
  'mail_smtppassword' => 'replaced',
  'ldapIgnoreNamingRules' => false,
  'ldapProviderFactory' => '\\OCA\\User_LDAP\\LDAPProviderFactory',
  'allowZipDownload' => true,
  'forcessl' => true,
  'remember_login_cookie_lifetime' => 86400,
  'session_lifetime' => 43200,
  'trashbin_retention_obligation' => '14, 30',
  'memcache.local' => '\\OC\\Memcache\\Redis',
  'redis' =>
  array (
    'host' => 'localhost',
    'port' => 6379,
  ),
  'share_folder' => '_Mit_mir_geteilt',
  'maintenance' => false,
  'theme' => '',
  'loglevel' => 2,
  'updater.release.channel' => 'stable',
);
6

You have already redis installed, so you can use it for file-locking as well (it takes a lot of load from mysql):
https://docs.nextcloud.com/server/14/admin_manual/configuration_server/caching_configuration.html#id3

'memcache.locking' => '\OC\Memcache\Redis',

Besides of that, you can check the cache settings of mysql…

1 Like
7

I’ve got the same problem here.

It looks like lookup_server_connector tries to update user data on lookup.netcloud.com. It hooks “User changed” and it looks like it’s also triggered if users synced from LDAP. Got around 300k entries in oc_jobs here (udated some days ago to NC14). The lookup.nextcloud.com server seems to be down.

All federation options are set inactive in admistrative options here. Why it is syncing ? “occ app:disable” doesn’t work für lookup_server_connector, so I removed the folder in apps and deleted all entries of the LookupServerConnector class in oc_jobs.

Filed a bug report: https://github.com/nextcloud/server/issues/13396

1 Like
8

Just wanted to add that even it the issue is said to be solved in NC16 and that the fix is backported to NC15, I still had this problem with V16.0.1. Not sure if I should report it ?

Anyway your fix worked for me too @afleischer, thank you for sharing it. It’s been a plague for one year.

9

Yes, if the fix does not help you, open a new issue.