Overload of MySQL Queries

[♥monte22]

Over the last few days (actually started over the weekend) I've seen an increase in MySQL queries on my site. My host sent me an email saying I was averaging 145 queries a second, which is quite high. This site gets only low to moderate traffic, so I am certain the queries are not 'natural'. Cache option in OSC is turned on, and seems to be working.

 

Below is a sample taken from the sql log file. I'm no sql guru, so I'm not sure how to interpret what it means. As you can imagine at 145 queries a second, the actual log file is quite long.

 

Anyone have any ideas?

 

FYI, site is http://www.gofastperformance.com/

 

2 Query select count(*) as count from categories where parent_id = '1'

2 Query select count(*) as count from categories where parent_id = '1'

2 Query select count(*) as count from categories where parent_id = '6'

2 Query select count(*) as count from categories where parent_id = '6'

2 Query select count(*) as count from categories where parent_id = '9'

2 Query select count(*) as count from categories where parent_id = '9'

2 Query select count(*) as count from categories where parent_id = '632'

2 Query select count(*) as count from categories where parent_id = '632'

2 Query select count(*) as count from categories where parent_id = '276'

2 Query select count(*) as count from categories where parent_id = '276'

2 Query select count(*) as count from categories where parent_id = '12'

2 Query select count(*) as count from categories where parent_id = '12'

2 Query select count(*) as count from categories where parent_id = '17'

2 Query select count(*) as count from categories where parent_id = '17'

2 Query select count(*) as count from categories where parent_id = '154'

2 Query select count(*) as count from categories where parent_id = '154'

2 Query select count(*) as count from categories where parent_id = '318'

2 Query select count(*) as count from categories where parent_id = '318'

2 Query select count(*) as count from categories where parent_id = '20'

2 Query select count(*) as count from categories where parent_id = '20'

2 Query select count(*) as count from categories where parent_id = '327'

2 Query select count(*) as count from categories where parent_id = '327'

2 Query select count(*) as count from categories where parent_id = '35'

2 Query select count(*) as count from categories where parent_id = '35'

2 Query select count(*) as count from categories where parent_id = '174'

2 Query select count(*) as count from categories where parent_id = '174'

2 Query select count(*) as count from categories where parent_id = '170'

2 Query select count(*) as count from categories where parent_id = '170'

2 Query select count(*) as count from categories where parent_id = '36'

2 Query select count(*) as count from categories where parent_id = '36'

2 Query select count(*) as count from categories where parent_id = '38'

2 Query select count(*) as count from categories where parent_id = '38'

2 Query select count(*) as count from categories where parent_id = '44'

2 Query select count(*) as count from categories where parent_id = '44'

2 Query select count(*) as count from categories where parent_id = '332'

2 Query select count(*) as count from categories where parent_id = '332'

2 Query select count(*) as count from categories where parent_id = '176'

2 Query select count(*) as count from categories where parent_id = '176'

2 Query select count(*) as count from categories where parent_id = '40'

2 Query select count(*) as count from categories where parent_id = '40'

2 Query select count(*) as count from categories where parent_id = '42'

2 Query select count(*) as count from categories where parent_id = '42'

2 Query select count(*) as count from categories where parent_id = '180'

2 Query select count(*) as count from categories where parent_id = '180'

2 Query select count(*) as count from categories where parent_id = '758'

2 Query select count(*) as count from categories where parent_id = '758'

2 Query select count(*) as count from categories where parent_id = '183'

2 Query select count(*) as count from categories where parent_id = '183'

2 Query select count(*) as count from categories where parent_id = '185'

2 Query select count(*) as count from categories where parent_id = '185'

[♥FWR Media]

Over the last few days (actually started over the weekend) I've seen an increase in MySQL queries on my site. My host sent me an email saying I was averaging 145 queries a second, which is quite high. This site gets only low to moderate traffic, so I am certain the queries are not 'natural'. Cache option in OSC is turned on, and seems to be working.

 

Below is a sample taken from the sql log file. I'm no sql guru, so I'm not sure how to interpret what it means. As you can imagine at 145 queries a second, the actual log file is quite long.

 

Anyone have any ideas?

 

FYI, site is http://www.gofastperformance.com/

 

2 Query select count(*) as count from categories where parent_id = '1'

2 Query select count(*) as count from categories where parent_id = '1'

2 Query select count(*) as count from categories where parent_id = '6'

2 Query select count(*) as count from categories where parent_id = '6'

2 Query select count(*) as count from categories where parent_id = '9'

2 Query select count(*) as count from categories where parent_id = '9'

2 Query select count(*) as count from categories where parent_id = '632'

2 Query select count(*) as count from categories where parent_id = '632'

2 Query select count(*) as count from categories where parent_id = '276'

2 Query select count(*) as count from categories where parent_id = '276'

2 Query select count(*) as count from categories where parent_id = '12'

2 Query select count(*) as count from categories where parent_id = '12'

2 Query select count(*) as count from categories where parent_id = '17'

2 Query select count(*) as count from categories where parent_id = '17'

2 Query select count(*) as count from categories where parent_id = '154'

2 Query select count(*) as count from categories where parent_id = '154'

2 Query select count(*) as count from categories where parent_id = '318'

2 Query select count(*) as count from categories where parent_id = '318'

2 Query select count(*) as count from categories where parent_id = '20'

2 Query select count(*) as count from categories where parent_id = '20'

2 Query select count(*) as count from categories where parent_id = '327'

2 Query select count(*) as count from categories where parent_id = '327'

2 Query select count(*) as count from categories where parent_id = '35'

2 Query select count(*) as count from categories where parent_id = '35'

2 Query select count(*) as count from categories where parent_id = '174'

2 Query select count(*) as count from categories where parent_id = '174'

2 Query select count(*) as count from categories where parent_id = '170'

2 Query select count(*) as count from categories where parent_id = '170'

2 Query select count(*) as count from categories where parent_id = '36'

2 Query select count(*) as count from categories where parent_id = '36'

2 Query select count(*) as count from categories where parent_id = '38'

2 Query select count(*) as count from categories where parent_id = '38'

2 Query select count(*) as count from categories where parent_id = '44'

2 Query select count(*) as count from categories where parent_id = '44'

2 Query select count(*) as count from categories where parent_id = '332'

2 Query select count(*) as count from categories where parent_id = '332'

2 Query select count(*) as count from categories where parent_id = '176'

2 Query select count(*) as count from categories where parent_id = '176'

2 Query select count(*) as count from categories where parent_id = '40'

2 Query select count(*) as count from categories where parent_id = '40'

2 Query select count(*) as count from categories where parent_id = '42'

2 Query select count(*) as count from categories where parent_id = '42'

2 Query select count(*) as count from categories where parent_id = '180'

2 Query select count(*) as count from categories where parent_id = '180'

2 Query select count(*) as count from categories where parent_id = '758'

2 Query select count(*) as count from categories where parent_id = '758'

2 Query select count(*) as count from categories where parent_id = '183'

2 Query select count(*) as count from categories where parent_id = '183'

2 Query select count(*) as count from categories where parent_id = '185'

2 Query select count(*) as count from categories where parent_id = '185'

 

All your solutions are here ..

 

http://www.oscommerce.com/forums/index.php?showtopic=119077

 

It's a long read but very worthwhile.

[♥monte22]

Ok, I installed the parse time/query contrib to figure out the cause.

 

Here are the results:

 

Current Parse Time: 0.404 s with 1327 queries

 

It seems its querying all of the categories each time the page loads. Is there a way to optimize this? I would think the cache feature would do this, but apparently not.

[♥geoffreywalton]

All your solutions are here ..

[♥FWR Media]

Ok, I installed the parse time/query contrib to figure out the cause.

 

Here are the results:

 

Current Parse Time: 0.404 s with 1327 queries

 

It seems its querying all of the categories each time the page loads. Is there a way to optimize this? I would think the cache feature would do this, but apparently not.

 

To give you an idea .. my version of oscommerce loads with 8 - 15 fast queries and it doesn't use page cache (which I recommend NOT using). Most of what I did is in that thread.

[♥FWR Media]

And btw by VPS host

 

do you mean you are hosted on a VPS or you have a VPS to yourself.

 

Reason I ask is that with the right supplier VPS can massively outperform dedicated until you reach the megabuck league.

 

Megabuck meaning £300 month plus.

[♥monte22]

And btw by VPS host

 

do you mean you are hosted on a VPS or you have a VPS to yourself.

 

Reason I ask is that with the right supplier VPS can massively outperform dedicated until you reach the megabuck league.

 

Megabuck meaning £300 month plus.

 

I have my own VPS which is currently hosting a handful of sites.

 

I will look a little deeper into that thread.

 

Thanks

[♥monte22]

I went ahead and installed the Optimize categories box v1.2 and I don't see any change in the query number. I went back through the install twice to make sure I had everything correctly.

 

Is there something I am missing?

[Jan Zonjee]

Is there something I am missing?

You probably don't use the standard osC categories box but use some contribution instead?

[♥monte22]

You probably don't use the standard osC categories box but use some contribution instead?

 

You're right I don't, and I thought that might be causing a problem.

 

I went ahead and took the stock categories box file (categories.php), and uploaded it, but unfortunately still no change.

[♥FWR Media]

You're right I don't, and I thought that might be causing a problem.

 

I went ahead and took the stock categories box file (categories.php), and uploaded it, but unfortunately still no change.

 

 

Read the post and do your best to correctly add the recomended tweeks.

 

It's pointless reporting back here after every try .. the work has already been done for you.

[♥monte22]

I was just looking for suggestions on a conflict that may be causing the issue. It apparently was related to the STS template system that was installed.

 

After the categories box optimizer and removing a few lines from sts_user_code.php the queries have now drop down to a reasonable 26 on the home page.

 

I appreciate the help.

[Jan Zonjee]

After the categories box optimizer and removing a few lines from sts_user_code.php the queries have now drop down to a reasonable 26 on the home page.

Wow, from 1327->26. That is great! Glad to hear it worked for you, eventually :)

[♥FWR Media]

Fantastic!! well done!