Performance Issue Administrator Gallery Images

[hmayer1980]

Hello Jan,

I do not want to bother you, but I have to ask new again because from time to time the admin site gets killed by my hoster for the long execution duration.
I am using phoca gallery 4.1.2 with now a litte over 100.000 pictures...
I have debugged this a little and its because of the mysql queries.
I have attaced a Saved Version of a view of my admin page (including all) while Joomla Debug was enabled to give you all the DB Queries and their runtime. https://www.hmayer.net/index.php/dateig ... erformance (No attachments here )
As you can see there are two queries specifically I am pointing to - Duration > 7seconds each.

My Questions for optimizations would be
- Is there a way to not Query all pictures (113650 rows) as in Query #8
- Is this query identical to query #9 except of the "LIMIT" 0,20 (Paging only here?)
- Query #9 is not significantly faster because of the hugh number of rows in the temp operation
- I would recommend pushing the limit and WHERE further into the query - as SUBQUERY for phocagallery
- this is save because there are only left joins
- and those will then not operate on ALL rows, but only on the "LIMIT" (20) rows we selected
- In my case a query speed difference from 7.6 seconds to 0.3 seconds.

The original Query

Code: Select all

SELECT 
    a.*,
    ...
FROM
    `j25_phocagallery` AS a
        LEFT JOIN
    `j25_languages` AS l ON l.lang_code = a.language
        LEFT JOIN
    j25_users AS uc ON uc.id = a.checked_out
        LEFT JOIN
    j25_users AS uua ON uua.id = a.userid
        LEFT JOIN
    j25_phocagallery_categories AS c ON c.id = a.catid
        LEFT JOIN
    j25_users AS ua ON ua.id = c.owner_id
        LEFT JOIN
    j25_phocagallery_img_votes_statistics AS v ON v.imgid = a.id
WHERE
    (a.published IN (0 , 1))
GROUP BY a.id
ORDER BY a.title ASC
LIMIT 0 , 20

and the proposed change.

Code: Select all

SELECT 
    a.*,
    ...
FROM
    (SELECT 
        *
    FROM
        `j25_phocagallery`
    WHERE
        published IN (0 , 1)
    ORDER BY title ASC
    LIMIT 0 , 20) AS a
        LEFT JOIN
    `j25_languages` AS l ON l.lang_code = a.language
        LEFT JOIN
    j25_users AS uc ON uc.id = a.checked_out
        LEFT JOIN
    j25_users AS uua ON uua.id = a.userid
        LEFT JOIN
    j25_phocagallery_categories AS c ON c.id = a.catid
        LEFT JOIN
    j25_users AS ua ON ua.id = c.owner_id
        LEFT JOIN
    j25_phocagallery_img_votes_statistics AS v ON v.imgid = a.id
GROUP BY a.id
ORDER BY a.title ASC

Anything possible there?
Hannes

PS: Also on the gallery frontend there are a lot of queries which are executed twice?

[Jan]

Hi, regarding SQL queries:

BACKEND:

- there can be problem when new images are added and the system search for images which do not have thumbnails yet and when it finds them, then it makes the thumbnails - see Info panel in Phoca Gallery, and set recommended settings - when thumbnails are made, it helps when pagination thumbnail creating is enabled.

FRONTEND:

- mostly there is no problem with images, as images are loaded in pagination - e.g. only 20 images on one site

- but the problem can be with categories - if there are a lot of categories - because you need to load all categories at once and create tree from them - this is why it can be problematic when there are a lot of categories

DOUBLE QUERIES - hmm, this can be due to using e.g. module - the module can load the same query as component. Sometimes there are special cases when it is better to load the same query, this is for example by checking different rights - you need to check if the user has rights to see the categories, etc.

But there should not be a lot of them, hmmm, can you paste the queries which are loaded twice, maybe there can be some problem in MVC of Joomla! etc, ???

Jan

[hmayer1980]

Hi,

the original Post is a Backend only Problem. Since I could not attach something I have linked to an MHT File which gives you a view of the Queries and Durations. My Proposal would still be a query rewrite as above.
Thumbnail Generation is DISABLED (since almost two years) - I generate all thumbnails myself and upload them with the Pictures.

The Link from above with the stored Version of an Admin View with Joomla DEBUG is still valid.
https://www.hmayer.net/index.php/dateig ... erformance

For the second question I have created a second MHT Archive with a view of the Frontend while Joomla DEBUG was enabled.
https://www.hmayer.net/index.php/dateig ... atequeries

I wonder if the queries are correct as executed... and if you could do something to Change them at all?

Hannes

[Jan]

Hi, which program you are using for opening MHT, I have tried IE but it requires password and the sql queries are not visible there ???

Jan

[hmayer1980]

Hi,
Its the website saved in IE - so it is possible to open in IE. It asks for a password because dev.hmayer.net is password protected, but that is only because it still tries to download some CSS / Script etc.. from there. Just Ignore the password requests. I had to cancel 7 or 8 times, then the page is showing. And also at the far bottom of the page - in the admin also on the far right in IE - is the joomla debug part where when you click on "Database Queries" it still shows them. This part of the script works on my IE - even if I deleted the browser cache before....
IT should work...
Hannes

[Jan]

Hi, see image, the problem is, I cannot see the queries, as they are aligned right but IE does not allow to scroll to right

[Jan]

EDIT: now I see, in admin there is one sql query doubled, I will test it but this seems like it is somehow related with loading the items in MVC

Jan

[Jan]

Hmmm, testing now, this is related to pagination.

First, the info about all the items and then limited select of the items.

I have written it to feature request list, to see if I can somehow limit the second query (the problem is, Phoca Gallery only builds the query so the using of it is made in JModelList class which is parent of the model class which cares about listing and paginating the items.

Jan

[hmayer1980]

Hi,

I have now added two PDF Files with the printed (PDF) Screens I saved as mht before.

You can download them form this filegallery. https://www.hmayer.net/index.php/dateig ... ocagallery

I see and understand the issue if this comes through the joomla Framework this should be a General query Performance improvment for the joomla Framework then. It must get slow in the backend too if you have 100.000 Articles or so too! Don´t you think?

Thanks for Investigation.
Regards
Hannes

[Jan]

Hi, it is not so easy as it looks - There are more ways how to use MVC in Joomla! Not sure, but maybe articles are not using legacy like Phoca Gallery (JModel JModelList JModelAdmin)

For now, there are two the same queries - one get the lists of images (e.g. only 20 - depends on pagination) second to count the pagination values (the part which is displayed below - the numbers, next and previous) and thers is no limit as all items need to be counted.

I have written it to feature request list - to see if there is no other way but as written, really no idea as the pagination is done in model parts of Joomla!

Jan