Cron job MySQL disk writes

kieronam · December 9, 2020, 7:39pm

Nextcloud version (eg, 18.0.2): 20.0.1
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 (Ubuntu)
PHP version (eg, 7.1): 7.4.3

Wondered if anyone could help with this. Nextcloud is causing MySQL to write a large amount of data to disk every time the cron job is run (/nextcloud/cron.php) - this amounts to over 170Gb a day. No other processes are writing significant amounts to disk and this seems to stop when I stop the cron job.

The cron job is running every 5mins under user www-data.

Any idea why the cron job causes this? Is this normal behaviour or is there a way around it? Just concerned this is slowly eating through the lifespan of the SSD it runs on.

I haven’t had this problem until recently, so wonder if it has been caused by a recent(ish) update?

Thanks in advance.

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

Steps to replicate it:

Set up cron job as above
Wait for next execution of cron job
Check disk activity using netdata (or similar) and notice a large amount of disk activity every 5mins. Check disk status using smartctl (or similar) and notice large amounts of data being written and SSD lifetime slowly being used up.

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 => '(removed)',
  ),
  'datadirectory' => '/var/www/nextcloud/data',
  'dbtype' => 'mysql',
  'version' => '20.0.0.9',
  'overwrite.cli.url' => '(removed)',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'mysql.utf8mb4' => true,
  'dbuser' => '(removed)',
  'dbpassword' => '(removed)',
  'installed' => true,
  'memcache.local' => '\OC\Memcache\APCu',
  'memcache.locking' => '\OC\Memcache\Redis',
  'redis' => [
    'host' => '127.0.0.1',
    'port' => 6379,
  ],
);

The output of your Nextcloud log in Admin > Logging:

Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-12-09T19:25:58+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-12-09T19:25:58+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-12-09T19:25:57+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-12-09T19:20:16+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-12-09T19:20:14+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-12-03T19:06:30+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-12-03T19:06:30+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-12-03T19:06:30+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-12-03T19:06:12+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-12-03T19:06:12+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-12-03T19:06:12+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-12-03T19:01:05+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-12-03T19:01:05+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-12-03T19:01:05+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-12-03T18:59:49+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-12-03T18:59:49+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-12-03T18:59:49+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-28T11:37:00+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-28T11:36:59+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-28T11:36:59+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-28T11:34:27+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-28T11:34:27+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-28T11:34:27+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-23T11:12:39+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-23T11:12:38+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-23T10:34:48+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-23T10:34:47+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-23T07:57:29+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-23T07:57:28+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-22T11:00:44+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-22T11:00:43+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-22T11:00:43+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-20T07:37:16+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-20T07:37:14+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-19T11:21:29+0000
Error	PHP	Error: Undefined offset: 3 at /var/www/nextcloud/lib/private/legacy/OC_Helper.php#548	
2020-11-19T11:21:28+0000

j-ed · December 10, 2020, 9:42am

The problem can be solved by upgrading your installation to NC 20.0.2, as you can read in the following discussion:

kieronam · January 4, 2021, 8:38pm

Thanks @j-ed but that’s not what’s causing this issue - it’s been on going for a while, long before I was getting those ‘undefined offset’ warnings. Just to check, I’ve upgraded to 20.0.3 and now 20.0.4 and unfortunately am still getting the issue.

Can anyone confirm whether your NC instance generates significant disk activity via SQL on every call of the Cron job? Is this expected behaviour (although I never used to get this before NC version 20)?

If not, any other theories on why this is happening?

Cheers

Incidentally, this is my cron tab:
*/5 * * * * /usr/bin/php -f /var/www/nextcloud/cron.php
*/10 * * * * /usr/bin/php -f /var/www/nextcloud/occ files:scan --all

j-ed · January 5, 2021, 12:37pm

I would recommend to deactivated the second cron job, because there is no need to scan the file system for new files every 10 minutes, as long as you make sure that files are only added to Nextcloud using the standard web environment.

kieronam · January 5, 2021, 1:34pm

Thanks @j-ed. Unfortunately not an option as files in the external storage need to be accessed in a variety of ways.
In any case, as you can see, the majority of the disk usage seems to be coming from the main cron.php job.
Do you see similar disk writes from SQL when your cron.php is run?

gauravahuja9 · March 24, 2021, 9:39pm

I can confirm this. I installed nextcloud with mariadb on docker only 6 days ago. The total database size is ~2MB but the writes to disk by mariadb is over 20GB, over 3GB/day. From the mysql access logs I suspected it is due to the background jobs quering and updating the table (oc_jobs) every 5 minutes. So, I changed the nextcloud cron frequency from running every 5 minutes to every hour. This change significantly reduced the mariadb disk io from over 3GB/day to 250MB/day.

mneiger · March 25, 2021, 9:12am

The issue suddenly disappeared from my server around 3rd of March, though I could not pinpoint the specific change.
Configuration changes were limited to updates. If it was connected to “content”, then nothing would be trackable.

flotpg1 · April 30, 2022, 9:05pm

I have exactly the same issue. cronjob high I/O write load · Issue #22419 · nextcloud/server · GitHub
This problem already killed an HP enterprise SSD due to the excessive writes…
NC 23.0.3

flotpg1 · April 30, 2022, 9:16pm

just checked process list in mysql and saw oc_filecache.
The DB file has almost 300GB on a two user instance…
mysql mysql 297G Apr 30 23:15 oc_filecache.ibd

Crys · April 25, 2023, 1:17pm

Here the same. It also killed an HP Enterprise NVMe SSD a few weeks ago and I had the same issue. NC 25

MelBourbon · November 15, 2023, 5:19pm

Has somebody found a solution here? I may have the same problem (Nextcloud seems to keep drive active - #6 by devnull)

devnull · November 16, 2023, 8:00am

No real solution. But you can excecute cron.php manually. First deactivate cron or make sure that cron is not executed at the same time.

For manually execution use:
sudo -u www-data php /path/to/nextcloud/cron.php
(please first execute this command)

Then you can use “strace” (maybe you must install it) and see system calls e.g. read of files. You can store the log at /tmp by user www-data.

sudo -u www-data strace -ytff -o /tmp/log.txt php /path/to/nextcloud/cron.php

Name of logfile is e.g. /tmp/log.txt.2135 (process-id). If forked there are more logfiles.

It could be that the command itself is not helpful. But due to large amounts of data, there should be larger intervals in the time display in the logfile. Perhaps this will provide clues to the cause of the problem, if there is one at all.

You can also strace the pid of your mariadb
ps -ef |grep mariadb
and use strace in the same way for the mariddb:

strace -ytff -o /tmp/log-mariadb.txt -p <process-id>
You find the logs at /tmp/log-mariadb.txt.<pids>