"Your Photos" generates long sql queries

🍱 Features & apps 📸 Photos
1

HI

In the photo app “Your Photos” shows images chronologically.
Here I notice that scrolling is really slow and the log show long sql queries when requesting the next 25 images. (imageperrow * 5)

The long query looks like the following which takes the first time (before it gets cached) 4 to 5 seconds.

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 ((`mimetype` = 6) OR (`mimetype` = 11) OR (`mimetype` = 19) OR (`mimetype` = 13) OR (`mimetype` = 20)) AND (((`storage` = 3) AND ((`path` = 'files') OR (`path` LIKE 'files/%'))) OR ((`storage` = 5)

  AND ((`path` = 'files/Rezepte') OR (`path` LIKE 'files/Rezepte/%'))) OR ((`storage` = 5) AND ((`path` = 'files/photo')

  OR (`path` LIKE 'files/photo/%'))) OR ((`storage` = 4)

  AND (`path_hash` = ''))

  OR ((`storage` = 4) AND (`path_hash` = '')) OR ((`storage` = 4) AND (`path_hash` = ''))

  OR ((`storage` = 4) AND (`path_hash` = '')) OR ((`storage` = 4) AND (`path_hash` = ''))

  OR ((`storage` = 6) AND (`path_hash` = '')) OR ((`storage` = 6) AND (`path_hash` = ''))

  OR some more path_hashes....)

  ORDER BY `mtime` desc LIMIT 25 OFFSET 225;

I stripped it to generic items which should be possible to run on any nextcloud database not having the same hashes and files…
This request still consumes 4 seconds.
Maybe someone can try this on his side.

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 ((`mimetype` = 6) OR (`mimetype` = 11) OR (`mimetype` = 19) OR (`mimetype` = 13) OR (`mimetype` = 20)) AND (((`storage` = 3) AND ((`path` = 'files') OR (`path` LIKE 'files/%'))) OR (`storage` = 5))

 ORDER BY `mtime` desc LIMIT 25 OFFSET 225;

My size of oc_filecache

MariaDB [nextcloud]> SELECT count(*) FROM oc_filecache;
+----------+
| count(*) |
+----------+
|   915601 |
+----------+
1 row in set (0.450 sec)

MariaDB [nextcloud]> SELECT count(*) FROM oc_filecache_extended;
+----------+
| count(*) |
+----------+
|   199811 |
+----------+
1 row in set (0.067 sec)

mysql/my.cnf

[server]

skip_name_resolve = 1

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

query_cache_type = 1

query_cache_limit = 2M

query_cache_min_res_unit = 2k

query_cache_size = 64M

tmp_table_size= 64M

max_heap_table_size= 64M

slow_query_log = 1

slow_query_log_file = /var/log/mysql/slow.log

long_query_time = 1

[client-server]

!includedir /etc/mysql/conf.d/

!includedir /etc/mysql/mariadb.conf.d/

[client]

default-character-set = utf8mb4

[mysqld]

character_set_server = utf8mb4

collation_server = utf8mb4_general_ci

transaction_isolation = READ-COMMITTED

binlog_format = ROW

innodb_large_prefix=on

innodb_file_format=barracuda

innodb_file_per_table=1

Showing big albums (not “Your Photos”) loads super fast no problem here.

So I wonder is it just on my machine showing this slow scrolling of Your Photos?
Can someone please try the stripped query on his side?
Thanks for any hint

regards
Jochen

1 Like
2

What I found out is that the nextcloud talk makes a huge difference showing images chronologically.
With talk disabled it is much faster.

Beside this the bottleneck also comes from that my mariadb only utilizing one core

3

OK so the long queries comes from a very very long query string (62400 character long!) which is only triggered by photos with talk enabled and takes 4-5sec.
If talk is disabled photos in your photos (chronologically ordered) doesn’t create this strange query anymore

4

Maybe you can test it again with Nextcloud 25 and Photos 2.0.

5

Hi
thanks for your reply. I just update to nextcloud 25 and photo 2.0

It still creates these long queries in the slow.log, how ever the waiting/blocking time on the front end side is hidden better

Time: 221102 21:59:11

User@Host: nextcloud[nextcloud] @ localhost

Thread_id: 27397 Schema: nextcloud QC_hit: No

Query_time: 6.616051 Lock_time: 0.003327 Rows_sent: 200 Rows_examined: 530668

Rows_affected: 0 Bytes_sent: 49563

SET timestamp=1667422751;
SELECT file.fileid, storage, path, path_hash, file.parent, file.name, mimetype, mimepart, size, mtime, storage_mtime, encrypted, etag, permissions, checksum, unencrypted_size FROM oc_filecache file WHERE ((mimetype = 6) OR (mimetype = 11) OR (mimetype = 19) OR (mimetype = 13) OR (mimetype = 20)) AND (((storage = 3) AND ((path = ‘files’) OR (path LIKE ‘files/%’))) OR (storage = 19) OR (storage = 20) OR (storage = 21) OR (storage = 27) OR ((storage = 5) AND ((path = ‘files/photo’) OR (path LIKE ‘files/photo/%’))) OR ((storage = 5) AND ((path = ‘files/multimedia’) OR (path LIKE ‘files/multimedia/%’))) OR ((storage = 5) AND ((path = ‘files/video’) OR (path LIKE ‘files/video/%’))) OR ((storage = 5) AND ((path = ‘files/Rezepte’) OR (path LIKE ‘files/Rezepte/%’))) OR ((storage = 4) AND (path_hash = ‘f5183c2c8445c5a1e91b271e938c1c10’)) OR ((storage = 4) AND (path_hash = '298dac888944faf73db7… query string continue more than a page long…

6

FYI - if someone with same/similar problem ends here…
I continued my try and error (this time not doing crazy things in the database) on cloned instances and I was able to find the problem - an old chat conversation in talk has caused the problem. After deleting this in the (via the web front end) the problem of long sql queries is gone.
Fits to the observation that disabling the talk app also removed the problem…
But why talk is influencing the photo app here, is unclear to me…

best regards
jochen