Slow Mysql queries causing cpu throttling. Slow Mysql queries causing cpu throttling.
 

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]

Main Menu

Slow Mysql queries causing cpu throttling.

Started by aftab1003, March 17, 2011, 05:43:16 PM

Previous topic - Next topic

0 Members and 2 Guests are viewing this topic.

aftab1003

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?

Nibbler


aftab1003

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

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

Joe Carver

Go to: Config >> Logging and statistics

then try turning down / off some of what you have set for logging and stats.....

Αndré

Quote from: Joe Carver on March 22, 2011, 11:40:25 PM
turning down / off some of what you have set for logging and stats.....
Regarding the query
QuoteUPDATE 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?

aftab1003

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.

Quote from: Αndré on March 23, 2011, 09:55:40 AM
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...

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

Quote from: Joe Carver on March 22, 2011, 11:40:25 PM
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.

aftab1003

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

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

Αndré

Quote from: Αndré on March 23, 2011, 09:55:40 AM
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
Quote0.0005 sec
or
Quote0.0003 sec
query times.

Photospace111

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

Joe Carver

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.