Cron job MySQL disk writes

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:

  1. Set up cron job as above
  2. Wait for next execution of cron job
  3. 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

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

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

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.

1 Like

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?

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.

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.

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

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