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: Slow Mysql queries causing cpu throttling.  (Read 5844 times)

0 Members and 1 Guest are viewing this topic.

aftab1003

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 67
    • PictureRating Site for Teens
Slow Mysql queries causing cpu throttling.
« 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...
Code: [Select]
# 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?
Logged

Nibbler

  • Guest
Re: Slow Mysql queries causing cpu throttling.
« Reply #1 on: March 17, 2011, 06:55:21 pm »

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

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

aftab1003

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 67
    • PictureRating Site for Teens
Re: Slow Mysql queries causing cpu throttling.
« Reply #2 on: March 22, 2011, 09:43:09 pm »

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

Joe Carver

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 1513
  • aka 'i-imagine'
    • Home Page
Re: Slow Mysql queries causing cpu throttling.
« Reply #3 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.....

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15765
Re: Slow Mysql queries causing cpu throttling.
« Reply #4 on: March 23, 2011, 09:55:40 am »

turning down / off some of what you have set for logging and stats.....
Regarding the query
Quote
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?
Logged

aftab1003

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 67
    • PictureRating Site for Teens
Re: Slow Mysql queries causing cpu throttling.
« Reply #5 on: March 24, 2011, 08:33:36 pm »

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

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

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

aftab1003

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 67
    • PictureRating Site for Teens
Re: Slow Mysql queries causing cpu throttling.
« Reply #6 on: March 29, 2011, 03:09:24 pm »

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

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15765
Re: Slow Mysql queries causing cpu throttling.
« Reply #7 on: March 31, 2011, 02:58:16 pm »

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
Quote
0.0005 sec
or
Quote
0.0003 sec
query times.
Logged

Photospace111

  • Coppermine newbie
  • Offline Offline
  • Posts: 2
Re: Slow Mysql queries causing cpu throttling.
« Reply #8 on: August 04, 2011, 09:26:40 am »

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
Logged

Joe Carver

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 1513
  • aka 'i-imagine'
    • Home Page
Re: Slow Mysql queries causing cpu throttling.
« Reply #9 on: August 04, 2011, 11:21:58 pm »

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.
Pages: [1]   Go Up
 

Page created in 0.681 seconds with 26 queries.