Announcement

#1 2019-03-17 14:00:16

Undergrid
Member
2019-03-13
25

Guest user causes entire image list to be loaded.

Hi All,

So I've been working on optimising my 166 thousand image piwigo gallery and I noticed that guest users were loading pages a lot slower than my admin user. 

For example, the admin user loads category 1 with a (chrome reported) wait time of 141ms.  The guest user takes between 3.95 seconds and over 10 seconds.

So I looked at the queries and found the following:

For the administrator:

Code:

[11] 
SELECT *
  FROM piwigo_categories
  WHERE id = 1
;
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.017 s)
(num rows        : 1 )

[11] 
SELECT DISTINCT(image_id)
  FROM piwigo_image_category
    INNER JOIN piwigo_images ON id = image_id
  WHERE
    category_id = 1

  ORDER BY file ASC, id ASC
;
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.017 s)
(num rows        : 0 )

[12] 
SELECT DISTINCT category_id
  FROM piwigo_category_filters
;
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.017 s)
(num rows        : 1 )

[13] 
SELECT pt.id, pt.visible, pt.nfo, ptl.lang, ptl.title, ptl.content
          FROM piwigo_amm_personalised pt
            LEFT JOIN piwigo_amm_personalised_langs ptl
            ON pt.id=ptl.id
          WHERE (ptl.lang = '*' OR ptl.lang = 'en_GB')  AND ptl.content != ''  ORDER BY pt.id, ptl.lang ASC 
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.017 s)
(num rows        : 0 )

And for the guest:

Code:

[12] 
SELECT *
  FROM piwigo_categories
  WHERE id = 1
;
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.031 s)
(num rows        : 1 )

[12] 
SELECT DISTINCT(image_id)
  FROM piwigo_image_category
    INNER JOIN piwigo_images ON id = image_id
  WHERE
    category_id = 1
AND (category_id NOT IN (1694,675,676,677,678,1340,1373,1363,1361,1365,1367,1343,1347,1345,1359,1379,1383,1355,1375,1357,1377,1349,1381,1371,1353,1351,1369,3090,3057,3058,3073,3074,5209,2846))
  ORDER BY name ASC,file ASC, id ASC
;
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.031 s)
(num rows        : 0 )

[13] 
SELECT DISTINCT category_id
  FROM piwigo_category_filters
;
(this query time : 0.000 s)
(total SQL time  : 0.001 s)
(total time      : 0.031 s)
(num rows        : 1 )

[14] 
SELECT id
  FROM piwigo_categories
  WHERE
    (id NOT IN (1694,675,676,677,678,1340,1373,1363,1361,1365,1367,1343,1347,1345,1359,1379,1383,1355,1375,1357,1377,1349,1381,1371,1353,1351,1369,3090,3057,3058,3073,3074,5209,2846,5234))
(this query time : 0.000 s)
(total SQL time  : 0.002 s)
(total time      : 0.032 s)
(num rows        : 3771 )

[15] 
SELECT DISTINCT(image_id)
  FROM piwigo_image_category
    INNER JOIN piwigo_images ON id = image_id
  WHERE
    category_id IN (1,3, ...)

  ORDER BY name ASC,file ASC, id ASC
;
(this query time : 10.852 s)
(total SQL time  : 10.853 s)
(total time      : 10.884 s)
(num rows        : 168736 )

[16] 
SELECT pt.id, pt.visible, pt.nfo, ptl.lang, ptl.title, ptl.content
          FROM piwigo_amm_personalised pt
            LEFT JOIN piwigo_amm_personalised_langs ptl
            ON pt.id=ptl.id
          WHERE (ptl.lang = '*' OR ptl.lang = 'en_GB')  AND ptl.content != ''  ORDER BY pt.id, ptl.lang ASC 
(this query time : 0.000 s)
(total SQL time  : 10.854 s)
(total time      : 10.947 s)
(num rows        : 0 )

Two things to note about the above:

1) I removed a huge list if category numbers in query 15 for the guest for clarity
2) Yes, there actually are two number 11 queries for the admin and two number 12 queries for the guest listed on the generated web pages.

Note that query 15 ends up selecting every image in the database and then, just to make things worse, sorts them on fields that aren't indexed in the database

So the administrator seems to skip queries 14 and 15 (from the guest list) no apparent ill effect on the web page, but the guest account basically thrashes the database.

Looking at the code, I believe query 15 is in section_init_inc.php starting at line 308 and it appears to be populating a cache with the photos, though I've no idea why or why the admin skips it.

Can anyone advise?


Piwigo version: 2.9.4
PHP version: 7.0.30-0+deb9u1
MySQL version:  Ver 15.1 Distrib 10.1.26-MariaDB
Piwigo URL: available privately on request (I don't want to thrash the machine while this is an issue)

Offline

 

#2 2019-03-18 23:22:02

flop25
Piwigo Team
2006-07-06
7037

Re: Guest user causes entire image list to be loaded.

are you using the flat view?


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 2019-03-19 17:48:08

Undergrid
Member
2019-03-13
25

Re: Guest user causes entire image list to be loaded.

Nope, just the standard view.  In fact, category 1 doesn't have any images in it directly at all, just subcategories which contain other categories and (eventually) images.

Offline

 

#4 2019-03-19 19:28:13

Undergrid
Member
2019-03-13
25

Re: Guest user causes entire image list to be loaded.

So after flop25's question I'm looking at this code a little closer and there's something odd...

To get the "category_id IN" where clause (line 285), it appears the server not only thinks that flat mode is set (line 265), but also that every category in the gallery is a child of the current category ($subcat_ids has to contain every category ID).  it's worth noting that, in the guest sql logs above, while we're visiting category 1, the first category listed in the where clause is 1 and I don't believe piwigo allows categories to be a child of themselves.

Interestingly the "if (isset($page['flat']))" on line 265 doesn't check the value of the parameter, just if it's set or not, so "$page['flat'] === false" would be treated the same as "$page['flat'] === true" or even "$page['flat'] === 42".

After a little poke around in the personal plugins and template, I manage to dump $page to the smarty debugger and... flat isn't defined in the same run as that query which has a "category_id IN" where clause...

Umm...

Offline

 

#5 2019-03-19 19:37:42

Undergrid
Member
2019-03-13
25

Re: Guest user causes entire image list to be loaded.

Another interesting fact...

Following back the logic branch that would have gotten me a "category IN" we should see the execution of another query with a rather distinctive "WHERE uppercats LIKE" (line break removed) but that isn't showing in the log.

I'd think I was looking at the wrong page, but the only other "SELECT DISTINCT(image_id)" are in the batch manager.

Offline

 

#6 2019-03-19 22:09:46

Undergrid
Member
2019-03-13
25

Re: Guest user causes entire image list to be loaded.

Okay, so ignore all the stuff above.  Turns out there's a similar query in a the SmartAlbums plugin and that's the one that's causing the issue.  I'll be disabling the plugin and following this up with the author.

Offline

 

#7 2019-03-20 07:06:59

executive
Member
2017-08-16
1214

Re: Guest user causes entire image list to be loaded.

I don't think the original author is developing it any further.

Offline

 

Board footer

Powered by FluxBB

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