Images that belongs to Album with more than 3,199 images load slow

[lovedigit]

▶ Reproduction steps

1. Create an album
2. Upload exactly 3,199 images (I used import tool)
3. Open any image in that album. it will load at normal speed. No stress on database as well.
4. Add 1 more image
5. Open any image in that album. Now it takes about 5-10 seconds for first byte.

😢 Unexpected result

This is very specific number. I don't think it is exactly about large database queries as we previously thought. Because 1 image shouldn't make day and night difference.
If album has less than 3,200 images, any image in the album loads fine. At exactly 3,200 images, it starts to load slow.
The album listing, user account listing, and other website operations are not affected regardless of number of images in album.

I did test it locally as well. It has the same issue.

📃 Error log message

No log, except slow mysql queries.


[CODE title="mysql slow query log"]# User@Host: chevereto[chevereto] @ localhost []
# Thread_id: 339585 Schema: chevereto QC_hit: No
# Query_time: 10.649693 Lock_time: 0.000110 Rows_sent: 6 Rows_examined: 760815
# Rows_affected: 0 Bytes_sent: 15124
SET timestamp=1621098219;
SELECT * , NULL as image_original_exifdata FROM (SELECT * FROM chv_images
LEFT JOIN chv_albums ON chv_images.image_album_id = chv_albums.album_id
WHERE chv_images.image_album_id=2 AND chv_images.image_id <= 223239 AND chv_images.image_is_approved = 1 AND image_size > 0 AND (chv_albums.album_privacy NOT IN ('private','private_but_link','password','custom') OR chv_albums.album_privacy IS NULL)
ORDER BY chv_images.image_date_gmt DESC, chv_images.image_id DESC
LIMIT 6) chv_images
LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id
LEFT JOIN chv_users ON chv_images.image_user_id = chv_users.user_id
LEFT JOIN chv_categories ON chv_images.image_category_id = chv_categories.category_id
ORDER BY chv_images.image_date_gmt DESC, chv_images.image_id DESC;[/CODE]

 

[twm]

My website has the same issue, but this issue only occurs when there are more than 100,000 pictures in the album.
I am not sure if it is caused by server performance.

Chevereto v3.18.1
Images: 26M+

AMD Ryzen 3700x
64G RAM
1TB NVMe
CentOS 7

 

[lovedigit]

The reason why I say that it is not related to hardware limitation or database being slow, is that in my case it only happens on certain accounts.
My admin account has this issue. Other users have not reported it so far.

Also, if I run the slow queries directly, it returns within fraction of a second, but when it is run through the application, it is somehow slow. Again only for my account that I know of.
So, I suspect it is affecting admins atm?

What @twm has reported is similar to what I face, but it is not the same. None of my user has single album with 100,000 images.
On my account, images start to load slow after exactly 3,200 images. The album listing itself loads as usual, but individual images are slow.
Mysql slow query log reports that some of these queries takes 10s or move to complete. Again, if I run those queries directly on the database, it is fast. Completes in 0.002s.

Boggles my mind. Other non-admin user has 30k+ image in an album, but I can see images from that album as usual, no slow queries, or any unusual load time.

Which leads me to believe that there is something on the application layer that has limitation in certain cases.