Large Number of Albums Slow Performance

[Rodolfo]

I'm not sure how that will translate to chevereto but I'm satisfied with postgresql

Me neither, but as PostgreSQL allows to put "more logic" into the database it could leverage a lot of resources that are currently taken by the application layer. Also, the support for complex queries is way better. Just to be able to work with it feels like a breath of new air and possibilities. You will glad to know that I'm learning it already and that everyday I see it as the best alternative for us.

However, do note that I don't like systems with the flexibility of choosing the database layer. It will be either MySQL/MariaDB or PostgreSQL, there won't be both happening at the same time. I've learned that when you design systems you can't afford to have such flexibility, and the better framing the easier incremental development gets and lesser debt.

 

[konj]

However, do note that I don't like systems with the flexibility of choosing the database layer.

As i have read and researched so far, PostgreSQL seems to have strong development since 20+ years, its already about 34 years old company, and all the things and functions i have looked into (as example their analyze option explain is pretty good and nice), in longterm i would say it could be right choice, because PostgreSQL offers better performance overall, known for boasting fast read and write times, as well as quick data acces makes PostgreSQL good choice for People looking for the highest sql performance database avilable, additionally, postgresql includes some features for database optimizations like partial indexes, the only little "negative" thing i could find out, its the fact that PostgreSQL is more hungry for resources compared to MariaDB, and to be honest, every "serious" hosting provider is supporting both or even more DBMS (Database managment systems), and i mean, how hard can it be to install and setup PostgreSQL on own Server at end, so for whatever u go at end Rodolfo i appreciate it.

 

[twm]

I think i have the same problem
Site data
images: 13M

Not all albums will have problems,
Only albums with more than about 10,000 pictures

server
chevereto 3.7.0/3.7.1
i7-7700/64G/512G NVMe/CentOS 7

php 7.4
MariaDB 10.3

key_buffer_size 4096MB
query_cache_size 2048MB
tmp_table_size 4096MB

innodb_buffer_pool_size = 24576M
innodb_log_file_size = 2048M
innodb_log_buffer_size = 2048M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# Time: 201227 13:08:51
# User@Host: demo_imgdata[demo_imgdata] @ localhost []
# Thread_id: 7197 Schema: demo_imgdata QC_hit: No
# Query_time: 11.546996 Lock_time: 0.000065 Rows_sent: 65 Rows_examined: 5856520
# Rows_affected: 0 Bytes_sent: 70070
SET timestamp=1609045731;
SELECT * , NULL as image_original_exifdata FROM (SELECT * FROM chv_images
WHERE chv_images.image_album_id='42090' AND chv_images.image_is_approved = 1 AND image_size > 0
ORDER BY chv_images.image_id DESC
LIMIT 65) 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_albums ON chv_images.image_album_id = chv_albums.album_id
LEFT JOIN chv_categories ON chv_images.image_category_id = chv_categories.category_id
LEFT JOIN chv_likes ON chv_likes.like_content_type = "image" AND chv_likes.like_content_id = chv_images.image_id AND chv_likes.like_user_id = 1
ORDER BY chv_images.image_id DESC;
# Time: 201227 13:09:13
# User@Host: demo_imgdata[demo_imgdata] @ localhost []
# Thread_id: 7276 Schema: demo_imgdata QC_hit: No
# Query_time: 11.765501 Lock_time: 0.000062 Rows_sent: 65 Rows_examined: 5888204
# Rows_affected: 0 Bytes_sent: 70102
SET timestamp=1609045753;
SELECT * , NULL as image_original_exifdata FROM (SELECT * FROM chv_images
WHERE chv_images.image_album_id='42021' AND chv_images.image_is_approved = 1 AND image_size > 0
ORDER BY chv_images.image_id DESC
LIMIT 65) 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_albums ON chv_images.image_album_id = chv_albums.album_id
LEFT JOIN chv_categories ON chv_images.image_category_id = chv_categories.category_id
LEFT JOIN chv_likes ON chv_likes.like_content_type = "image" AND chv_likes.like_content_id = chv_images.image_id AND chv_likes.like_user_id = 1
ORDER BY chv_images.image_id DESC;
/xxxxx/xxxxxx/mysql/bin/mysqld, Version: 10.3.16-MariaDB-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
# Time: 201227 13:44:40
# User@Host: demo_imgdata[demo_imgdata] @ localhost []
# Thread_id: 6741 Schema: demo_imgdata QC_hit: No
# Query_time: 19.914454 Lock_time: 0.000095 Rows_sent: 65 Rows_examined: 5888204
# Rows_affected: 0 Bytes_sent: 70102
use demo_imgdata;
SET timestamp=1609047880;
SELECT * , NULL as image_original_exifdata FROM (SELECT * FROM chv_images
WHERE chv_images.image_album_id='42021' AND chv_images.image_is_approved = 1 AND image_size > 0
ORDER BY chv_images.image_id DESC
LIMIT 65) 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_albums ON chv_images.image_album_id = chv_albums.album_id
LEFT JOIN chv_categories ON chv_images.image_category_id = chv_categories.category_id
LEFT JOIN chv_likes ON chv_likes.like_content_type = "image" AND chv_likes.like_content_id = chv_images.image_id AND chv_likes.like_user_id = 1
ORDER BY chv_images.image_id DESC;