MySQL consuming lots of CPU, some long queries running against oc_filecache

Host information:
Nextcloud version: 13.0.5
Operating system and version: Debian 9.3
Apache or nginx version: nginx 1.10.3
PHP version: 7.0.30
Database: MariaDB 10.1.26

For quite some time now mysql process is consuming a lot of CPU. It starts after some time after boot (probably by a cron job), and as far as I saw never stops consuming it.

Running mytop during such CPU usage shows a dozen or so queries like this:

     8985 nextclou       localhost nextcloud   1194   0.0  Query    Sending data SELECT `fileid`, `storage`, `path`, `parent`, `name`, `mimetype`, `storage_mtime`, `mimepart`, `size`, `mtime`, `encrypted`, `etag`, `permissions`, `checksum` FROM `oc_filecache` WHERE `storage` = '3' AND `name` COLLATE utf8mb4_general_ci LIKE 'stderr-75.v%.d1550139691'
     9275 nextclou       localhost nextcloud   1126   0.0  Query    Sending data SELECT `fileid`, `storage`, `path`, `parent`, `name`, `mimetype`, `storage_mtime`, `mimepart`, `size`, `mtime`, `encrypted`, `etag`, `permissions`, `checksum` FROM `oc_filecache` WHERE `storage` = '3' AND `name` COLLATE utf8mb4_general_ci LIKE 'stderr-73.v%.d1550139691'
     6857 nextclou       localhost nextcloud   1100   0.0  Query    Sending data SELECT `fileid`, `storage`, `path`, `parent`, `name`, `mimetype`, `storage_mtime`, `mimepart`, `size`, `mtime`, `encrypted`, `etag`, `permissions`, `checksum` FROM `oc_filecache` WHERE `storage` = '3' AND `name` COLLATE utf8mb4_general_ci LIKE 'stderr-77.v%.d1550139691'
     1289 nextclou       localhost nextcloud   1019   0.0  Query    Sending data SELECT `fileid`, `storage`, `path`, `parent`, `name`, `mimetype`, `storage_mtime`, `mimepart`, `size`, `mtime`, `encrypted`, `etag`, `permissions`, `checksum` FROM `oc_filecache` WHERE `storage` = '3' AND `name` COLLATE utf8mb4_general_ci LIKE 'stderr-67.v%.d1550139689'

More details and my suspicions:
I have only one active user (myself) with two devices (phone and laptop) syncing files there.
I’m also having an external storage (local directory for that server) shared for all users.
My current suspicion is that some files at that storage are causing problems - I have symlinks and hardlinks on said storage, and I wonder whether those are causing some sort of weird loops.

I have no idea how to find what files are these queries looking for, though.

I tried looking for such files by running find /var/www/nextcloud/data -name 'stderr-*', but found nothing.

Right now I’m running an occ files:scan --all hoping that would help fix the issue, but it didn’t finish yet.

Is this the first time you’ve seen this error?
As stated above, I started noticing this a few days ago (it might have been unnoticed a week or so).
I didn’t reconfigure anything at the time it started. I’ve enabled query caching in MariaDB yesterday, and it lessened the load slightly (previously it tried to consume all CPU it could, now it seem to be staying about 70-100% of one core).

Steps to replicate it:
Unknown. I just start the server and wait, it eventually happens.

The output of your Nextcloud log in Admin > Logging:
Here’s the topmost error, previous log entry seems to be related to me rebooting the server.

Error	core	Error while running background job (Doctrine\DBAL\Exception\DriverException): An exception occurred while executing 'DELETE FROM `oc_share` WHERE `item_type` in ('file', 'folder') AND NOT EXISTS (SELECT `fileid` FROM `oc_filecache` WHERE `file_source` = `fileid`)': SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
OCA\Files_Sharing\DeleteOrphanedSharesJob ):
/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php - line 128: Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException('An exception oc...', Object(Doctrine\DBAL\Driver\PDOException))
/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php - line 1015: Doctrine\DBAL\DBALException driverExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDOMySql\Driver), Object(Doctrine\DBAL\Driver\PDOException), 'DELETE FROM `oc...', Array)
/var/www/nextcloud/lib/private/DB/Connection.php - line 216: Doctrine\DBAL\Connection->executeUpdate('DELETE FROM `oc...', Array, Array)
/var/www/nextcloud/apps/files_sharing/lib/DeleteOrphanedSharesJob.php - line 62: OC\DB\Connection->executeUpdate('DELETE FROM `oc...')
/var/www/nextcloud/lib/private/BackgroundJob/Job.php - line 61: OCA\Files_Sharing\DeleteOrphanedSharesJob->run(NULL)
/var/www/nextcloud/lib/private/BackgroundJob/TimedJob.php - line 55: OC\BackgroundJob\Job->execute(Object(OC\BackgroundJob\JobList), Object(OC\Log))
/var/www/nextcloud/cron.php - line 123: OC\BackgroundJob\TimedJob->execute(Object(OC\BackgroundJob\JobList), Object(OC\Log))

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

$CONFIG = array (
  'instanceid' => 'REMOVED',
  'passwordsalt' => 'REMOVED',
  'secret' => 'REMOVED',
  'trusted_domains' =>
  array (
    0 => '',
    1 => 'REMOVED',
  'datadirectory' => '/var/www/nextcloud/data',
  'overwrite.cli.url' => 'https://REMOVED',
  'dbtype' => 'mysql',
  'version' => '',
  'dbname' => 'REMOVED',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'REMOVED',
  'dbpassword' => 'REMOVED',
  'installed' => true,
  'ldapIgnoreNamingRules' => false,
  'ldapProviderFactory' => '\\OCA\\User_LDAP\\LDAPProviderFactory',
  'maintenance' => false,
  'mysql.utf8mb4' => true,
  'overwritehost' => 'REMOVED',
  'overwriteprotocol' => 'https',
  '' => false,
  'onlyoffice' =>
  array (
    'verify_peer_off' => true,
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'memcache.locking' => '\\OC\\Memcache\\Redis',
  'filelocking.enabled' => true,
  'redis' =>
  array (
    'host' => '/var/run/redis/redis.sock',
    'port' => 0,
    'timeout' => 0.0,
  'trashbin_retention_obligation' => 'auto, 7',
  'loglevel' => 3,
  'enable_previews' => true,
  'enabledPreviewProviders' =>
  array (
    0 => 'OC\\Preview\\Image',
    1 => 'OC\\Preview\\Movie',
    2 => 'OC\\Preview\\TXT',
  'trusted_proxies' =>
  array (
    0 => '192.168.REMOVED',
  'proxy' => 'REMOVED',

The output of your Apache/nginx/system log:
Seems irrelevant to this problem, nothing there except errors caused by server reboot.

MariaDB config:


# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

transaction-isolation = READ-COMMITTED

query_cache_size = 50000000
query_cache_type = on

occ files:scan --all finished, and but it still runs those long queries and keeps the load at least at 40%.
occ files:cleanup says it found no orphans.

I will be grateful for any debugging tips.

Well, it seems that turning on cache size was the key, server was able to chew throw all the queries and is now quiet for two days… I’ll keep looking for some abnormal behaviour.

How did you do ?
Same here.