Slow Mysql queries causing cpu throttling.

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

Code Select Expand


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

See http://documentation.coppermine-gallery.net/en/performance.htm

Those queries would probably run faster if you stopped using album keywords.

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

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

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?

[aftab1003]

Thank you Thank you .....
First i love the Support and then CPG software.

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.

[aftab1003]

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

[Αndré]

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.

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