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!
less files, less pictures, as I can see less files within folders, …