Nextcloud version (eg, 18.0.2): 20.0.0
Operating system and version (eg, Ubuntu 20.04): Ubuntu Server 20.04.1
Apache or nginx version (eg, Apache 2.4.25): Apache 2.4.41-4ubuntu3.1
PHP version (eg, 7.1): php 7.4
I am seeing very high disk writes (as well as high CPU usage) for sustained periods by MySQL. Using netdata, I can pin the source of these high disk writes down to MySQL running on my Nextcloud virtual machine.
These high disk writes seem to begin whenever Nextcloud scans my external storage (which was set on a cron job as user www-data: */15 * * * * /usr/bin/php -f /var/www/nextcloud/occ files:scan --all). The external storage is certainly large, but I only started having this problem recently (I can’t be sure when exactly), whereas I’ve been relying on this file scanning cron job for years and there hasn’t been any sudden increase in the size of the external storage.
These disk writes are so high and sustained that they caused me to have to replace an SSD before I knew this was happening. They burned through 7% of the lifetime of the replacement SSD within a week, before I found the source of the excess writes and shut down the VM.
I have now spun up a brand new VM and now have Nextcloud running on a completely new installation of Ubuntu Server 20.04.1 with Apache and MySQL installed from Ubuntu repositories. The VM isn’t used for anything other than Nextcloud. However, I’m seeing exactly the same issue again.
I rely on the cron job to scan my external storages so that apps (particularly the desktop sync apps) work properly. Without it, files don’t sync until after I log in on the web, which I don’t usually do.
Any idea why I would be getting this, and any workarounds? Thanks in advance!
Is this the first time you’ve seen this error? (Y/N): Y
Steps to replicate it:
- Install nextcloud with apache2 and mysql
- Set up large external storage
- Set up cron job to scan files (*/15 * * * * /usr/bin/php -f /var/www/nextcloud/occ files:scan --all)
- Install netdata
- Wait for cron job to run
- Using netdata, see very high, sustained disk writes originating from mysql every time the cron job runs
The output of your Nextcloud log in Admin > Logging:
(...)
{"reqId":"gIBIC3ynarEkSuPtKARM","level":3,"time":"2020-10-13T06:50:02+00:00","remoteAddr":"","user":"--","app":"core","method":"","url":"--","message":{"Exception":"Doctrine\\DBAL\\Exception\\DeadlockException","Message":"An exception occurred while executing 'DELETE FROM `oc_file_locks` WHERE `ttl` < ?' with params [1602571802]:\n\nSQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction","Code":0,"Trace":[{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php","line":169,"function":"convertException","class":"Doctrine\\DBAL\\Driver\\AbstractMySQLDriver","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php","line":149,"function":"wrapException","class":"Doctrine\\DBAL\\DBALException","type":"::"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php","line":1071,"function":"driverExceptionDuringQuery","class":"Doctrine\\DBAL\\DBALException","type":"::"},{"file":"/var/www/nextcloud/lib/private/DB/Connection.php","line":214,"function":"executeUpdate","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/Lock/DBLockingProvider.php","line":277,"function":"executeUpdate","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/apps/files/lib/BackgroundJob/CleanupFileLocks.php","line":58,"function":"cleanExpiredLocks","class":"OC\\Lock\\DBLockingProvider","type":"->"},{"file":"/var/www/nextcloud/lib/private/BackgroundJob/Job.php","line":52,"function":"run","class":"OCA\\Files\\BackgroundJob\\CleanupFileLocks","type":"->"},{"file":"/var/www/nextcloud/lib/private/BackgroundJob/TimedJob.php","line":59,"function":"execute","class":"OC\\BackgroundJob\\Job","type":"->"},{"file":"/var/www/nextcloud/cron.php","line":126,"function":"execute","class":"OC\\BackgroundJob\\TimedJob","type":"->"}],"File":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php","Line":34,"Previous":{"Exception":"Doctrine\\DBAL\\Driver\\PDOException","Message":"SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction","Code":"40001","Trace":[{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php","line":1062,"function":"execute","class":"Doctrine\\DBAL\\Driver\\PDOStatement","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/Connection.php","line":214,"function":"executeUpdate","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/Lock/DBLockingProvider.php","line":277,"function":"executeUpdate","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/apps/files/lib/BackgroundJob/CleanupFileLocks.php","line":58,"function":"cleanExpiredLocks","class":"OC\\Lock\\DBLockingProvider","type":"->"},{"file":"/var/www/nextcloud/lib/private/BackgroundJob/Job.php","line":52,"function":"run","class":"OCA\\Files\\BackgroundJob\\CleanupFileLocks","type":"->"},{"file":"/var/www/nextcloud/lib/private/BackgroundJob/TimedJob.php","line":59,"function":"execute","class":"OC\\BackgroundJob\\Job","type":"->"},{"file":"/var/www/nextcloud/cron.php","line":126,"function":"execute","class":"OC\\BackgroundJob\\TimedJob","type":"->"}],"File":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php","Line":129,"Previous":{"Exception":"PDOException","Message":"SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction","Code":"40001","Trace":[{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php","line":127,"function":"execute","class":"PDOStatement","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php","line":1062,"function":"execute","class":"Doctrine\\DBAL\\Driver\\PDOStatement","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/Connection.php","line":214,"function":"executeUpdate","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/Lock/DBLockingProvider.php","line":277,"function":"executeUpdate","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/apps/files/lib/BackgroundJob/CleanupFileLocks.php","line":58,"function":"cleanExpiredLocks","class":"OC\\Lock\\DBLockingProvider","type":"->"},{"file":"/var/www/nextcloud/lib/private/BackgroundJob/Job.php","line":52,"function":"run","class":"OCA\\Files\\BackgroundJob\\CleanupFileLocks","type":"->"},{"file":"/var/www/nextcloud/lib/private/BackgroundJob/TimedJob.php","line":59,"function":"execute","class":"OC\\BackgroundJob\\Job","type":"->"},{"file":"/var/www/nextcloud/cron.php","line":126,"function":"execute","class":"OC\\BackgroundJob\\TimedJob","type":"->"}],"File":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php","Line":127}},"CustomMessage":"Error while running background job (class: OCA\\Files\\BackgroundJob\\CleanupFileLocks, arguments: )"},"userAgent":"--","version":"20.0.0.9"}
{"reqId":"8e4aOqy4cFyQQUtDT3ax","level":3,"time":"2020-10-13T15:35:02+00:00","remoteAddr":"","user":"--","app":"core","method":"","url":"--","message":{"Exception":"Doctrine\\DBAL\\Exception\\DeadlockException","Message":"An exception occurred while executing 'DELETE FROM `oc_file_locks` WHERE `ttl` < ?' with params [1602603302]:\n\nSQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction","Code":0,"Trace":[{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php","line":169,"function":"convertException","class":"Doctrine\\DBAL\\Driver\\AbstractMySQLDriver","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php","line":149,"function":"wrapException","class":"Doctrine\\DBAL\\DBALException","type":"::"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php","line":1071,"function":"driverExceptionDuringQuery","class":"Doctrine\\DBAL\\DBALException","type":"::"},{"file":"/var/www/nextcloud/lib/private/DB/Connection.php","line":214,"function":"executeUpdate","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/Lock/DBLockingProvider.php","line":277,"function":"executeUpdate","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/apps/files/lib/BackgroundJob/CleanupFileLocks.php","line":58,"function":"cleanExpiredLocks","class":"OC\\Lock\\DBLockingProvider","type":"->"},{"file":"/var/www/nextcloud/lib/private/BackgroundJob/Job.php","line":52,"function":"run","class":"OCA\\Files\\BackgroundJob\\CleanupFileLocks","type":"->"},{"file":"/var/www/nextcloud/lib/private/BackgroundJob/TimedJob.php","line":59,"function":"execute","class":"OC\\BackgroundJob\\Job","type":"->"},{"file":"/var/www/nextcloud/cron.php","line":126,"function":"execute","class":"OC\\BackgroundJob\\TimedJob","type":"->"}],"File":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php","Line":34,"Previous":{"Exception":"Doctrine\\DBAL\\Driver\\PDOException","Message":"SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction","Code":"40001","Trace":[{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php","line":1062,"function":"execute","class":"Doctrine\\DBAL\\Driver\\PDOStatement","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/Connection.php","line":214,"function":"executeUpdate","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/Lock/DBLockingProvider.php","line":277,"function":"executeUpdate","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/apps/files/lib/BackgroundJob/CleanupFileLocks.php","line":58,"function":"cleanExpiredLocks","class":"OC\\Lock\\DBLockingProvider","type":"->"},{"file":"/var/www/nextcloud/lib/private/BackgroundJob/Job.php","line":52,"function":"run","class":"OCA\\Files\\BackgroundJob\\CleanupFileLocks","type":"->"},{"file":"/var/www/nextcloud/lib/private/BackgroundJob/TimedJob.php","line":59,"function":"execute","class":"OC\\BackgroundJob\\Job","type":"->"},{"file":"/var/www/nextcloud/cron.php","line":126,"function":"execute","class":"OC\\BackgroundJob\\TimedJob","type":"->"}],"File":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php","Line":129,"Previous":{"Exception":"PDOException","Message":"SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction","Code":"40001","Trace":[{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php","line":127,"function":"execute","class":"PDOStatement","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php","line":1062,"function":"execute","class":"Doctrine\\DBAL\\Driver\\PDOStatement","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/Connection.php","line":214,"function":"executeUpdate","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/Lock/DBLockingProvider.php","line":277,"function":"executeUpdate","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/apps/files/lib/BackgroundJob/CleanupFileLocks.php","line":58,"function":"cleanExpiredLocks","class":"OC\\Lock\\DBLockingProvider","type":"->"},{"file":"/var/www/nextcloud/lib/private/BackgroundJob/Job.php","line":52,"function":"run","class":"OCA\\Files\\BackgroundJob\\CleanupFileLocks","type":"->"},{"file":"/var/www/nextcloud/lib/private/BackgroundJob/TimedJob.php","line":59,"function":"execute","class":"OC\\BackgroundJob\\Job","type":"->"},{"file":"/var/www/nextcloud/cron.php","line":126,"function":"execute","class":"OC\\BackgroundJob\\TimedJob","type":"->"}],"File":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php","Line":127}},"CustomMessage":"Error while running background job (class: OCA\\Files\\BackgroundJob\\CleanupFileLocks, arguments: )"},"userAgent":"--","version":"20.0.0.9"}
The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):
<?php
$CONFIG = array (
'instanceid' => 'oc7lmbh3982t',
'passwordsalt' => *********,
'secret' => *********,
'trusted_domains' =>
array (
0 => 'cloud.kieronam.net',
),
'datadirectory' => '/var/www/nextcloud/data',
'dbtype' => 'mysql',
'version' => '20.0.0.9',
'overwrite.cli.url' => 'https://cloud.kieronam.net',
'dbname' => 'nextcloud',
'dbhost' => 'localhost',
'dbport' => '',
'dbtableprefix' => 'oc_',
'mysql.utf8mb4' => true,
'dbuser' => *********,
'dbpassword' => *********,
'installed' => true,
'memcache.local' => '\OC\Memcache\APCu',
'memcache.distributed' => '\OC\Memcache\Redis',
'redis' => [
'host' => '127.0.0.1',
'port' => 6379,
],
);