MySQL 5.5 extremely high loads

[webmentors]

Hi,

After migrating one account with a MySQL database, (MySQL 4 to 5.5), As soon as the site gets a hits, CPU loads get very (120 or more) causing the machine to completely lock up after a couple of mins. I've checked the table structure and it could use some optimization.

I had to suspend this account for now as I am not able to run any sort of analytics long enough. Eventually CPU usage goes really high.

Information on the server:
4 GB RAM, 8 cores total (Dual Xeon)
RedHat 6. 64 Bit system
WHM 11.32.5 Release.
top usage is normally 0.30%
its a dedicated server, not a VPS.

What do I get MySQL to play nice and not take down the whole server with it cant handle the queries from this application?

More info at normal usage (when the problem database is not activated by the problem site).
top - 11:05:42 up 11 days, 8:16, 4 users, load average: 0.29, 0.23, 0.19
Tasks: 349 total, 1 running, 348 sleeping, 0 stopped, 0 zombie
Cpu(s): 1.3%us, 0.6%sy, 0.0%ni, 98.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 3911780k total, 3776672k used, 135108k free, 303740k buffers
Swap: 2104504k total, 169440k used, 1935064k free, 2605996k cached

 

[Infopro]

You don't mention anything about the site. What software is the site using, some sort of CMS with lots of addons possibly? That's where I'd start looking before questioning server configuration.

 

[webmentors]

Thanks for the suggestion.

I took a look at the code of the site, its a simple ecommerce website. It doesnt use a CMS or have an addon. Just straight php/MySQL. I took one quick look at the templating engine, and in the SQL code, the table structures and queries look like they could be seriously improved. For example on every page hit, there is a LIKE query on a table thats has 6000+ rows, on a field that it isn't indexed. This query is then used to create dynamic functions.

It it comes down to it, we can probably patch up the some of the code to optimize the queries.

 

[webmentors]

OK it looked like a MySQL issue but it wasn't. actually it was suPHP that was causing an excessive load. After using Apache Configuration setting in WHM, the load is down to an average 1.28.