The cron.php tasks run for hours, constant CPU load, queries on oc_filecache

ℹ️ Support
, , ,
19

I can confirm the described issue. Did you check the GitHub issue tracker?

20

No, but I’ve looked now. The entry https://github.com/nextcloud/server/issues/18960 looks as if it is similar, doesn’t seem to be the same. It doesn’t appear to have made any progress.

1 Like
21

Did you check the database itself for errors/corruption, slow queries and such? oc_filescache is known to be large, but what is the size of database file(s) (in relation to amount of actual handled files in NC and found files in NC data dir)?
Also mysqltuner or similar tool can help to check for insufficient cache sizes and such.
And of course dmesg to check for file system errors.

22

In my case, it helped recuding the cron frequency from every 5 mins to 15 mins and adding flock to avoid multiple runs in case the previous has not exited:
/var/spool/cron/crontabs/www-data:
*/15 * * * * flock /tmp php -f /var/www/html/cron.php

23

I’m facing the similar problem.
I started using the PreviewGenerator an generated the 32, 64, 256 and 2048 px previews for my 440 GB files (113 GB is my photo libary).
With this my oc_filecache table grown to a size of 420 MB.

I saw that the cron-job calls this (long running) query, like in your case:

SELECT `path` FROM nextcloud.oc_filecache WHERE `storage` = 1 AND `size` < 0 ORDER BY `fileid`

So I discovered that this query needs a lot of time. Here my slow-query output of mysql:

# User@Host: ncuser[ncuser] @ localhost []  Id: 21805
# Query_time: 253.154251  Lock_time: 0.000205 Rows_sent: 191412  Rows_examined: 1454739
SET timestamp=1603198875;
SELECT `path` FROM `oc_filecache` WHERE (`storage` = 1) AND (`size` < 0) ORDER BY `fileid` DESC;

Now I will also try to reduce the cron frequency from 5 mins to 15 mins and use the flock hint.

24

Similar problem here. My shared hosting provider complains about excess CPU usage even though I only use Nextcloud for my family with very low usage. I also use PreviewGenerator. Unfortunately not technical enough to give much more info. My hosting provider is threatening to kick me off their service… Cron every 15 minutes. Any suggestions on what to try (with good instructions !)

25

I started the same journey, not yet finished, but one issue was some symlinks which broke cron.php

Anyhow, I am still having situations where the load of my server runs quite high, because of multiple crons running at the same time.
If I use something like flock, it happened that crons do not run for days, because of the “one” which never finishes. Without flock, at a certain point mysqld starts freaking out a little :wink:

But I would say a “starting point”
https://help.nextcloud.com/t/some-cron-php-threads-never-finish-result-high-cpu-usage-because-of-mysqld/

26

Hey @h3rb3rt,
it looks like that your situation (file scan / symlinks) is maybe the same result but a different reason.

Also for me it didn’t worked with flock. I had the same issues.

27

I made some tests at my database why this query is so slow.
The short version:
For this query the wrong index is used.

Longer version:
We talk about this query:

SELECT `path` FROM nextcloud.oc_filecache WHERE `storage` = 1 AND `size` < 0 ORDER BY `fileid` DESC

I asked the MySQL-Server to explain me, how he will execute this query by prepend EXPLAIN:

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'oc_filecache', NULL, 'ref', 'fs_storage_path_hash,fs_storage_mimetype,fs_storage_mimepart,fs_storage_size', 'fs_storage_path_hash', '8', 'const', '788173', '33.33', 'Using where; Using filesort'

The correct index would be fs_storage_size. But it is using the fs_storage_path_hash.
I’m not sure why…
On a fresh installed Nextcloud 20 test instance with only the default data, the right index is used.

If I force the used index to fs_storage_size the query is executed in 3.654 sec.
So this is a huge difference to 253 sec.

I will try to write a GitHub issue in the next days.

1 Like
28

Ok, this is a little too much magic for me :wink:
Reading this I have no clue how and if I could verify this on my instance at all, but after installed some extended monitoring I see that after the cron starts (still not knowing what it exactly does all the time) the IO to my mounted NFS storage where the data is located gets extremly high and stays there as long as the job runs.
I need to kill the cron to fix the issues, this happens twice a day.

But running the oc files:scan --all is finished within 30 minutes.

This bugs me a lot the last few days.

1 Like
29

Multiple topics about long running cron jobs/high CPU usage and pretty much no response from anybody except for the multiple people who are having the problem. Is anyone working on this?

While I love Nextcloud, this lack of support/responsiveness impedes Nextcloud’s ability to become a mainstream solution for people other than enthusiasts and/or highly technical people.

Some response to this/these potential problems asap would be appreciated by many.

2 Likes
30

I finally found time to write a issue on GitHub:

3 Likes
31

Thank you ! I just realized a couple weeks ago that my cron.php via webcron was not working anymore. Set up cron and found out that it tends to run endlessly and multiple times. It does eat all of my mariadb server’s cpu on this request…

SELECT path FROM oc_filecache WHERE (storage = 879) AND (size < 0) ORDER BY fileid DESC

But sometimes the task does get through… Hopefully we’ll get an answer at some point!

32

To fix the problem temporarily I added a “timeout 30m flock /tmp/cron.lock” before the cron.php command in crontab so if a task lingers it lets it run for 30 minutes while queueing the other instances and then kills it. Should relieve my mariadb server :slight_smile:

1 Like
33

Quick follow up after 2 weeks : lowered the timeout to 10mn and the problem seems to be contained. Still there but has a low to no impact on my database server now.

Upgraded last night to NC 20.0.3. Will check if the problem still exists.

1 Like
34

Hello, 20.0.3 here, still experiencing the same problem. Endless cron with mariadb eating 150% cpu.
Using preview-generator app, seems to be the causing this behavior

35

Anybody knows if there is the same behaviour with nextcloud-preview.sh https://github.com/GAS85/nextcloud_scripts/blob/master/nextcloud-preview.sh ?

36

I wanna report you the current state of the GitHub-Issue:
In my case, I found a workaround (maybe solution) for this problem:
I run the Scan Appdata occ command occ files:scan-app-data and that fixed the problem for me.

It is useful when you run this command with screen to avoid aborting it when you leave terminal, because it can run longer (for me 1,75 h).

It is not exactly sure where the problem is coming from. We trying to figure it out.

37

I’ve noticed that things have gotten a lot better since my external storage file ownership/permissions now all match the user I was mapping it with. I had some files and directories that had owner-only permissions, with a different owner. I would have expected the scanner to just ignore those files/directories and continue on with the rest, but it seemed to get locked up trying desperately to access the inaccessible.

38

occ files:scan-app-data didn’t the trick for me… same fault…

USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
root 1 0.0 0.0 200 4 ? Ss May29 0:00 s6-svscan -t0 /var/run/s6/services
root 38 0.0 0.0 200 4 ? S May29 0:00 s6-supervise s6-fdholderd
root 366 0.0 0.0 200 4 ? S May29 0:00 s6-supervise nginx
root 367 0.0 0.0 200 0 ? S May29 0:00 s6-supervise php-fpm
root 368 0.0 0.0 200 4 ? S May29 0:00 s6-supervise cron
root 374 0.0 0.0 5192 2508 ? Ss May29 0:00 nginx: master process /usr/sbin/nginx -c /config/nginx/nginx.conf
root 375 0.0 0.0 2820 596 ? Ss May29 0:00 bash ./run
root 386 0.0 0.0 2344 792 ? S May29 0:05 /usr/sbin/crond -f -S -l 0 -c /etc/crontabs
abc 2087 0.0 0.0 5376 1012 ? S May30 0:00 nginx: worker process
abc 2088 0.0 0.0 5376 1012 ? S May30 0:00 nginx: worker process
abc 2089 0.0 0.0 5376 1008 ? S May30 0:00 nginx: worker process
abc 2090 0.0 0.0 5376 1020 ? S May30 0:00 nginx: worker process
root 2092 0.0 0.6 254872 24136 ? Ss May30 0:15 php-fpm: master process (/etc/php7/php-fpm.conf)
abc 2094 0.0 0.1 254876 5872 ? S May30 0:00 php-fpm: pool www
abc 2095 0.0 0.1 254876 5876 ? S May30 0:00 php-fpm: pool www
abc 2623 0.0 0.7 254872 27932 ? D 07:40 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2625 0.0 0.7 254872 28220 ? D 07:50 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2628 0.0 0.7 254872 28108 ? D 08:00 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2629 0.0 0.7 254872 27896 ? D 08:10 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2631 0.0 0.7 254872 28084 ? D 08:20 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2633 0.0 0.7 254872 27980 ? D 08:29 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2634 0.0 0.7 254872 28188 ? D 08:39 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2636 0.0 0.7 254872 28036 ? D 08:49 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2639 0.0 0.7 254872 27992 ? D 08:59 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2640 0.0 0.7 254872 27920 ? D 09:09 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2642 0.0 0.7 254872 28104 ? D 09:19 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2644 0.0 0.7 254872 28144 ? D 09:29 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2645 0.0 0.7 254872 28016 ? D 09:39 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2647 0.0 0.7 254872 28148 ? D 09:49 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2650 0.0 0.7 254872 28132 ? D 10:00 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2651 0.0 0.7 254872 28108 ? D 10:10 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2653 0.0 0.7 254872 27980 ? D 10:20 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2655 0.0 0.7 254872 28256 ? D 10:30 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2656 0.0 0.7 254872 28068 ? D 10:40 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2658 0.0 0.7 254872 28080 ? D 10:50 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2752 0.0 0.7 254872 27956 ? D 11:00 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2753 0.0 0.7 254872 28036 ? D 11:10 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2755 0.0 0.7 254872 28072 ? D 11:20 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2757 0.0 0.7 254872 28224 ? D 11:30 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2758 0.0 0.7 254872 28072 ? D 11:39 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2760 0.0 0.7 254872 28072 ? D 11:49 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2763 0.0 0.7 254872 28224 ? D 11:59 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2764 0.0 0.7 254872 27888 ? D 12:09 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2766 0.0 0.7 254872 28140 ? D 12:19 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2768 0.0 0.7 254872 28276 ? D 12:29 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2769 0.0 0.7 254872 28188 ? D 12:40 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2771 0.0 0.7 254872 28068 ? D 12:50 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2774 0.0 0.7 254872 28180 ? D 13:00 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2775 0.0 0.7 254872 28284 ? D 13:10 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2777 0.0 0.7 254872 28188 ? D 13:20 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2779 0.0 0.7 254872 28232 ? D 13:30 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2780 0.0 0.7 254872 28020 ? D 13:40 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2782 0.0 0.7 254872 27996 ? D 13:49 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2792 0.0 0.7 254872 27948 ? D 13:59 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2793 0.0 0.7 254872 28024 ? D 14:09 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2795 0.0 0.7 254872 28200 ? D 14:19 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2797 0.0 0.7 254872 28120 ? D 14:29 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2798 0.0 0.7 254872 28256 ? D 14:40 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2800 0.0 0.7 254872 28188 ? D 14:50 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2803 0.0 0.7 254872 27908 ? D 15:00 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2804 0.0 0.7 254872 28192 ? D 15:10 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2806 0.0 0.7 254872 27952 ? D 15:20 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2808 0.0 0.7 254872 28216 ? D 15:30 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2809 0.0 0.7 254872 28004 ? D 15:40 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2811 0.0 0.7 254872 28004 ? D 15:49 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2814 0.0 0.7 254872 27936 ? D 15:59 0:00 php7 -f /config/www/nextcloud/cron.php
abc 2815 0.0 0.7 254872 28132 ? D 16:09 0:00 php7 -f /config/www/nextcloud/cron.php
root 2838 0.0 0.0 2988 1704 pts/0 Ss 16:16 0:00 bash
abc 2856 0.0 0.7 254872 28204 ? D 16:20 0:00 php7 -f /config/www/nextcloud/cron.php
root 2929 0.0 0.0 2560 732 pts/0 R+ 16:27 0:00 ps aux