Query on oc_filecache uses wrong index - Cron job runs very long #24401
Comments
JohnWalkerx
added
0. Needs triage
JohnWalkerx
changed the title
|
Maybe the index is missing on your instance? can you run: |
|
I would say the index
Output of Check indices of the share table.
Check indices of the filecache table.
Check indices of the twofactor_providers table.
Check indices of the login_flow_v2 table.
Check indices of the whats_new table.
Check indices of the cards table.
Check indices of the cards_properties table.
Check indices of the calendarobjects_props table.
Check indices of the schedulingobjects table.
Check indices of the oc_properties table.
Done.I will observe if anything has changed. |
|
On 19.0.4 (updated from 16.0.0). |
|
Problem still exists. Actual two cron jobs are running. Intervall of calling is 15 minutes. CPU graph over the last 24 hours: |
Well this means our database structure and code are correct. Maybe you can run some repair/optimize commands on your database? |
|
I tried following today:
Nothing helps. The query optimizer still wants to use the I'm not the only one who have this issue. There are others on the forum. Maybe it depends on the size of the table. The table of my clean NC20 instance is very small. Here the info of my indexes: I also find a site in the MySQL Docs: Optimizer-Related Issues Is it possible that we force the index in the query from nextcloud? |
Not really as this is not supported by all databases Nextcloud supports. Also just for the record I get different results depending on the storage. E.g. for us storage with ID 1 does not exist. Result: If I change the ID to 3 it uses the correct index: |
|
On my instance storage with ID 1 is the normal data directory. I looked a little bit at the processes/queries which mysqld is running. While I'm typing this comment, two cron jobs are running and each job queried it at least 3 times. |
|
I want to jump onto this bandwagon ;) Even though I am not sure if it is the same problem, but at least for people searching, they should get linked through the different articles and sites. Therefor linking at least the Nextcloud Forum post I started and used to link some additional threads and sites as reference I fixed my problem (for now) by cleaning up my photo previews on disk and in the database. Besides this, why "scan files" is done twice a day is still unclear, I thought this would not be necessary if everything is pushed via the standard API / Interfaces. |
|
Does anyone know what is done with this query? I searched a little bit deeper and had a look at the output of this query. And the output are just directories in the preview folder (excepted the first one). There are also a lot of directories in the Maybe this information can point someone in the right direction. |
|
I am running mariadb database and I am affected by the same issue. I used preview generator as well. I am using docker instance for nextcloud version 20.0.3. |
|
appdata should not really be scanned by this. So I guess this is where the problem comes from. |
|
@JohnWalkerx @robertugo2 So the preview folders / files itself are fine, but somehow the sheer amount of the folder or something else breaks the system. @nickvergessen |
They have the same storage id as your files? On my dev instance the previews have a different storage id then my files. |
|
@kesselb |
|
@feutl not a question for you. If you are interested read the issue from the beginning. Then you may know why I was asking another person for the storage id. Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue. |
Yes it's needed, but it shouldn't run through appdata_* server/apps/files/lib/BackgroundJob/ScanFiles.php Lines 104 to 109 in d523286 Returns the appdata_* thing for you too, instead of only real users. Can you after: add: \OC::$server->getLogger()->error(json_encode($users), ['app' => 'server-24401']);and then check your log file for |
|
@kesselb @nickvergessen |
@feutl of course my comment was totally out of nowhere unrelated to anything that has been written before.
server/apps/files/lib/BackgroundJob/ScanFiles.php Lines 100 to 106 in 9305a00 @feutl add the code to line 106. |
@kesselb |
|
@JohnWalkerx server/lib/private/Files/Cache/Cache.php Lines 994 to 1007 in aef1cdb
|
I added this line. I also don't have the function
@kesselb sudo -u www-data php ./occ files:scan --all --unscanned
Starting scan for user 1 out of 13 (User1)
Starting scan for user 2 out of 13 (User2)
Starting scan for user 3 out of 13 (User3)
Starting scan for user 4 out of 13 (User4)
Starting scan for user 5 out of 13 (User5)
Starting scan for user 6 out of 13 (User6)
Starting scan for user 7 out of 13 (User7)
Starting scan for user 8 out of 13 (User8)
Starting scan for user 9 out of 13 (User9)
Starting scan for user 10 out of 13 (User10)
Starting scan for user 11 out of 13 (User11)
Starting scan for user 12 out of 13 (User12)
Starting scan for user 13 out of 13 (User13)
+---------+-------+--------------+
| Folders | Files | Elapsed time |
+---------+-------+--------------+
| 0 | 0 | 00:00:01 |
+---------+-------+--------------+So we'll have to wait until the background jobs runs the scan. |
|
I got following debug line output: {
"reqId": "ks9Kp2ovRkkr6dfJ8G2W",
"level": 3,
"time": "2020-12-16T01:15:02+01:00",
"remoteAddr": "",
"user": "--",
"app": "server-24401",
"method": "",
"url": "--",
"message": {
"User1": {},
"User2": {},
"User3": {},
"User4": {},
"User5": {},
"User6": {},
"User7": {},
"User8": {},
"User9": {},
"User10": {},
"User11": {},
"User12": {},
"User13": {}
},
"userAgent": "--",
"version": "19.0.6.2"
}Currently one cron job is running and is executing the query of Just for the record: In the |
|
@Cebrain I solved it yesterday! |
Great to hear!
With this, the jobs still run long (15 minutes) but arent killing my server |
This is super easy - I took this video https://youtu.be/JgrPcQIQuF8 |
|
Hi, please update to 24.0.9 or better 25.0.3 and report back if it fixes the issue. Thank you! My goal is to add a label like e.g. 25-feedback to this ticket of an up-to-date major Nextcloud version where the bug could be reproduced. However this is not going to work without your help. So thanks for all your effort! If you don't manage to reproduce the issue in time and the issue gets closed but you can reproduce the issue afterwards, feel free to create a new bug report with up-to-date information by following this link: https://github.com/nextcloud/server/issues/new?assignees=&labels=bug%2C0.+Needs+triage&template=BUG_REPORT.yml&title=%5BBug%5D%3A+ |
szaimen
added
needs info
0. Needs triage
|
@szaimen I think I can reproduce this on NC 25.0.4 My digging points me at Explain Output:
|
|
I just profiled a similar command don't know if it is helpfull:
|
|
Since upgrading to 25 I've hit this too, but not just with CRON - all file uploads choke and die on this query. Here's the explain on one: This issue is currently show-stopper for me. I've tried disabling redis, migrating out of the snap installation, migrating to postgres (and then back again to mysql) with no luck. |
|
Update: Ran an EXPLAIN ANALYZE on this query. The fs_storage_path_hash index seems... suboptimal? |
This Workaround didn't work for me. |
|
To update my comment here - adding an index on |
not working for me neither. i still have this problem. |
|
I had the same issue with slow query logged and mariadb process constantly using high cpu. I'm on Nextcloud 27.1.1 with mariadb 10.6.14. The SELECT
`filecache`.`fileid`,
`storage`,
`path`,
`path_hash`,
`filecache`.`parent`,
`filecache`.`name`,
`mimetype`,
`mimepart`,
`size`,
`mtime`,
`storage_mtime`,
`encrypted`,
`etag`,
`permissions`,
`checksum`,
`unencrypted_size`,
`metadata_etag`,
`creation_time`,
`upload_time`
FROM
`oc_filecache` `filecache`
LEFT JOIN `oc_filecache_extended` `fe` ON `filecache`.`fileid` = `fe`.`fileid`
WHERE
(`storage` = 27)
AND (
`path_hash` = '3d3867fe61097ed2ceed36fd182d435b'
)
My solution was to create an additional index that EXPLAIN now shows index is being used for this query: |
|
My PostgreSQL shows high CPU too and slow queries even with index already in place as per below (didn't have to create it recently - and seems like was created automatically): |
|
I found this bug after seeing this behavior on my instance. After some research I realized that I didn't tune up the mariadb server after moving to a new machine. I followed the recommendations, and now the issue has resolved. So it might be worth to check out your sql server. :) |
|
Would you mind to mention which performance tuning you've in mind? Looking at suggested settings for Mysql and mapping these to Postgres it would just mean decreasing memory available for queries. Happy to hear if there is anything specific. Unsure how further/which area should be tuned though :( |
|
My instance fallen into this problem 5 days ago after updating from 28.0.6 to 29.0.1. |
|
@bugsyb apologies to answering so late. In my situation I had mariadb running with the default (Debian) settings. Following the documentation was enough to counter the behavior described in this issue. Regarding psql, I wouldn't know, sorry. (On the machine where I had this Nextcloud instance before, I had put effort in mariadb ages ago, but I forgot about that. Now I just don't have time to dive into it further.) |
and others
➡️ 🚧 Possible workaround (works for me):
occ files:scan-app-dataon your instance to rescan the appdata folder.How to use GitHub
Steps to reproduce
(0. It began after I run the Preview Generator over 1553047 files (not only pictures). So a lot of previews were generated.)
Expected behaviour
Each cron job is done before the next one is started. CPU is not very high.
Actual behaviour
After some time there are running multiple cron jobs. CPU is at 100% because of mysqld.
This is because there is a very slow query:
If I enable to log slow querys I get following output:
If I let explain me this query I get following:
So it uses the wrong index
fs_storage_path_hash.I would expect that it uses the
fs_storage_sizeindex.If I execute the query and force the index it looks nice:
Output of EXPLAIN:
Why it doesn't use the
fs_storage_sizeindex?On a fresh NC20 test instance it uses this index.
Server configuration detail
Operating system: Linux 5.4.0-54-generic #60-Ubuntu SMP Fri Nov 6 10:37:59 UTC 2020 x86_64 / Ubuntu 20.04.29 LTS
Webserver: Apache (apache2handler)
Database: mysql 8.0.22
PHP version:
7.3.24-3+ubuntu20.04.1+deb.sury.org+1
Modules loaded: Core, date, libxml, openssl, pcre, zlib, filter, hash, Reflection, SPL, session, sodium, standard, apache2handler, mysqlnd, PDO, xml, apcu, bcmath, calendar, ctype, curl, dom, mbstring, fileinfo, ftp, gd, gettext, gmp, iconv, igbinary, imagick, intl, json, exif, mysqli, pdo_mysql, apc, posix, readline, redis, shmop, SimpleXML, sockets, sysvmsg, sysvsem, sysvshm, tokenizer, wddx, xmlreader, xmlwriter, xsl, zip, Phar, Zend OPcache
Nextcloud version: 19.0.5 - 19.0.5.2
Updated from an older Nextcloud/ownCloud or fresh install: Updated from 19.0.4 but problem exists there also.
Where did you install Nextcloud from: unknown
Signing status
Array
(
)
List of activated apps
Configuration (config/config.php)
Are you using external storage, if yes which one: No
Are you using encryption: No
Are you using an external user-backend, if yes which one: No
Client configuration
Browser: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:82.0) Gecko/20100101 Firefox/82.0
Operating system: -
Logs
There are no relevant logs
The text was updated successfully, but these errors were encountered: