Hello,
Piwigo 11 has been released for 2 months and while deploying it on Piwigo.com we have encountered some performances issues linked to the "related albums" new features.
For example, imagine you have a photo library with:
* 50k photos (that's a "moderate size" in Piwigo world)
* dispatched in 4k albums (that's a lot)
* with 150 tags (very common and usual)
Now imagine you open a tag, which is linked to 10k photos. Piwigo 11 will search for the related albums of this huge set of photos. The SQL query will take 5 seconds (that's absolutely too much) and nearly all of your 4k albums will be in the results (which makes the feature useless, by the way).
That's a real example on Piwigo.com, and several photo libraries may have the same kind of metrics. The immediate solution we implemented was to deactivate this new feature "related albums".
We obviously have to optimize that and I see 2 solutions to combine:
1) if the number of $page['items'] (here the number of photos linked to the tag) is beyond a limit (let's say 1k), then Piwigo does not try to find related albums
2) we only display the X "most related" albums, and not all. We do the same for related tags, with $conf['menubar_tag_cloud_items_number'] = 20; It does not solve the performance issue, but I don't see the point of displaying a menu with the 4k albums...
Offline
Hi Pierrick
I agree that both solutions should be implemented and both should a config variable.
Maybe a maximum execution time as an argument should be used too?
Offline
Is there no way to limit an SQL query to certain time or number of results?
Offline
executive wrote:
Is there no way to limit an SQL query to certain time [...] ?
That would be so cool :-)
Offline
Offline
plg wrote:
executive wrote:
Is there no way to limit an SQL query to certain time [...] ?
That would be so cool :-)
when I posted last mounth, I found out that:
https://dev.mysql.com/doc/refman/8.0/en … ution-time
available in 5.7 too https://dev.mysql.com/doc/refman/5.7/en … ution-time
Offline
as a workaround, one can indeed limit SQL query execution time:
[mysqld] # For MySQL 5.7+ only (disabled by default) #max_execution_time = 30000 # Set a timeout limit for SELECT statements (value in milliseconds). # This option may be useful to address aggressive crawling on large sites, # but it can also cause issues (e.g. with backups). So use with extreme caution and test! # More info at: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time # For MariaDB 10.1.1+ only (disabled by default) #max_statement_time = 30 # The equivalent of "max_execution_time" in MySQL 5.7+ (set above) # The variable is of type double, thus you can use subsecond timeout. # For example you can use value 0.01 for 10 milliseconds timeout. # More info at: https://mariadb.com/kb/en/aborting-statements/
Mind you, the my.cnf setting is different in actual MySQL and MariaDB. So you need to uncomment the one you actually need for your chosen DB.
Last edited by deemon (2021-05-08 00:09:54)
Offline