Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Very long running query eats almost all CPU #29377

Closed
Volker-K opened this issue Oct 22, 2021 · 5 comments
Closed

Very long running query eats almost all CPU #29377

Volker-K opened this issue Oct 22, 2021 · 5 comments
Labels
0. Needs triage Pending check for reproducibility or if it fits our roadmap bug

Comments

@Volker-K
Copy link

Problem

We are currently running NC 21.0.3.1 on four Apache-servers with a central MariaDB 10.4.17.

Every few days the CPU load on the database server is extremely high and we find a large number of processes from the same Apache node executing the same statement. The DB Server is normally running with less than 30% CPU load, uses more than 90% when those queries are active.
A test on the command line showed that those statements have a runtime of about 2 minutes and deliver an empty result set.
It seems that users call a function that takes too long for them and then call the same function again, e.g. via reload and start a new query while the old queries are still running.

Output of show processlist;

+-------+-------------+---------------------+----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
| Id    | User        | Host                | db       | Command | Time | State        | Info                                                                                                 | Progress |
+-------+-------------+---------------------+----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
|  5223 | your_dbuser | your_hostname:51308 | cloud_db | Query   |    0 | Init         | show processlist                                                                                     |    0.000 |
| 25590 | your_dbuser | your_hostname:60614 | cloud_db | Query   |  524 | Sending data | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 25607 | your_dbuser | your_hostname:60628 | cloud_db | Query   |  553 | Sending data | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 25639 | your_dbuser | your_hostname:60660 | cloud_db | Query   |  547 | Sending data | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 26635 | your_dbuser | your_hostname:33070 | cloud_db | Query   |  427 | Sending data | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 26653 | your_dbuser | your_hostname:33080 | cloud_db | Query   |  516 | Sending data | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 26691 | your_dbuser | your_hostname:33104 | cloud_db | Query   |  514 | Sending data | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 27046 | your_dbuser | your_hostname:33356 | cloud_db | Query   |  384 | Sending data | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 27100 | your_dbuser | your_hostname:33388 | cloud_db | Query   |  493 | Sending data | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 27117 | your_dbuser | your_hostname:33404 | cloud_db | Query   |  499 | Sending data | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 28040 | your_dbuser | your_hostname:33902 | cloud_db | Query   |  389 | Sending data | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 28052 | your_dbuser | your_hostname:33922 | cloud_db | Query   |  349 | Sending data | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 28073 | your_dbuser | your_hostname:33946 | cloud_db | Query   |  344 | Sending data | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 29514 | your_dbuser | your_hostname:34776 | cloud_db | Query   |  329 | Sending data | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 29577 | your_dbuser | your_hostname:34824 | cloud_db | Query   |  281 | Sending data | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |

Sample SQL:

SELECT 	`file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepart`, 
		`size`, `mtime`, `storage_mtime`, `encrypted`, `etag`, `permissions`, `checksum`, `metadata_etag`, `creation_time`, `upload_time` 
FROM 	`oc_filecache` `file` 
LEFT JOIN `oc_filecache_extended` `fe` 
		ON `file`.`fileid` = `fe`.`fileid` 
WHERE (`storage` = 3) 
AND (((`path`  COLLATE utf8mb4_general_ci LIKE 'files/some-incomplete-pathname%') 
OR (`path_hash` = '0fcbb8347535deda2438dfc512176238')) 
AND (`name`  COLLATE utf8mb4_general_ci LIKE '%part-of-filename%')) 
ORDER BY `mtime` desc LIMIT 5;

At first glance, usable indexes on the columns "path" and "name" are missing. The index fs_parent_name_hash contains the column "name", but also "parent" as the first column, which makes it unusable for queries like this, only on "name" without "parent".
In addition, the LIKE query with masking of the first bytes can hardly be optimised via a normal index. A reformulation with a FULL TEXT INDEX and the appropriate MATCH() ... AGAINST query would probably be faster.

occ db:add-missig-indexes has been run and doesn't miss any index.

Expected behaviour

Queries in an online system should not take more than some parts of a second.

Actual behaviour

The database server is at its load limit and at least one user is frustrated by the poor performance of Nextcloud.

Server configuration

Operating system:
openSUSE 15.2

Web server:
Apache

Database:
MariaDB 10.4.17

PHP version:
7.4.6

Nextcloud version:
NC 21.0.3.1

Updated from an older Nextcloud/ownCloud or fresh install:
Updated from NC 20.x

@Volker-K Volker-K added 0. Needs triage Pending check for reproducibility or if it fits our roadmap bug labels Oct 22, 2021
@solracsf
Copy link
Member

This must help (maybe you can give it a try?): #28541

@Volker-K
Copy link
Author

Volker-K commented Oct 22, 2021

This must help (maybe you can give it a try?): #28541

Thank yu, just adding

CREATE INDEX fs_path_partial
ON oc_filecache 
(path(1000)
);

to the database. Now let's try if it helps ;-)

Thank you.

@Volker-K
Copy link
Author

Ok, it did speed up the queries but the problem remains since they are still running much too long.
Obviously they are running when using the search-field in the files-app. The hypno-wheel next to the field is still turning even when there are items displayed. I think the query has not yet sent an SQLCODE 100 and is still looking for data.
If a user enters another search term the query is still running while a second (third...) one is startet. Some search terms deliver results very quickliy, others don't what probably leads to reloading the page to restart the search because it seems to "hang".
Searching on files is broken right now.

@Volker-K
Copy link
Author

possiby same problem: #29392

@solracsf
Copy link
Member

Let's move this to #23835 and centralize discussions there.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
0. Needs triage Pending check for reproducibility or if it fits our roadmap bug
Projects
None yet
Development

No branches or pull requests

2 participants