Long running queries (sometimes more than 2000 seconds)

ℹ️ Support
1

Nextcloud version: 24.0.2
MariaDB Version: 10.6
Both are running in docker containers.

I encounter some queries that are running for a very long time and I don’t see why the database index obviously is not used. Here is some analytics from my database:

MariaDB [(none)]> show processlist;
+-------+-----------+------------------+-----------+---------+------+---------------------+------------------------------------------------------------------------------------------------------+----------+
| Id    | User      | Host             | db        | Command | Time | State               | Info                                                                                                 | Progress |
+-------+-----------+------------------+-----------+---------+------+---------------------+------------------------------------------------------------------------------------------------------+----------+
| 49580 | nextcloud | 172.18.0.3:37872 | nextcloud | Query   | 2951 | Creating sort index | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 49600 | nextcloud | 172.18.0.3:37914 | nextcloud | Query   | 2942 | Creating sort index | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 49731 | nextcloud | 172.18.0.3:38188 | nextcloud | Query   | 2684 | Sending data        | SELECT `a`.`name` FROM `oc_filecache` `a` LEFT JOIN `oc_filecache` `b` ON `a`.`name` = `b`.`fileid`  |    0.000 |
| 50066 | nextcloud | 172.18.0.3:38896 | nextcloud | Query   | 2377 | Creating sort index | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 50523 | nextcloud | 172.18.0.3:39886 | nextcloud | Query   | 1998 | Creating sort index | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 50542 | nextcloud | 172.18.0.3:39928 | nextcloud | Query   | 1980 | Creating sort index | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 50941 | nextcloud | 172.18.0.3:40772 | nextcloud | Query   | 1403 | Creating sort index | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 51689 | nextcloud | 172.18.0.3:42366 | nextcloud | Query   |  877 | Creating sort index | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 51754 | nextcloud | 172.18.0.3:42502 | nextcloud | Query   |  831 | Creating sort index | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 51768 | nextcloud | 172.18.0.3:42530 | nextcloud | Query   |  826 | Creating sort index | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 51821 | nextcloud | 172.18.0.3:42648 | nextcloud | Query   |  769 | Creating sort index | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 51839 | nextcloud | 172.18.0.3:42688 | nextcloud | Query   |  742 | Creating sort index | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 51857 | nextcloud | 172.18.0.3:42726 | nextcloud | Query   |  736 | Creating sort index | SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepa |    0.000 |
| 51925 | nextcloud | 172.18.0.3:42874 | nextcloud | Query   |   90 | Sending data        | SELECT `size`, `unencrypted_size` FROM `oc_filecache` WHERE `parent` = 5                             |    0.000 |
| 52251 | nextcloud | localhost        | NULL      | Query   |    0 | starting            | show processlist                                                                                     |    0.000 |
| 52414 | nextcloud | 172.18.0.3:43910 | nextcloud | Query   |    0 | Updating status     | UPDATE `oc_file_locks` SET `lock` = `lock` - '1' WHERE (`key` IN ('files/bed4df36d30a241181bbe10879d |    0.000 |
| 52417 | nextcloud | 172.18.0.3:43916 | nextcloud | Sleep   |    6 |                     | NULL                                                                                                 |    0.000 |
| 52418 | nextcloud | 172.18.0.3:43918 | nextcloud | Query   |    0 | starting            | COMMIT                                                                                               |    0.000 |
| 52419 | nextcloud | 172.18.0.3:43920 | nextcloud | Query   |    0 | Updating status     | COMMIT                                                                                               |    0.000 |
| 52420 | nextcloud | 172.18.0.3:43922 | nextcloud | Query   |    0 | Updating status     | UPDATE `oc_filecache` SET `size` = '100193455646' WHERE (`fileid` = 240405) AND ((`size` <> '1001934 |    0.000 |
| 52423 | nextcloud | 172.18.0.3:43928 | nextcloud | Query   |    0 | Sending data        | INSERT INTO `oc_mounts` (`storage_id`,`root_id`,`user_id`,`mount_point`,`mount_id`,`mount_provider_c |    0.000 |
+-------+-----------+------------------+-----------+---------+------+---------------------+------------------------------------------------------------------------------------------------------+----------+

Analyzing the queries it boils down to this statement:

select count(*) from oc_filecache where storage = 1 and (path_hash = '1a1bdb0c874d2a0351463d857646b44a' or (path LIKE 'appdata_ocprs1rb5wmk/collectives/4/%'));

I can ask MariaDB to explain the execution of that query:

MariaDB [nextcloud]> explain select count(*) from oc_filecache where storage = 1 and (path_hash = '1a1bdb0c874d2a0351463d857646b44a' or (`path` LIKE 'appdata_ocprs1rb5wmk/collectives/4/%'));
+------+-------------+--------------+------+-----------------------------------------------------------------------------------------------------+------+---------+------+---------+-------------+
| id   | select_type | table        | type | possible_keys                                                                                       | key  | key_len | ref  | rows    | Extra       |
+------+-------------+--------------+------+-----------------------------------------------------------------------------------------------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | oc_filecache | ALL  | fs_storage_path_hash,fs_storage_mimetype,fs_storage_mimepart,fs_storage_size,fs_storage_path_prefix | NULL | NULL    | NULL | 2220617 | Using where |
+------+-------------+--------------+------+-----------------------------------------------------------------------------------------------------+------+---------+------+---------+-------------+

The right key fs_storage_path_prefix is listed. But it seams not to use it.

Anybody any idea on why this happens?

I know that I am running a newer MariaDB than recommended by nextcloud. I don’t know if it would be fixed with 10.5.

Thanks a lot for your advice.
Roman

2

OK. It looks like the index is not used because my path contains an underline (_). This should be escaped because mariaDB would interpret it as a wildcard and therefore not use the index.
My problem is now, that I don’t know where this path is coming from in the source code. I even don’t know if I should look in nextcloud or in the collectives app.

Any hint anyone?

3

I think the query is triggered at the file overview in the section recommendations which shows the latest changed files. Is there any way how to turn this off?
I also figured out that the problem goes away when I inactivate the app collectives. But I don’t think it is the source of the problem. I beleave the problem is that this query gets too complicated and MariaDB is unable to find the best strategy.

4

By the way: The whole query is:


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`,
       `unencrypted_size`
FROM `oc_filecache` `file`
LEFT JOIN `oc_filecache_extended` `fe` ON `file`.`fileid` = `fe`.`fileid`
WHERE ((`mimetype` <> 2)
       OR (`size` = 0))
  AND (((`storage` = 3)
        AND ((`path` = 'files')
             OR (`path` LIKE 'files/%')))
       OR ((`storage` = 1)
           AND ((`path_hash` = '1a1bdb0c874d2a0351463d857646b44a')
                OR (`path` LIKE 'appdata_ocprs1rb5wmk/collectives/4/%')))
       OR ((`storage` = 1)
           AND ((`path_hash` = '411ff3d42ee516fd62ec9a60985e7389')
                OR (`path` LIKE 'appdata_ocprs1rb5wmk/collectives/1/%')))
       OR ((`storage` = 1)
           AND ((`path_hash` = '883cd42efa54e36eef716cf11828b35e')
                OR (`path` LIKE 'appdata_ocprs1rb5wmk/collectives/3/%')))
       OR ((`storage` = 1)
           AND ((`path_hash` = 'ef477a7f1f913a05557ac2fabf4470ef')
                OR (`path` LIKE 'appdata_ocprs1rb5wmk/collectives/2/%')))
       OR ((`storage` = 6)
           AND ((`path` = 'files/Hochzeit')
                OR (`path` LIKE 'files/Hochzeit/%')))
       OR ((`storage` = 6)
           AND ((`path` = 'files/Wohnung')
                OR (`path` LIKE 'files/Wohnung/%'))))
ORDER BY `mtime` + '0' DESC
LIMIT 7;
5

I figured it out. It was the app Recommendations. Turning it off fixed my issue.

6

I’ve found that removing the + '0' from the ORDER BY fixes it.