100% CPU - Account suspended for high MySQL usage

Hi All,

I am running a clean install of OC v1.5.1.3 (http://www.bric-crete.com.au) and today I had my hosting service as Arvixe.com suspended and received the following email message from Arvixe sysadmin:

"The account has been suspended for high MySQL usage by database 'thrashin_ocarBC' "

Seems that the CPU spiked and stayed elevated at 100% for possibly up to two hours on their shared CROC server. The admin killed some offending queries and sent them to me:

root@croc [~]# mysqladmin proc | grep thrashin_ocarBC
| 113 | thrashin_ocarBC | localhost | thrashin_ocarBC | Query | 3 | Opening tables | SELECT * FROM oc_category c LEFT JOIN

oc_category_description cd ON (c.category_id = cd.category_id) |
| 189 | thrashin_ocarBC | localhost | thrashin_ocarBC | Query | 3 | Opening tables | SELECT * FROM oc_category c LEFT JOIN

oc_category_description cd ON (c.category_id = cd.category_id) |
| 267 | thrashin_ocarBC | localhost | thrashin_ocarBC | Query | 3 | Opening tables | SELECT * FROM oc_url_alias WHERE `query` =

'category_id=87' |
| 294 | thrashin_ocarBC | localhost | thrashin_ocarBC | Query | 0 | Opening tables | SELECT * FROM oc_url_alias WHERE `query` =

'category_id=87' |
| 306 | thrashin_ocarBC | localhost | thrashin_ocarBC | Query | 3 | closing tables | SELECT * FROM oc_category c LEFT JOIN

oc_category_description cd ON (c.category_id = cd.category_id) |
| 361 | thrashin_ocarBC | localhost | thrashin_ocarBC | Query | 0 | Opening tables | SELECT * FROM oc_url_alias WHERE `query` =

'category_id=87' |
| 477 | thrashin_ocarBC | localhost | thrashin_ocarBC | Query | 3 | Opening tables | SELECT * FROM oc_category c LEFT JOIN

oc_category_description cd ON (c.category_id = cd.category_id) |
| 549 | thrashin_ocarBC | localhost | thrashin_ocarBC | Query | 3 | Opening tables | SELECT * FROM oc_category c LEFT JOIN

oc_category_description cd ON (c.category_id = cd.category_id) |
|


I'd really appreciate it if anyone could help me understand what process may have kicked off the above, if any of it looks out of the ordinary and what might be the cause of the high CPU levels? Neither my client nor I were doing any work on the site at the time.

Don't know if this is useful info, but we have approximately 80 categories - I don't know if that is excessive?

Hope you can help me out, as I want to avoid another suspension, as it takes out a number of other domains I have hosted on the same account.

Cheers.

have you optimized your mysql database ? add index ?

I have optimised the DB just tonight after advice from Arvixe Support. I have not created any indexes - is this necessary, given this is pretty much a standard OC install and only dealing with a few hundred products?

Also, I was advised to "set session timeout for database so that whenever user logout the database connection close automatically". Any ideas on how I do this and what they should be set to?

Any help would be greatly appreciated.

Cheers.

How many categories do you have?

I have the same problem with OC 1.5.1.3 (mysqld goes to 100% CPU usage)
Have you already solved?
I will try to install a new version and let you know whats going on, but if you know how to solve it please post it.

Thanks

Hello -

It appears that I am having the same issue. I am using a dynamic categories drop down module that utilizes the same queries as the built-in functions of OpenCart. The categories are cached after a single query, so it seems unlikely that they would cause a strain on the database. I have approximately 28000 categories, but the module performance is fantastic and the data is returned very fast.

I have optimized the oc_category and oc_category_description tables (v1.5.5.1). There is a unique index on oc_category.category_id and a unique index on the combination of category_id and language_id on oc_category_description table. Based on the queries, provided by my hosting provider, below should I put a stand-alone index on oc_category_description.category_id also?

I have search through the catalog files for the sql statements below and have been unable to find them. I can find the select * from oc_category c LEFT ... but with a different where condition. I received these sql statements from my hosting provider. Apparently they were really putting a strain on the server. So much so that they were forced to suspend my site. I am stumped...which in reality is really easy to do...so I'm not all surprised. If anyone can point me in the right direction on how to resolve this I would be very grateful. Thank you in advance for your help.

John

8919440 | database | user | database |
Query | 2 | removing tmp table | SELECT * FROM oc_category c LEFT
JOIN oc_category_description cd ON (c.category_id = cd.category_id) |

| 8919725| database | user | database |
Query | 2 | removing tmp table | SELECT * FROM oc_category c LEFT
JOIN oc_category_description cd ON (c.category_id = cd.category_id) |

| 8920417 | database | user | database |
Query | 1 | removing tmp table | SELECT * FROM oc_category c LEFT
JOIN oc_category_description cd ON (c.category_id = cd.category_id) |

murp9971 wrote:I have approximately 28000 categories, but the module performance is fantastic and the data is returned very fast.

The module only gets the parent level. But what about the menu bar at the top? That builds the category tree. Recursive category mapping is what causes this issue... and trying to recursively build 28000 categories could definitely cause this.

Try unchecking the "top" checkbox for all categories so they don't try to build the tree.

Thanks Qphoria for the quick response. I have always had all the categories unchecked except for one so the menu will display. The first category is Alabama and it is checked while the remaining categories are not. Should I also uncheck this category also? I want to keep the menu available if all possible. If I have to uncheck it maybe commenting out the <?php if ($categories) { ?> line in the header.tpl will keep the menu visible but will help prevent the resource issue from happening again.

I have the site map link hidden so it can't be clicked on, but after looking at my access logs it looks like Google bot was crawling just before the high resource usage and suspension. Could this maybe be an issue? I wouldn't think so, but I don't know what I know.

Thank you again. I really do appreciate it. PS Love your Download Auto-Insert extension exceeded my expectations...

John

If you are running a product feed, shut if off for now. If you are running any SEO extension, etc., shut it off for now. If you have any runaway sessions, kill the /tmp/* for them. Look at your system/log/ and /vqmod/logs/ for hints of why the hyperactivity occurs. You may well have several completely and ridiculously oversize log files. The largest log file that I have encountered, which arose over this sort of behavior, was TWO GIGAbytes and still growing over ongoing runaway activity (it was summarily expunged, it couldn't even be read). Look if need be at your system traffic and ftp logs for signatures of visitors. Some robots overdo it, notably a certain Russian one trying to outdo Google (actually, both of them are nuts, and non-Boolean to boot). The second-last thing you want happened, host shut it down. The last thing you want is that certain letter from Google to effect that you're out of the database because your website has evidently according to its algorithms been compromised or hacked. Look at your files to see if anything unusual is there. Ensure that your permissions have NOT been reset to 777, they should be 755 dirs, 644 files. See PM.

My host has already suspended my site, but they are allowing me to 'figure out' what is going on and correct/update/change what is necessary so that it will not happen again in the future. They have been very fair about it.

I have two product feeds enabled. Google Sitemap and CSV Product Import.
I have no SEO Extensions running.
System/log/ and /vqmod/logs/ are small. ftp logs only show my limited activity and other logs are also small 500KB max
Reviewed permissions and they have not been reset to 777 dirs and file permissions look good.

Honestly I don't have much activity on my site other than Google bot as I haven't been around long enough for anyone to really notice.

Thank you for your help.

John

This one: CSV Product Import. Plus the dropdown dynamic categories (somewhere above). Both: Off. Run OC for 24 hours, see if there is a significant drop. If drop, then continue. If no drop, then stop OC and look at other things to shut off before retesting.

Read the logs not for scattergunned diverse errors, but for batteries of repeat errors, onesies, deuces, triplets, even ten or so per iteration. Look at those for patterns that specify what threw the errors. If no errors thrown, then one of those turned Off, nominally running smoothly, cannot justify its expense in potential shutdown.

I was able to narrow down the issue to the Google site map url extension. Thanks Butte (Extensions->Product Feeds->Google Site Map). I disable it and created my own xml file, place it in the root directory, and entered the path into the Google Webmaster Tools instead of the url. It appears that the url dynamically builds the sitemap file using the same functions that builds the category tree Qphoria mentions. Recursive category mapping was causing the issue. After making this change, CPU usage dropped significantly and has remained constant since.