Announcement

#31 2014-01-12 16:09:48

jimaek
Member
2013-05-17
41

Re: Huge amounts of queries to MySQL

Not sure, you should ask @plg.

Offline

 

#32 2014-01-13 07:06:08

mdp
Member
2013-12-17
18

Re: Huge amounts of queries to MySQL

Thanks. Dear plg, do you have a solution for me too for this? Thanks a lot!

Offline

 

#33 2014-01-13 09:41:18

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

Re: Huge amounts of queries to MySQL

mdp wrote:

Thanks. Dear plg, do you have a solution for me too for this? Thanks a lot!

Not yet :-/

Offline

 

#34 2014-04-13 23:55:40

jimaek
Member
2013-05-17
41

Re: Huge amounts of queries to MySQL

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

 

#35 2014-04-30 17:47:07

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

Re: Huge amounts of queries to MySQL

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

 

#36 2014-04-30 18:34:51

rvelices
Former Piwigo Team
2005-12-29
1960

Re: Huge amounts of queries to MySQL

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

 

#37 2014-04-30 18:49:33

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

Re: Huge amounts of queries to MySQL

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

 

#38 2014-05-02 15:35:28

rvelices
Former Piwigo Team
2005-12-29
1960

Re: Huge amounts of queries to MySQL

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

 

#39 2014-05-05 15:56:56

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

Re: Huge amounts of queries to MySQL

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

 

#40 2014-05-05 16:25:27

rvelices
Former Piwigo Team
2005-12-29
1960

Re: Huge amounts of queries to MySQL

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

 

#41 2014-05-06 13:53:20

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

Re: Huge amounts of queries to MySQL

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

Offline

 

#42 2014-05-06 13:56:48

flop25
Piwigo Team
2006-07-06
7037

Re: Huge amounts of queries to MySQL

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


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

 

#43 2014-05-06 14:35:14

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

Re: Huge amounts of queries to MySQL

After a few minutes (on a Piwigo.com server with ~1k Piwigo) I must say it works pretty well!

Offline

 

#44 2014-05-07 12:49:04

rvelices
Former Piwigo Team
2005-12-29
1960

Re: Huge amounts of queries to MySQL

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

 

#45 2014-05-07 12:58:28

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

Re: Huge amounts of queries to MySQL

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

 

Board footer

Powered by FluxBB

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