forum.coppermine-gallery.net

Support => cpg1.5.x Support => cpg1.5 miscellaneous => Topic started by: ikar on July 04, 2016, 09:27:42 pm

Title: Hosting problem - Slow database queries lot of time
Post by: ikar on July 04, 2016, 09:27:42 pm
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
Title: Re: Hosting problem - Slow database queries lot of time
Post by: Αndré on July 04, 2016, 09:43:06 pm
I had a quick view at the logs and it seems all slow queries looks like this:
Quote
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.
Title: Re: Re: Hosting problem - Slow database queries lot of time
Post by: ikar on July 04, 2016, 10:00:21 pm
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/
Title: Re: Hosting problem - Slow database queries lot of time
Post by: Αndré on July 04, 2016, 10:11:50 pm
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.
Title: Re: Re: Hosting problem - Slow database queries lot of time
Post by: ikar on July 04, 2016, 10:22:27 pm
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?
Title: Re: Hosting problem - Slow database queries lot of time
Post by: ikar on July 06, 2016, 07:14:01 am
Deleted "most viewed pics" + deleted "random pics" + deleted "most rated pics" = no affects :(
Gallery used critically  lot of resources :(
Title: Re: Hosting problem - Slow database queries lot of time
Post by: Αndré on July 06, 2016, 03:26:17 pm
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]
    [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]
                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.
Title: Re: Re: Hosting problem - Slow database queries lot of time
Post by: ikar on July 06, 2016, 03:41:31 pm
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?
Title: Re: Hosting problem - Slow database queries lot of time
Post by: Αndré on July 06, 2016, 03:45:19 pm
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).
Title: Re: Re: Hosting problem - Slow database queries lot of time
Post by: ikar on July 06, 2016, 04:06:01 pm
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.
Title: Re: Hosting problem - Slow database queries lot of time
Post by: Αndré on July 06, 2016, 04:59:42 pm
So do you want to disable meta albums or not?
Title: Re: Re: Hosting problem - Slow database queries lot of time
Post by: ikar on July 06, 2016, 07:57:26 pm
So do you want to disable meta albums or not?

Lets try
Title: Re: Hosting problem - Slow database queries lot of time
Post by: Αndré on July 06, 2016, 08:43:05 pm
Open include/functions.inc.php, find
Code: [Select]
case 'topn': // Most viewed filesand below, add
Code: [Select]
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.
Title: Re: Re: Hosting problem - Slow database queries lot of time
Post by: ikar on July 06, 2016, 08:59:56 pm
Open include/functions.inc.php, find
Code: [Select]
case 'topn': // Most viewed filesand below, add
Code: [Select]
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?
Title: Re: Hosting problem - Slow database queries lot of time
Post by: Αndré on July 06, 2016, 09:09:22 pm
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.
Title: Re: Re: Hosting problem - Slow database queries lot of time
Post by: ikar on July 06, 2016, 09:51:26 pm
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.
Title: Re: Hosting problem - Slow database queries lot of time
Post by: ikar on July 09, 2016, 08:28:36 am
Last 2 days testing - the DB slow query load decreased 10 times!
Thanks
Title: Re: Hosting problem - Slow database queries lot of time
Post by: ikar on July 11, 2016, 12:23:53 pm
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.
Title: Re: Hosting problem - Slow database queries lot of time
Post by: Αndré on July 11, 2016, 01:09:10 pm
107 out of 150 slow queries in your log file are generated by the "lastup" meta album. To disable it, find
Code: [Select]
case 'lastup': // Latest (most recent) uploadsand below, add
Code: [Select]
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.
Title: Re: Re: Hosting problem - Slow database queries lot of time
Post by: ikar on July 11, 2016, 01:29:24 pm
107 out of 150 slow queries in your log file are generated by the "lastup" meta album. To disable it, find
Code: [Select]
case 'lastup': // Latest (most recent) uploadsand below, add
Code: [Select]
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?
Title: Re: Hosting problem - Slow database queries lot of time
Post by: Αndré on July 11, 2016, 04:56:02 pm
Find
Code: [Select]
    // Meta albums
    switch($album) {
and above, add
Code: [Select]
return;to disable all meta albums.


This is a problem of my ISP MySQL DB?

I think so. It seems the MySQL server has a very bad performance, for whatever reason.
Title: Re: Re: Hosting problem - Slow database queries lot of time
Post by: ikar on July 11, 2016, 06:12:45 pm
Find
Code: [Select]
    // Meta albums
    switch($album) {
and above, add
Code: [Select]
return;to disable all meta albums.

Above? Or below?

switch($album) {
return;
...
}

Title: Re: Re: Hosting problem - Slow database queries lot of time
Post by: ikar on July 11, 2016, 06:17:24 pm
Well :)


return;
switch($album) {

...
}
Title: Re: Hosting problem - Slow database queries lot of time
Post by: ikar on July 13, 2016, 02:32:26 pm
Hi. Last day - 0 slow queries
Sorry - I don't understand anything in SQL. I try to send your answer to my ISP.
This is his answer (sorry foe automatically Google translated)
-----------------------------
In this case, particularly the performance of your site galleries are not so important, you need to analyze and optimize database queries it.
Optimization of queries to the database should be limited to the following results:
- In all correspondence indexes are used to retrieve data,
- Minimize the use of temporary files and filesort operations.

All these data for each of the queries can be obtained using EXPLAIN: http://dev.mysql.com/doc/refman/5.6/en/explain.html

Previously, we have provided you slow query log. Analyze requests from any given one of the log files, for example:

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 4505, 5;


Using EXPLAIN, execute this query:
mysql> explain
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
                 LIMIT 4505, 5 -> ORDER BY hits DESC, pid ASC
    -> LIMIT 4505, 5;
+----+-------------+-------+--------+-------------------------------+----------+---------+-------------------------+-------+- ---------------------------- +
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------+----------+---------+-------------------------+-------+- ---------------------------- +
| 1 | SIMPLE | r | range | pic_hits, aid_approved, pic_aid | pic_hits | 4 | NULL | 19381 | Using where; Using filesort |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | wwwikarin_gallery.r.aid | 1 | Using index |
+----+-------------+-------+--------+-------------------------------+----------+---------+-------------------------+-------+- ---------------------------- +
2 rows in set (0.02 sec)


As can be seen, the sample is derived from a table with 19,381 lines. Yes, indexes are used, but the sample data from the 19381 using Using filesort type of request in any case will move all the lines and creating temporary files (which in turn can cause a strain on the drive). Using LIMIT in this case does not bring much good, so going on a first sampling of all the rows, and then cut off in accordance with LIMIT.

And such requests, according to the logs, there is a very large number. This and similar indicators of resources.
Title: Re: Hosting problem - Slow database queries lot of time
Post by: Αndré on July 13, 2016, 10:36:43 pm
They suggest to use (additional) indexes in the pictures table. May be worth a try. If I have some spare time, I'll make some tests with "EXPLAIN" on my local testbed.

If some database expert reads this, feel free to reply with some useful tips :)