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]   Go Down

Author Topic: Great amount of Dbqueries ... freezing the MySQL Server  (Read 5516 times)

0 Members and 1 Guest are viewing this topic.

Corwin

  • Coppermine newbie
  • Offline Offline
  • Posts: 6
Great amount of Dbqueries ... freezing the MySQL Server
« on: July 30, 2007, 02:40:17 pm »

hi everyone !

I have a huge coppermine gallery with about 24'000 ressources (pictures and sounds). My gallery is very well visited, as the amount of queries freeze the MySQL Server... and my hosting company tell me I have to suspend (not only inactivate) the Gallery... as I will be able to solve this freezing problem.

Here is what is giving to me as I enable the log :

. as logged as Admin, for the index page : Page generated in 0.534 seconds - 142 queries in 0.196 seconds
. as logged as User (everyone could see the ressources, even not logged in) : Page generated in 0.502 seconds - 139 queries in 0.177 seconds

I have deactivate the most statistics, etc...

I have made a robot.txt file to block google,... (even if I want to be in google, I have no other choice...)


# User@Host: xp_user[xp_user] @ localhost []
# Query_time: 79 Lock_time: 0 Rows_sent: 1 Rows_examined: 47438
SELECT count(*) FROM cpg133_pictures as p LEFT JOIN cpg133_albums as a
ON a.aid=p.aid WHERE 1 AND approved='YES';

See that a simple query take about 80 seconds to be launched, so imagine with 139 queries... when the gallery is public open !

So here are my questions :

a. is it possible to restrict the number of queries ?
b. does it exist a maximum amount of ressources, after which number it will freeze everything ?
c. what can I do to solve this problem and to be able to reopen my gallery asap ?

Thanks in advance...
Logged

Nibbler

  • Guest
Re: Great amount of Dbqueries ... freezing the MySQL Server
« Reply #1 on: July 30, 2007, 04:47:47 pm »

Post a link. Those numbers imply you have far too many things on your main page. The query you post is a stats query and can be disabled (search the board).

a. You can in MySQL configuration, not within Coppermine.
b. The maximum level of activity on the gallery that will cause problems depends on how you use the gallery and server config.
c. Remove features that cause the most load.
Logged

Corwin

  • Coppermine newbie
  • Offline Offline
  • Posts: 6
Re: Great amount of Dbqueries ... freezing the MySQL Server
« Reply #2 on: July 30, 2007, 11:50:40 pm »

ouki. thanks !

here is the link : http://www.ninesages.org/XP_Album_enattentersolution

I have reopenend it for you to be able to see.

Here are printscreen of my configuration :

http://www.ninesages.org/config_album/xp_config1.jpg

http://www.ninesages.org/config_album/xp_config2.jpg

http://www.ninesages.org/config_album/xp_config3.jpg

http://www.ninesages.org/config_album/xp_config4.jpg

http://www.ninesages.org/config_album/xp_config5.jpg

http://www.ninesages.org/config_album/xp_config6.jpg

I'm really totally ready to hear all what you can tell me to solve my problem !

As my webhoster tell me, it's no limit of queries number...

Really big thanks in advance !
Logged

Corwin

  • Coppermine newbie
  • Offline Offline
  • Posts: 6
Re: Great amount of Dbqueries ... freezing the MySQL Server
« Reply #3 on: July 30, 2007, 11:58:12 pm »

(sorry for double post, but I can't edit...)

just for infos : the two files yp_customheader.php and yp_customfooter.php doesn't have any DB_query... juste the rotating banner, the count of visits amount (with txt file), and the partenars.

tx !
Logged

Joachim Müller

  • Dev Team member
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 47843
  • aka "GauGau"
    • gaugau.de
Re: Great amount of Dbqueries ... freezing the MySQL Server
« Reply #4 on: July 31, 2007, 07:23:29 am »

Ideas and hints:
1) You have cpg1.4.10, while the most recent stable release is cpg1.4.12 - it's mandatory to upgrade asap.
2) Your template contains invalid HTML - it is mandatory to close open tags (like <body> and <html>) - edit http://www.ninesages.org/XP_Album_enattentersolution/themes/rmxp/template.html and make it validate
3) Reduce database load by specifying album thumbnails instead of using the "last uploaded" one, see http://forum.coppermine-gallery.net/index.php?topic=44378.0
4) Disable "albums can be private" if you can - the "NOT IN" part of the queries is a resource hog - see http://forum.coppermine-gallery.net/index.php?topic=15653.0 and http://forum.coppermine-gallery.net/index.php?topic=40749.0
5) As suggested: reduce the database load by removing the statistics. Remove the output: http://forum.coppermine-gallery.net/index.php?topic=32833.0 Remove the queries as well.
Logged

Corwin

  • Coppermine newbie
  • Offline Offline
  • Posts: 6
Re: Great amount of Dbqueries ... freezing the MySQL Server
« Reply #5 on: August 01, 2007, 12:10:46 am »

Thanks a lot for your help.

So I made this ...

1. new version installed
2. correction of the template
2b. correction of the template theme.php for removing the number of pictures and albums in each category
3. for specifying album thumbnails instead of using the "last uploaded" one, I will try to do it
4. this is already disabled
5. well, I've seen where I can remove this. But is it possible de have only the number of files, without the number of albums, comments, views, etc ?

btw, the gallerie is back at http://www.ninesages.org/XP_Album


Logged

Corwin

  • Coppermine newbie
  • Offline Offline
  • Posts: 6
Re: Great amount of Dbqueries ... freezing the MySQL Server
« Reply #6 on: August 01, 2007, 12:20:12 am »

(once again, sorry for double post...)

here are the queries taking the most time... why then, as I haven't set up the Statistics (cf pt 4) ?

[137] => SELECT COUNT(*) FROM cpg133_pictures WHERE approved = 'NO' (0.013s)

[133] => SELECT count(*) FROM cpg133_pictures as p LEFT JOIN cpg133_albums as a ON a.aid=p.aid WHERE 1 AND approved='YES' (0.024s)

[104] => SELECT a.aid, count( p.pid )  AS pic_count, max( p.pid )  AS last_pid, max( p.ctime )  AS last_upload, a.keyword FROM cpg133_albums AS a  LEFT JOIN cpg133_pictures AS p ON a.aid = p.aid AND p.approved =  'YES' WHERE a.aid IN (78, 79, 80, 81, 150)GROUP BY a.aid (0.005s)

[84] => SELECT a.aid, count( p.pid )  AS pic_count, max( p.pid )  AS last_pid, max( p.ctime )  AS last_upload, a.keyword FROM cpg133_albums AS a  LEFT JOIN cpg133_pictures AS p ON a.aid = p.aid AND p.approved =  'YES' WHERE a.aid IN (49, 50, 51, 52, 53, 141, 145)GROUP BY a.aid (0.016s)

Logged

Stramm

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Gender: Male
  • Posts: 6006
    • Bettis Wollwelt
Re: Great amount of Dbqueries ... freezing the MySQL Server
« Reply #7 on: August 01, 2007, 07:03:05 am »

That's the stats you can see on the index (x files in x albums and x categories with x comments viewed x times) [queries in function get_cat_list()]

The calculation is done for each infdividual user. If there are hidden albums for him, then this albums, files do not get coinsidered in the stats. If you don't bither and always show the total number of files, albums the query won't be such a resource hog.

find
Code: [Select]
"SELECT count(*) FROM {$CONFIG['TABLE_PICTURES']} as p " . 'LEFT JOIN ' . $CONFIG['TABLE_ALBUMS'] . ' as a ' . 'ON a.aid=p.aid ' . 'WHERE 1' . $pic_filter . ' AND approved=\'YES\'';
replace with
Code: [Select]
"SELECT count(*) FROM {$CONFIG['TABLE_PICTURES']}";
find
Code: [Select]
"SELECT count(*) FROM {$CONFIG['TABLE_COMMENTS']} as c " . 'LEFT JOIN ' . $CONFIG['TABLE_PICTURES'] . ' as p ' . 'ON c.pid=p.pid ' . 'LEFT JOIN ' . $CONFIG['TABLE_ALBUMS'] . ' as a ' . 'ON a.aid=p.aid ' . 'WHERE 1' . $pic_filter;replace with
Code: [Select]
"SELECT count(*) FROM {$CONFIG['TABLE_COMMENTS']}";
find
Code: [Select]
"SELECT sum(hits) FROM {$CONFIG['TABLE_PICTURES']} as p " . 'LEFT JOIN ' . $CONFIG['TABLE_ALBUMS'] . ' as a ' . 'ON p.aid=a.aid ' . 'WHERE 1' . $pic_filter;replace with
Code: [Select]
"SELECT sum(hits) FROM {$CONFIG['TABLE_PICTURES']}"

Corwin

  • Coppermine newbie
  • Offline Offline
  • Posts: 6
Re: Great amount of Dbqueries ... freezing the MySQL Server
« Reply #8 on: August 02, 2007, 05:36:34 am »

great thanks ! I've modified this to fit my gallerie. And I have reopenend it... hoping my host will not kill me again.

by the way, I do not have found where I can modify something to have only the number of files, albums and categories and not
"23719 ressources dans 324 albums et 41 catégories avec 144 commentaires affichées 932892 fois", just "23719 ressources dans 324 albums et 41 catégories"

tx
Logged
Pages: [1]   Go Up
 

Page created in 0.03 seconds with 20 queries.