Hosting problem - Slow database queries lot of time

[ikar]

Hi all. The problem is - my  hosting with gallery (gallery data base ) uses a lot of resources - Slow database queries.
this problem has place on previous version (1.4.x - 1.5.2x). Just now upgrade  for last stab version 1.5.42 - no changes - a lot of slow queries. But the statistic site (liveinternet)  show 0-5 maximum 10 open pages in \Gallery\* - I don't understand this!
I asked hoster to show me logs of slow queries. He did - but I do not understand this. Anybody can help me?
Thanks

[Αndré]

I had a quick view at the logs and it seems all slow queries looks like this:

SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title
                FROM cpg14x_pictures AS r
                INNER JOIN cpg14x_albums AS a ON a.aid = r.aid
                WHERE (1)
                AND approved = 'YES'
                AND hits > 0
                ORDER BY hits DESC, pid ASC
                 LIMIT 3078 ,5;

(the "LIMIT" clause is the only variable part).

This query originates in the "topn" (Most viewed files) meta album. To fix the slow queries, there are just 2 possibilities IMHO:
1. deactivate the "most viewed files" feature
2. move to a more powerful host

I don't know how much files you have in your gallery, but my personal gallery on a shared host with ~ 85k files runs just fine. A link to your gallery might help to rate your issue.

[ikar]

I had a quick view at the logs and it seems all slow queries looks like this:(the "LIMIT" clause is the only variable part).

This query originates in the "topn" (Most viewed files) meta album. To fix the slow queries, there are just 2 possibilities IMHO:
1. deactivate the "most viewed files" feature
2. move to a more powerful host

I don't know how much files you have in your gallery, but my personal gallery on a shared host with ~ 85k files runs just fine. A link to your gallery might help to rate your issue.

Thanks a lot! I will try to delete "most viewed files" option. But... any days there was no one viewed gallery pages but a lot of recourse is used.
I think if I will show my gallery link - me hoster will kill me for lot of recourse I used
I have about 50k photos in gallery.
http://www.ikar.in/Gallery/

[Αndré]

Again, just a quick view at your gallery, but all accesses to your gallery while having a look at the thumbnail view of your gallery where quite fast (http://www.ikar.in/Gallery/thumbnails.php?album=topn&cat=0). The issue occurs while opening a picture from that view (how we call "intermediate-sized view). By the way, you've "just" 20k (public) pictures in your gallery. Please enable debug more for everyone, so I can have a more detailed look at the queries. I assume it'll reveal the same result as in your log files, but I just want to make sure.

[ikar]

Again, just a quick view at your gallery, but all accesses to your gallery while having a look at the thumbnail view of your gallery where quite fast (http://www.ikar.in/Gallery/thumbnails.php?album=topn&cat=0). The issue occurs while opening a picture from that view (how we call "intermediate-sized view). By the way, you've "just" 20k (public) pictures in your gallery. Please enable debug more for everyone, so I can have a more detailed look at the queries. I assume it'll reveal the same result as in your log files, but I just want to make sure.

http://www.ikar.in/index1.html - the full "gallery" - more than 100k photos. Because this problem - I did not include all my photos into copp gallery.
I have turned "log all" - did you ask about this?

[ikar]

Deleted "most viewed pics" + deleted "random pics" + deleted "most rated pics" = no affects
Gallery used critically  lot of resources

[Αndré]

Not sure what you mean with

Deleted "most viewed pics" + deleted "random pics" + deleted "most rated pics"

as I can still see the links in your gallery and those meta albums still work.

A look at e.g. http://www.ikar.in/Gallery/displayimage.php?album=topn&cat=0&pid=9503#top_display_media shows some things:
- there are some queries, which all take about 130 ms
- there's a query, which takes about 4 seconds:

Code Select Expand

    [29] => SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title
                FROM cpg14x_pictures AS r
                INNER JOIN cpg14x_albums AS a ON a.aid = r.aid
                WHERE (1)
                AND approved = 'YES'
                AND hits > 0
                ORDER BY hits DESC, pid ASC
                 LIMIT 0 ,5 [include/functions.inc.php:1683] (4054.07 ms)

As a comparison, the same query in my gallery, which has about 84k pictures:

Code Select Expand

                AND hits > 0 [include/functions.inc.php:1594] (677.51 ms)
    [55] => SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title
                FROM cpg130_pictures AS r
                INNER JOIN cpg130_albums AS a ON a.aid = r.aid
                WHERE (1)
                AND approved = 'YES'
                AND hits > 0
                ORDER BY hits DESC, pid ASC
                 LIMIT 0 ,5 [include/functions.inc.php:1612] (505.11 ms)

I run my gallery on an average shared host (i.e. nothing "special" or high performance setup), so I think there's something wrong with your MySQL server performance.

[ikar]

Not sure what you mean withas I can still see the links in your gallery and those meta albums still work.

I had removed link to "most viewed" and other "most ... sand top...." from the main page of Gallery. It was shown the bottom). I think you mean deactivated - its mean remove from page setting in config menu. I don't know how I can deactivate it (and links to) "most ..."
But I see - this deactivation is only try to resolve MySQL DB of my hosting?

[Αndré]

If you really want to disable one or more meta albums, I can help you with that. Just removing the link won't deactivate them, as they can still be accessed via their URL (e.g. found via Google or even used by their crawler).

[ikar]

If you really want to disable one or more meta albums, I can help you with that. Just removing the link won't deactivate them, as they can still be accessed via their URL (e.g. found via Google or even used by their crawler).

I think nobody used Google to find direct link for my gallery meta albums Most dangerous I think - a link to this meta albums into a heard of every gallery page.
But sometimes then I seen statistics of lieavinternet - I have seen a 0 (zero!!!) visitors on any \Gallery\* pages and a lot of resource on hosting at the same days - I don't understand THIS! And so - there is no correlation between numbers of visitors Gallery's pages and hosting resource. And more - sometimes I have a lot of visitors in a few days - and no problems with limit in these days.

[Αndré]

So do you want to disable meta albums or not?

[ikar]

So do you want to disable meta albums or not?

Lets try

[Αndré]

Open include/functions.inc.php, find

Code Select Expand

case 'topn': // Most viewed files
and below, add

Code Select Expand

return;

The above code can be found twice in include/functions.inc.php, so make sure to modify both locations.

That change will disable the "most viewed" meta album, which causes the slow queries in your log files.

[ikar]

Open include/functions.inc.php, find

Code Select Expand

case 'topn': // Most viewed files
and below, add

Code Select Expand

return;

The above code can be found twice in include/functions.inc.php, so make sure to modify both locations.

That change will disable the "most viewed" meta album, which causes the slow queries in your log files.

Done. Thnks.
Will see results late
Are you shure the slow queries was in topn section only?

[Αndré]

I haven't looked each query of your log files, but just took some samples. But all samples were queries of the "topn" meta album.

[ikar]

I haven't looked each query of your log files, but just took some samples. But all samples were queries of the "topn" meta album.

Ok. Thanks.

[ikar]

Last 2 days testing - the DB slow query load decreased 10 times!
Thanks

[ikar]

No. Not so good. Problem came again. After two days I try to test Gallery - I've opened any galleries, seen any pics and so on. Nothing special - seen 20-50 pics in different galleries. No sorted, no marked, no voted, no commented - just only seen pics. Result - a lot of slow queries once again.
New log - attached.
Help me psl once more time.

[Αndré]

107 out of 150 slow queries in your log file are generated by the "lastup" meta album. To disable it, find

Code Select Expand

case 'lastup': // Latest (most recent) uploads
and below, add

Code Select Expand

return;

I haven't searched for the other 43 slow queries, but I assume they'll also be generated by a meta album. If you want to disable all meta albums, let me know.

[ikar]

107 out of 150 slow queries in your log file are generated by the "lastup" meta album. To disable it, find

Code Select Expand

case 'lastup': // Latest (most recent) uploads
and below, add

Code Select Expand

return;

I haven't searched for the other 43 slow queries, but I assume they'll also be generated by a meta album. If you want to disable all meta albums, let me know.

Yes - I do. Lets disable all meta albums. But! I need to understand - why is this problems take place? This is a problem of my ISP MySQL DB? Or it is a problem of my Gallery setting, configuration or structure?