Great amount of Dbqueries ... freezing the MySQL Server Great amount of Dbqueries ... freezing the MySQL Server
 

News:

CPG Release 1.6.26
Correct PHP8.2 issues with user and language managers.
Additional fixes for PHP 8.2
Correct PHP8 error with SMF 2.0 bridge.
Correct IPTC supplimental category parsing.
Download and info HERE

Main Menu

Great amount of Dbqueries ... freezing the MySQL Server

Started by Corwin, July 30, 2007, 02:40:17 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Print
Go Down Pages1
User actions

Corwin

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...

Nibbler

#1
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.

Corwin

#2
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 !

Corwin

#3
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 !

Joachim Müller

#4
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.

Corwin

#5
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


Corwin

#6
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)

Stramm

#7
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

#8
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
Print
Go Up Pages1
User actions