Slow sql query on oc_cache if desc and limit used

Nextcloud version: 21.0.3
Operating system and version: Ubuntu 20.04
Apache or nginx version: 2.4.41
PHP version: 7.4
MariaDB version: 10.3.29-MariaDB-0ubuntu0.20.04.1-log

I am facing the issue, that the query for the photos app is very slow, it takes 6-9 seconds to show the next 20 thumbnail. There is only CPU-usage, and no disk-IO (moving to SSD did not change anything on execution-time).
I do not know the time when this performance-issue started. Because of generating thumbnails on the fly it was slow anyway and I didn´t had the sql slow query log active …
This is the only performance-issue I can see in the slow sql query log and within the nextcloud-website.
This is the original SQL command which is also reported by mariadb as slow query:

MariaDB [...]> 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` = 2) AND ((`path`  COLLATE utf8mb4_general_ci LIKE 'files/%') AND ((`mimetype` = 13) OR (`mimetype` = 7) OR (`mimetype` = 83) OR (`mimetype` = 12) OR (`mimetype` = 30))) ORDER BY `mtime` desc LIMIT 20;
20 rows in set (6.322 sec)

I just tested a few things with the SQL-command above and thats what I found:
limit without desc: 3 rows in set (0.002 sec)
desc without limit: 24027 rows in set (0.272 sec)
desc and limit: 1 row in set (6.190 sec), up to 9 sec

more tests:

 SELECT * FROM `oc_filecache`  ORDER BY `mtime` desc LIMIT 3;
3 rows in set (0.000 sec)

SELECT * FROM `oc_filecache` WHERE (`storage` = 2) AND ((`path`  COLLATE utf8mb4_general_ci LIKE 'files/%') AND ((`mimetype` = 13) OR (`mimetype` = 7) OR (`mimetype` = 83) OR (`mimetype` = 12) OR (`mimetype` = 30))) ORDER BY `mtime` desc LIMIT 3;
3 rows in set (6.801 sec)

SELECT * FROM `oc_filecache` WHERE (`storage` = 2) ORDER BY `mtime` desc LIMIT 5;
5 rows in set (6.888 sec)

SELECT * FROM `oc_filecache` WHERE (`storage` = 2) ORDER BY `mtime` desc;
46268 rows in set (0.274 sec)

SELECT * FROM `oc_filecache` WHERE (`storage` = 2) ORDER BY `mtime` limit 7;
7 rows in set (0.002 sec)

 SELECT * FROM `oc_filecache` ORDER BY `mtime` desc LIMIT 9;
9 rows in set (0.001 sec)

My findings are, that as soon as there is a where, desc and limit in this query it will be slow. removing one of three and this query will be fast again.

I am facing the issue with the original mariadb files and also with the changes from nextcloud-admin docs included (/etc/mysql/mariadb.conf.d/50-server.cnf):

[server]
[mysqld]
user = mysql
pid-file = /run/mysqld/mysqld.pid
socket = /run/mysqld/mysqld.sock
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
bind-address = 127.0.0.1
skip_name_resolve = 1
tmp_table_size= 64M
max_heap_table_size= 64M
transaction_isolation = READ-COMMITTED
binlog_format = ROW
innodb_large_prefix=on
innodb_file_format=barracuda
innodb_file_per_table=1
query_cache_type = 1
query_cache_limit = 2M
query_cache_size = 64M
query_cache_min_res_unit = 2k
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 1
expire_logs_days = 10
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
innodb_buffer_pool_size = 128M
innodb_buffer_pool_instances = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 32M
innodb_max_dirty_pages_pct = 90
[embedded]
[mariadb]
[mariadb-10.3]

I do not think that the config.php or other nextcloud files are relevant because they are irrelevant for the mysql command and there isn´t any log in the nextcloud log.

my questions:

  • Are you facing the same issue?
  • Why is the query only slow if where, desc and limit are used togheter but not if only 2 of 3 are used in one command?
  • Is it possible to reduce the time needed?

Thank you!

ORDER BY can be a highly CPU intensive operation, because it forces the database server to actually sort the results. desc causes the order to be reversed, which obviously will take longer than if the results are already sorted ascending.

In addition, subsequent runs of the same query will have cached results, so take less time.

I just checked the photos App for differend Users, I am the only user with this long duration for the SQL statement. The scrolling in the photo app is very fast for the other users … (wtf?)
Adding an offset with higher than ~3000 will also cause an instant (~0.2 sec) result I checked the following statements a few times with different limits to avoid cached results because I couldn´t believe this.

SELECT * FROM `oc_filecache` WHERE (`storage` = 2) ORDER BY `mtime` desc LIMIT 37 OFFSET 3000;
37 rows in set (0.208 sec)

 SELECT * FROM `oc_filecache` WHERE (`storage` = 2) ORDER BY `mtime` desc LIMIT 37 OFFSET 2500;
37 rows in set (7.593 sec)

okay, but why is it 23 times (0.3 vs 6.8 sec) faster by just removing the limit? It should take the same or less time by using the limit, not 23 times more ^^

Also I do not understand why the offset >~3000 helps improving the query …
Something wrong with data within the table in the first ~3.000 entries? But if there is something wrong, why is the select so fast without the limit?
Seems for me that there is something wrong within the DB.

Kind of fascinating for me … And I hope that someone will give me some input how to debug such a strange case :smiley:

Is something different for your user. Much more files, or much more files in some folders? How many entries does the filecache table have?

You have tools (mysqltuner, tuning-primer.sh) to check the use of the database caches.

Hi,

I did some more testing and can´t find any logic.

  • its only if “where”, “desc” and “limit” are within one query, removing one and it is fast
  • its only for my user
  • I have only 1/3 of rows (and files) of another user
  • for all others the query-time is logic (join and desc needs more time, limit reduces the needed time), just the one query for my user …
  • Even the files within the folders are less than for other users (but it should not change anything for the limit?)

@tflidd I could not find any difference between my and other users which could answer my question :confused: less files, less pictures, as I can see less files within folders, …

I did some testing including the other users and resetting the query cache (just to be sure to have plain SQL performance to compare the time):

RESET QUERY CACHE;SELECT * FROM `oc_filecache` WHERE (`storage` = 2) ;
RESET QUERY CACHE;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` = 2) AND ((`path`  COLLATE utf8mb4_general_ci LIKE 'files/%') AND ((`mimetype` = 13) OR (`mimetype` = 7) OR (`mimetype` = 83) OR (`mimetype` = 12) OR (`mimetype` = 30))) ;
RESET QUERY CACHE;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` = 2) AND ((`path`  COLLATE utf8mb4_general_ci LIKE 'files/%') AND ((`mimetype` = 13) OR (`mimetype` = 7) OR (`mimetype` = 83) OR (`mimetype` = 12) OR (`mimetype` = 30))) ORDER BY `mtime` desc;
RESET QUERY CACHE;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` = 2) AND ((`path`  COLLATE utf8mb4_general_ci LIKE 'files/%') AND ((`mimetype` = 13) OR (`mimetype` = 7) OR (`mimetype` = 83) OR (`mimetype` = 12) OR (`mimetype` = 30))) ORDER BY `mtime` LIMIT 20;
RESET QUERY CACHE;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` = 2) AND ((`path`  COLLATE utf8mb4_general_ci LIKE 'files/%') AND ((`mimetype` = 13) OR (`mimetype` = 7) OR (`mimetype` = 83) OR (`mimetype` = 12) OR (`mimetype` = 30))) ORDER BY `mtime` desc LIMIT 20;

The results
(storage = 2) which is my account:
46291 rows in set (0.144 sec)
24039 rows in set (0.187 sec) ← only where
24039 rows in set (0.191 sec) ← where + desc
20 rows in set (0.094 sec) ← where + limit
20 rows in set (49.577 sec) ← where, desc and limit <— Why?

(storage = 3):
130925 rows in set (0.524 sec)
24039 rows in set (1.216 sec)
98558 rows in set (2.011 sec)
20 rows in set (0.060 sec)
20 rows in set (0.022 sec)

(storage = 4):
1431 rows in set (0.398 sec)
384 rows in set (0.003 sec)
384 rows in set (0.003 sec)
20 rows in set (0.002 sec)
20 rows in set (0.001 sec)

I did also a backup and restore on a fresh installed DB-Server only. Same behaviour (just longer duration because of less CPU/RAM and shared disk with other VMs, thats why it took ~50 sec and not ~7 sec).

mysqltuner:

General recommendations:
    Control warning line(s) into /var/log/mysql/error.log file => innodb_file_format is depricated 
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability => enough (2GGB) free RAM
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
    Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 2M, or use smaller result sets) => set to 4M
    performance_schema = ON enable PFS
    innodb_buffer_pool_size (>= 508.3M) if possible. => set to 512M
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

So there are some caching ajustments but its not a caching issue.
The changes did not change the duration … maybe I will create a new user for myself as workaround ^^

That is a good question. I don’t know enough about the database, perhaps they have a profiler or analyse mode so you can cut down what part takes so long. If I’d do manually, I’d try to try without the LEFT JOIN. Or, as you reproduce it on a fresh database, perhaps you can reduce your own data and find the entries that create the problem.