Advanced search  

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Pages: [1] 2   Go Down

Author Topic: Hosting problem - Slow database queries lot of time  (Read 11879 times)

0 Members and 1 Guest are viewing this topic.

ikar

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 21
Hosting problem - Slow database queries lot of time
« 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
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Hosting problem - Slow database queries lot of time
« Reply #1 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.
Logged

ikar

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 21
Re: Re: Hosting problem - Slow database queries lot of time
« Reply #2 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/
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Hosting problem - Slow database queries lot of time
« Reply #3 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.
Logged

ikar

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 21
Re: Re: Hosting problem - Slow database queries lot of time
« Reply #4 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?
Logged

ikar

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 21
Re: Hosting problem - Slow database queries lot of time
« Reply #5 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 :(
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Hosting problem - Slow database queries lot of time
« Reply #6 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.
Logged

ikar

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 21
Re: Re: Hosting problem - Slow database queries lot of time
« Reply #7 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?
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Hosting problem - Slow database queries lot of time
« Reply #8 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).
Logged

ikar

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 21
Re: Re: Hosting problem - Slow database queries lot of time
« Reply #9 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.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Hosting problem - Slow database queries lot of time
« Reply #10 on: July 06, 2016, 04:59:42 pm »

So do you want to disable meta albums or not?
Logged

ikar

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 21
Re: Re: Hosting problem - Slow database queries lot of time
« Reply #11 on: July 06, 2016, 07:57:26 pm »

So do you want to disable meta albums or not?

Lets try
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Hosting problem - Slow database queries lot of time
« Reply #12 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.
Logged

ikar

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 21
Re: Re: Hosting problem - Slow database queries lot of time
« Reply #13 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?
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Hosting problem - Slow database queries lot of time
« Reply #14 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.
Logged

ikar

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 21
Re: Re: Hosting problem - Slow database queries lot of time
« Reply #15 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.
Logged

ikar

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 21
Re: Hosting problem - Slow database queries lot of time
« Reply #16 on: July 09, 2016, 08:28:36 am »

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

ikar

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 21
Re: Hosting problem - Slow database queries lot of time
« Reply #17 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.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Hosting problem - Slow database queries lot of time
« Reply #18 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.
Logged

ikar

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 21
Re: Re: Hosting problem - Slow database queries lot of time
« Reply #19 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?
Logged
Pages: [1] 2   Go Up
 

Page created in 0.029 seconds with 20 queries.