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
Issue:
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))
{main}
The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):
<?php
$CONFIG = array (
'instanceid' => 'REMOVED',
'passwordsalt' => 'REMOVED',
'secret' => 'REMOVED',
'trusted_domains' =>
array (
0 => '192.168.1.101',
1 => 'REMOVED',
),
'datadirectory' => '/var/www/nextcloud/data',
'overwrite.cli.url' => 'https://REMOVED',
'dbtype' => 'mysql',
'version' => '13.0.5.2',
'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',
'auth.bruteforce.protection.enabled' => 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:
[client-server]
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
[mysqld]
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=1
transaction-isolation = READ-COMMITTED
query_cache_size = 50000000
query_cache_type = on
Update:
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.