Not sure, you should ask @plg.
Offline
Thanks. Dear plg, do you have a solution for me too for this? Thanks a lot!
Offline
mdp wrote:
Thanks. Dear plg, do you have a solution for me too for this? Thanks a lot!
Not yet :-/
Offline
Hey, any news on this?
I see there been some updated. Any ETA on fixing the performance issues?
Currently the server gets huge loads even after our fixes.
Offline
I'm currently looking for mysql slowliness on Piwigo.com. The number 1 problem (after AntiAspi, which is under improvement) is this problem of canonical URL and automatic flat mode.
flop25 wrote:
if we add the category parameter, how to deal with pictures in several albums? Will google consider as duplicate content?
The canonical URL must be unique for a give photo, but I want it to include a "default" album. If we're browsing picture.php?/123/category/18 the canonical may be picture.php?/123/category/6
To keep working with URL registered by search engines, I propose to automatically redirect picture.php?/123 to picture.php?/123/category/6 before the big SQL query.
flop25 wrote:
We should reconsider the display of the canonical picture page: non flat computation if we are not really in the flat mode (/flat in the url), so no next/previous, and highlighting more the albums containing the current picture. So we can let the flat mode
It sound like a good idea to me.
Offline
plg wrote:
To keep working with URL registered by search engines, I propose to automatically redirect picture.php?/123 to picture.php?/123/category/6 before the big SQL query.
Please optional! I don't see the reason why we should not be able to display the "piwigo photo stream".
Offline
rvelices wrote:
plg wrote:
To keep working with URL registered by search engines, I propose to automatically redirect picture.php?/123 to picture.php?/123/category/6 before the big SQL query.
Please optional! I don't see the reason why we should not be able to display the "piwigo photo stream".
I also use the flat mode :-). I just agree with flop25 who proposes that we maintain the flat mode only if it is explicitly requested in the url picture.php?/123/flat
Offline
Or even better. Why not use a file cache mechanism for this query. We could have a generic cache that could be used for example in your recent random image issue, quick search results etc... someone might one day write a memcached version...
Offline
A file cache or a new column piwigo_user_cache.flat_image_ids? I'll make some tests.
A file cache would not require to wait for Piwigo 2.7 (for example _data/user_cache/18.cache.php for user 18, would contain a serizaled array with image_ids). The "problem" is to correctly reset cache when it's needed. We can check on piwigo_user_cache.cache_update_time
Offline
plg wrote:
A file cache or a new column piwigo_user_cache.flat_image_ids? I'll make some tests.
A file cache would not require to wait for Piwigo 2.7 (for example _data/user_cache/18.cache.php for user 18, would contain a serizaled array with image_ids). The "problem" is to correctly reset cache when it's needed. We can check on piwigo_user_cache.cache_update_time
File cache. The "key" should contain the number of available images and also the sql query retrieving the images (e.g. forbidden_images, forbidden_categories etc ...). Choosing a reasonable cache of 2 hours will avoid cases when one image becomes unavailable and another one becomes availble ...
Offline
Let's make a first test with this... include/section_init.inc.php, line 325, replace:
$page['items'] = array_from_query($query, 'image_id');
by
if ('1=1' == $where_sql) // flat on the whole gallery, many items... { $cache_path = $conf['data_location'].'flat-u'.$user['id'].'.cache.php'; if (file_exists($cache_path) and filemtime($cache_path) > time()-60*5) // 5 minutes validity { pwg_debug('before flat cache read'); $page['items'] = unserialize(file_get_contents($cache_path)); pwg_debug('after flat cache read'); } else { pwg_debug('before flat cache query'); $page['items'] = array_from_query($query, 'image_id'); pwg_debug('before flat cache write'); file_put_contents($cache_path, serialize($page['items'])); pwg_debug('after flat cache write'); } } else { $page['items'] = array_from_query($query, 'image_id'); }
With a 50k photos gallery, on my test environment (Linux, i5-2500, slow disk not SSD), I get:
* SQL query ~= 2000ms
* cache write ~= 15ms
* cache read ~= 15ms
* cache size ~= 1MB
Offline
plg wrote:
Let's make a first test with this... include/section_init.inc.php, line 325, replace:
Code:
$page['items'] = array_from_query($query, 'image_id');by
Code:
if ('1=1' == $where_sql) // flat on the whole gallery, many items... { $cache_path = $conf['data_location'].'flat-u'.$user['id'].'.cache.php'; if (file_exists($cache_path) and filemtime($cache_path) > time()-60*5) // 5 minutes validity { pwg_debug('before flat cache read'); $page['items'] = unserialize(file_get_contents($cache_path)); pwg_debug('after flat cache read'); } else { pwg_debug('before flat cache query'); $page['items'] = array_from_query($query, 'image_id'); pwg_debug('before flat cache write'); file_put_contents($cache_path, serialize($page['items'])); pwg_debug('after flat cache write'); } } else { $page['items'] = array_from_query($query, 'image_id'); }With a 50k photos gallery, on my test environment (Linux, i5-2500, slow disk not SSD), I get:
* SQL query ~= 2000ms
* cache write ~= 15ms
* cache read ~= 15ms
* cache size ~= 1MB
I will tets that on my website too
Offline
After a few minutes (on a Piwigo.com server with ~1k Piwigo) I must say it works pretty well!
Offline
plg wrote:
After a few minutes (on a Piwigo.com server with ~1k Piwigo) I must say it works pretty well!
So ok with this mechanism ?
Offline
rvelices wrote:
plg wrote:
After a few minutes (on a Piwigo.com server with ~1k Piwigo) I must say it works pretty well!
So ok with this mechanism ?
Yes, I think this very specific case (flat on whole gallery) strongly benefits from a file cache. Maybe it should be even more specific : only for guest user. On my test, only 1.7% of Piwigo have generated a cache for another user (user_id!=2).
Maybe we should even consider the count($page['items']) to decide if it's relevant to generate a cache. Maybe that it's useless under 10k items.
I don't like the fact that the cache file might be invalid and still used. So I think invalidate_user_cache() should simply remove cache files as well : no need to have a X minutes validity and it will certainly keep the cache much longer (less cache generation = less database processing)
Offline