forum.coppermine-gallery.net
Support => cpg1.5.x Support => cpg1.5 miscellaneous => Topic started by: aftab1003 on March 17, 2011, 05:43:16 pm
-
upgraded to latest 1.5.12 last night.
theme= hardwired
host= hostmonster
url= http://picturerating.us/picture-gallery/index.php ( 16+ content warning )
problem = slow mysql queries
queries found in slow mysql queries log...
# Thu Mar 17 02:00:09 2011
# Query_time: 1.011965 Lock_time: 0.000175 Rows_sent: 1 Rows_examined: 288
use eshoppin_cpg;
SELECT r.pid FROM cpg14x_pictures AS r
WHERE ((aid = 24 ) OR (keywords like '%bikini indian model%' ))AND approved='YES'
ORDER BY pid ASC
LIMIT 0 ,1
# Thu Mar 17 02:00:14 2011
# Query_time: 5.293457 Lock_time: 3.633390 Rows_sent: 1 Rows_examined: 290
use eshoppin_cpg;
SELECT r.pid FROM cpg14x_pictures AS r
WHERE ((aid = 24 ) OR (keywords like '%bikini indian model%' ))AND approved='YES'
ORDER BY pid ASC
LIMIT 2 ,1
# Thu Mar 17 02:01:36 2011
# Query_time: 1.506957 Lock_time: 0.097221 Rows_sent: 1 Rows_examined: 1
use eshoppin_cpg;
SELECT filename, title, pid, position FROM cpg14x_pictures WHERE pid = 300
and lot more...
these type of queries are causing lot of cpu throttling and slowing down site loading.
any one facing this type of issue or any guide pleas?
-
See http://documentation.coppermine-gallery.net/en/performance.htm
Those queries would probably run faster if you stopped using album keywords.
-
thanks Nibbler for your kind reply...
some of my albums was with keywords and i removed them.
but now with another slow queries are coming like this..
# Tue Mar 22 14:14:03 2011
# Query_time: 2.321036 Lock_time: 0.000119 Rows_sent: 0 Rows_examined: 1
use eshoppin_cpg;
UPDATE cpg14x_pictures SET hits = hits + 1, lasthit_ip = '119.73.33.71', mtime = CURRENT_TIMESTAMP WHERE pid = 290
can you pleas suggest me what thing making this slow query?
-
Go to: Config >> Logging and statistics
then try turning down / off some of what you have set for logging and stats.....
-
turning down / off some of what you have set for logging and stats.....
Regarding the query
UPDATE cpg14x_pictures SET hits = hits + 1
you should try to turn off the 'Count file views' option.
Imo your server has a very poor performance if it takes so long to update a single row. Do you get that slow query continuously or just once?
-
Thank you Thank you .....
First i love the Support and then CPG software. ;D
at this time, i am logging "ALL logging mode", logging Ecards, Count File Views and Album Views.
Regarding the query you should try to turn off the 'Count file views' option.
Imo your server has a very poor performance if it takes so long to update a single row. Do you get that slow query continuously or just once?
File Count Views is now TURNED OFF.
here is another same type Query...
# Thu Mar 24 11:27:03 2011
# Query_time: 5.994747 Lock_time: 0.000126 Rows_sent: 0 Rows_examined: 1
use eshoppin_cpg;
UPDATE cpg14x_pictures SET hits = hits + 1, lasthit_ip = '218.248.64.139', mtime = CURRENT_TIMESTAMP WHERE pid = 212
Go to: Config >> Logging and statistics
then try turning down / off some of what you have set for logging and stats.....
Logging is now turned off.
lets see whats next. and i will let you know if there is any problem after doing your suggestions.
-
# Tue Mar 29 02:11:04 2011
# Query_time: 1.726458 Lock_time: 0.710047 Rows_sent: 1 Rows_examined: 193
use eshoppin_cpg;
SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title, r.keywords, r.votes, pic_rating, hits, caption, r.owner_id FROM cpg14x_pictures AS r
WHERE ((aid = 1 ) )AND approved='YES'
ORDER BY pid DESC
LIMIT 20 ,1
# Tue Mar 29 02:11:34 2011
# Query_time: 1.175897 Lock_time: 0.000113 Rows_sent: 1 Rows_examined: 0
use eshoppin_cpg;
SELECT COUNT(*) FROM cpg14x_comments WHERE pid = 975
# Tue Mar 29 02:11:35 2011
# Query_time: 1.920581 Lock_time: 0.344094 Rows_sent: 1 Rows_examined: 1
use eshoppin_cpg;
SELECT user_id, time FROM `eshoppin_cpg`.cpg14x_sessions WHERE session_id = 'f2668afd75ca3020000346f0c82a0fdd'
# Tue Mar 29 02:11:35 2011
# Query_time: 1.305253 Lock_time: 0.000147 Rows_sent: 1 Rows_examined: 1
use eshoppin_cpg;
SELECT COUNT(*) FROM cpg14x_comments WHERE pid = 1039
# Tue Mar 29 02:11:37 2011
# Query_time: 2.720603 Lock_time: 1.005821 Rows_sent: 1 Rows_examined: 1
use eshoppin_cpg;
SELECT user_id, time FROM `eshoppin_cpg`.cpg14x_sessions WHERE session_id = 'e6a4eda9725da76638e7db03a637c3fb'
8 sec query
# Tue Mar 29 02:12:41 2011
# Query_time: 8.826362 Lock_time: 0.874902 Rows_sent: 1 Rows_examined: 67
use eshoppin_cpg;
SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title, r.keywords, r.votes, pic_rating, hits, caption, r.owner_id FROM cpg14x_pictures AS r
WHERE ((aid = 27 ) )AND approved='YES'
ORDER BY pid ASC
LIMIT 8 ,1
i have moved my sites from this server to another, but no effect. any guide pleas?
-
Imo your server has a very poor performance if it takes so long to
... execute such simple queries. That may also apply to your new server. Similar queries on my simple development machine with ~80000 rows in the table I'm querying takes only
0.0005 sec
or
0.0003 sec
query times.
-
Hi, I have a similar problem with queries slowing down all the website (22 000 photos, 96 000 comments, 969 albums). I think that problem could be in having too much keywords in database (hudreds, maybe thousands). I disabled album keywords here http://documentation.coppermine-gallery.net/en/configuration.htm#admin_allow_user_album_keyword but did not help. All the keywords are still in db and users can assign keywords when uploading files. Shall I delete the keyword list and leave there only a few keywords to use? Is there any other way how to optimize keywords or any other feature to fasten server load? Thank you.
I am running CPG in test directory without pictures here http://www.fotoradce.cz/galerie4/index.php
-
1) Please start a new thread for your own issue. (The admins can decide to split your post.)
2) Your site response was inconsistent when I tried it at different times, so therefore...
3) Turn on debug, check your server logs for errors that could have caused a slow response.
4) Post a test user account with user name and password if you have a password protected gallery.