Announcement

  •  » Engine
  •  » 11.x, related albums and performance issues

#1 2021-04-12 18:53:30

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13487

11.x, related albums and performance issues

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

 

#2 2021-04-12 19:15:38

flop25
Piwigo Team
2006-07-06
7036

Re: 11.x, related albums and performance issues

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?


To get a better help : Politeness like Hello-A link-Your past actions precisely described
Check my extensions : more than 30 available
who I am and what I do : http://fr.gravatar.com/flop25
My gallery : an illustration of how to integrate Piwigo in your website

Offline

 

#3 2021-04-18 23:41:31

executive
Member
2017-08-16
1099

Re: 11.x, related albums and performance issues

Is there no way to limit an SQL query to certain time or number of results?

Offline

 

#4 2021-04-25 12:25:54

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13487

Re: 11.x, related albums and performance issues

executive wrote:

Is there no way to limit an SQL query to certain time [...] ?

That would be so cool :-)

Offline

 

#5 2021-05-04 17:20:57

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13487

Re: 11.x, related albums and performance issues

Offline

 

#6 2021-05-04 19:43:20

flop25
Piwigo Team
2006-07-06
7036

Re: 11.x, related albums and performance issues

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


To get a better help : Politeness like Hello-A link-Your past actions precisely described
Check my extensions : more than 30 available
who I am and what I do : http://fr.gravatar.com/flop25
My gallery : an illustration of how to integrate Piwigo in your website

Offline

 

#7 2021-05-08 00:09:14

deemon
Member
2021-05-03
14

Re: 11.x, related albums and performance issues

as a workaround, one can indeed limit SQL query execution time:

Code:

[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

 
  •  » Engine
  •  » 11.x, related albums and performance issues

Board footer

Powered by FluxBB

github twitter newsletter Donate Piwigo.org © 2002-2021 · Contact