Just drop the cache. Make it less complicated, not more.

If there is a "related" thing, make it opt-in.

As standard, just deliver the photos of the album. Doesn't cost any resources.

Zentalquabula wrote:

As standard, just deliver the photos of the album. Doesn't cost any resources.

Plainly wrong, sorry. Even if Piwigo doesn't display anything on the page, it costs resources. The minimal number of SQL queries to generate a page on a fresh Piwigo install must be around 15. That's not much compared to many CMS, but that's not "nothing" :-)

abravorus wrote:

Maybe I'm thinking in a wrong direction, but did you investigate the possibility to use in-memory database (like redis, for instance) to cache the results of the heavy SQL queries like shown above?

The problem is not to write the cache but to compute the cache.

As I describe, the long part of the cache computation is this big SQL query including 2 LEFT JOIN. I don't remember if I did this test before, but maybe I should try another approach: instead of performing the JOIN in SQL, I could fetch several sets of lines from MySQL and work arrays in PHP. I will see.

abravorus wrote:

For big galleries with 100k+ photos and 1k+ albums and for big number of guest users' requests this could be very effective - instead of executing each time heavy SQL query, the result of the first query could be saved for n seconds to in-memory cache and all next queries will be readdressed to read the result from in-memory cache.

That's nearly how the current user_cache works. It does not live for "n seconds" but "as long as it is not reset". Problems are:

* when you reset it too often. This is why we try to reset it less often (that's why I created the "lounge").

* also when you rebuild the same user_cache simultaneously for N http requests (that's why I implemented a "unique execution" mechanism in [Github] Piwigo issue #2311 ).

* eventually when you rebuild the cache for requests that does not need it (that's partly what I fixed in [Github] Piwigo issue #2335 and we will add some exceptions to avoid rebuilding user_cache uselessly)

Of course, making the user_cache faster to compute would be nice, but I keep thinking it's complicated.

I'm afraid Redis won't help in this situation. Anyway, adding Redis as a prerequisite to install Piwigo would be a no-go for me.

I am working on a Piwigo database with 881 albums and 2.9millions photos. Here is the SQL query that takes most of the computation time to build user_cache :

MariaDB [tmp_astro]> SELECT c.id AS cat_id, id_uppercat, global_rank,
    ->   MAX(date_available) AS date_last, COUNT(date_available) AS nb_images
    -> FROM piwiwi_categories as c
    ->   LEFT JOIN piwiwi_image_category AS ic ON ic.category_id = c.id
    ->   LEFT JOIN piwiwi_images AS i
    ->     ON ic.image_id = i.id
    ->       AND i.level<=8
    -> WHERE c.id NOT IN (738,395,567,759,658,161,399,588)
    ->   GROUP BY c.id
    -> ;
873 rows in set (4.127 sec)

Our base time to improve is 4.1s.

Every column is usefully index but images.level. I have added an index on it but i made no difference.

So let's break this big SQL query into smaller ones. I can't request less than that:

MariaDB [tmp_astro]> SELECT id, date_available FROM piwiwi_images WHERE level <= 8;
2984757 rows in set (1.950 sec)

MariaDB [tmp_astro]> select image_id,category_id from piwiwi_image_category;
2984757 rows in set (0.383 sec)

MariaDB [tmp_astro]> SELECT SQL_NO_CACHE id, id_uppercat, global_rank FROM piwiwi_categories;
881 rows in set (0.001 sec)

=> 2.3s

So we see that we can decrease the SQL by 50% BUT here we still have to:

1) store all fetched data in memory and it will take far more than what we currently do
2) loop on each category, loop on each image_category to calculate max(date_available) and count images

I haven't done it but I doubt it will take less than 2 seconds (we have at least 2.9 millions iterations with a date comparison inside).

Current conclusion: user_cache generation can't really be optimized. We need to focus on reseting it less often and generating it less often.

Small precision on my performance tests : if we get rid of images.level, we strongly decrease the time to fetch images from 2s to 0.5s. It could be a valid solution for 90% of Piwigo which don't use privacy_level.