Database disabled, SQL queries loading the server CPU

[cedarcreek]

Hello,

 

our host disabled a client's store this morning, stating it was taking up to 90% of server CPU with SQL queries. They sent this as an example:

 

# Time: 110824 19:30:35

# User@Host: leftrc_w[leftrc_w] @ [xxx.xx.x.xx]

# Query_time: 75 Lock_time: 0 Rows_sent: 12 Rows_examined: 76392

select p.products_id, p.products_image from orders_products opa,

orders_products opb, orders o, products p where opa.products_id = '1284'

and opa.orders_id = opb.orders_id and opb.products_id != '1284' and

opb.products_id = p.products_id and opb.orders_id = o.orders_id and

p.products_status = '1' group by p.products_id order by o.date_purchased

desc limit 12;

 

 

Can anyone tell me what's going on? I've looked through other topics like this, but my numbers don't look all that bad, save for the query_time.

 

Site is down. Please help if you can!

 

Thanks!

[Guest]

Bonner,

 

 

My first thought was...change hosts to an unlimited bandwidth supplier. However if that is not an option then I would look at your site activity, more than likely unless you are making tons of money on that website, the spam bots or hackers have access to your site.

 

 

 

 

Chris

[cedarcreek]

Well, I don't think a lot of money is being made (I'm not the site owner, just the web developer...and this site is completely new to me!), but there are a lot of products.

 

I just ran an EXPLAIN query in phpMyAdmin, and here is the result:

 

#1054 - Unknown column 'pa.products_id' in 'where clause'

[cedarcreek]

Here's some more info from logs:

 

# Time: 110824 19:30:35

# User@Host: leftrc_w[leftrc_w] @ [xxx.xx.x.xx]

# Query_time: 75 Lock_time: 0 Rows_sent: 12 Rows_examined: 76392

select p.products_id, p.products_image from orders_products opa,

orders_products opb, orders o, products p where opa.products_id = '1284'

and opa.orders_id = opb.orders_id and opb.products_id != '1284' and

opb.products_id = p.products_id and opb.orders_id = o.orders_id and

p.products_status = '1' group by p.products_id order by o.date_purchased

desc limit 12;

 

# Time: 110824 19:30:37

# User@Host: leftrc_w[leftrc_w] @ [xxx.xx.x.xx]

# Query_time: 55 Lock_time: 0 Rows_sent: 12 Rows_examined: 76667

select p.products_id, p.products_image from orders_products opa,

orders_products opb, orders o, products p where opa.products_id = '301'

and opa.orders_id = opb.orders_id and opb.products_id != '301' and

opb.products_id = p.products_id and opb.orders_id = o.orders_id and

p.products_status = '1' group by p.products_id order by o.date_purchased

desc limit 12;

 

 

# Time: 110824 17:40:47

# User@Host: leftrc_w[leftrc_w] @ [xxx.xx.x.xx]

# Query_time: 53 Lock_time: 0 Rows_sent: 12 Rows_examined: 78475

select p.products_id, p.products_image from orders_products opa,

orders_products opb, orders o, products p where opa.products_id = '230'

and opa.orders_id = opb.orders_id and opb.products_id != '230' and

opb.products_id = p.products_id and opb.orders_id = o.orders_id and

p.products_status = '1' group by p.products_id order by o.date_purchased

desc limit 12;

 

# Time: 110824 17:41:56

# User@Host: leftrc_w[leftrc_w] @ [xxx.xx.x.xx]

# Query_time: 18 Lock_time: 0 Rows_sent: 12 Rows_examined: 75661

select p.products_id, p.products_image from orders_products opa,

orders_products opb, orders o, products p where opa.products_id = '461'

and opa.orders_id = opb.orders_id and opb.products_id != '461' and

opb.products_id = p.products_id and opb.orders_id = o.orders_id and

p.products_status = '1' group by p.products_id order by o.date_purchased

desc limit 12;

 

# Time: 110824 17:42:17

# User@Host: leftrc_w[leftrc_w] @ [xxx.xx.x.xx]

# Query_time: 41 Lock_time: 0 Rows_sent: 12 Rows_examined: 77172

select p.products_id, p.products_image from orders_products opa,

orders_products opb, orders o, products p where opa.products_id = '257'

and opa.orders_id = opb.orders_id and opb.products_id != '257' and

opb.products_id = p.products_id and opb.orders_id = o.orders_id and

p.products_status = '1' group by p.products_id order by o.date_purchased

desc limit 12;

 

 

 

 

--- and some info from the web host support team below ---

 

 

There are 222 similar entries. I checked the web logs, and it is

possible your site was under attack, or being crawled at the time. Here

are the top 10 IP hits to your site below:

 

Count IP

917 67.195.112.96

627 65.52.110.201

373 98.213.223.85

344 46.105.131.45

303 66.249.71.120

239 208.126.133.80

215 184.1.216.11

201 77.88.28.247

172 98.222.151.215

132 87.69.89.213

 

The top 2 entries are Yahoo and MSN crawlers, respectively.

[cedarcreek]

In case it helps, here's the web site: lefthander-rc.com

 

Looks like there are just over 2000 products, so not that many, really.

[♥toyicebear]

This might be of help to you: A Store Speed Optimization in Progress

[Guest]

These are the only NON crawler IP's:

 

87.69.89.213 - Usa

98.213.223.85 - Israel

46.105.131.45 - France

 

 

None of the above IP's are from known hackers.

 

 

As previously stated, I would suggest moving your hosting account to an unlimited bandwidth company. There are some as low as 4 bucks a month.

 

 

 

Chris

[Guest]

Bonner,

 

 

There are questionable files in your /images directory and it is obvious you are using a credit card module that collects information directly on your website for offline processing. This is VERY appealing to hackers and against the law in most of North America.

 

 

You site needs to be secured ! If you are not hacked already, you soon will be.

 

 

 

 

Chris

[♥kymation]

Older versions of osCommerce did not have all of the necessary indices on the database, which caused some queries to be slow for larger stores. If this is a 2.2 series store, get a copy of osC 2.2RC2a from Github and check the database specification. Add any index that your store does not already have.

 

The queries that you posted look like the Also Purchased box. That can be slow if the site has a lot of old orders stored. Try to get rid of some old orders.

 

Turn on caching if it is not already on. That will remove the three slowest queries (for most sites anyway.) The cache is a pain to deal with, so only leave it on if it helps.

 

Regards

Jim

[satish]

Do data base indexing.

Also if its a single language store there remove language ID condition.

 

use slow query log to mak eout which sql consuming time and optimize.

 

Mysql query optimization is a big task in itself.

 

Satish

[cedarcreek]

Bonner,

 

 

There are questionable files in your /images directory and it is obvious you are using a credit card module that collects information directly on your website for offline processing. This is VERY appealing to hackers and against the law in most of North America.

 

 

You site needs to be secured ! If you are not hacked already, you soon will be.

 

 

 

 

Chris

 

Thanks everyone for the help, I appreciate it. Seems like it's just that one query that is appearing in the slow_log...and it has to do with the Also_Purchased add-on.

 

DunWeb, can you elaborate on the questionable files? I took a quick look but didn't see anything out of the ordinary, save for a few non-image file types.

 

I haven't had any contact with this site before, so this is all new to me. The checkout insecurity bothers me, obviously. The site uses SSL for checkout, but you say that isn't sufficient? Is there anything can be done on this old software to secure it, or are we at the point where we need to rebuild the store with new software?

 

Thanks!

[germ]

Other than being able to browse the images folder I personally see no "insecurities".

 

No hack files seem to be present.

 

And if the images folder permissions folder is no higher than 755 then being able to browse it really isn't "insecure".

 

It's not a good idea, but not a security risk (IMHO).