MySQL take up to 300% cpu use when site is visited by more than X people

Question

I'm puzzled; I assume a slow query.

Note: all my queries are tested and run great when there`s less people using my app/website (less then 0.01sec each).

So I've some high cpu usage with my current setup and I was wondering why? Is it possible it's an index issue?

Our possible solution: we thought we could use an XML cache file to store the informations each hour, and then reduce the load on our MySQL query? (update files each hour).

Will it be good for us to do such things? Since we have an SSD drive? Or will it be slower then before?

Currently in high traffic time, our website/app can take up to 30 seconds before return the first byte. My website is running under a Plesk 12 server.

UPDATE Here's more informations about my mysql setup.. http://pastebin.com/KqvFYy8y

Answer 1

Is it possible it's an index issue?

Perhaps but not necessarily. You need first to identify which query is slow. You find that in the slow query log. Then analyze the query. This is explained in literature or you can contact a consultant / tutor for that.

We thought we could use an xml cache file to store the informations each hour.. and then reduce the load on our mysql query?

Well, cache invalidation is not the easiest thing to do, but with a fixed rythm every hour this seems easy enough. But take care that it will only help if the actual query you cache was slow. Mysql normally has a query cache built in, check if it is enabled or not first.

Will it be good for us to do such things?

Normally if the things to do are good, the results will be good, too. Sometimes even bad things will result in good results, so such a general question is hard to answer. Instead I suggest you gain more concrete information first before you continue to ask around. Sounds more like guessing. Stop guessing. Really, that's only for the first two minutes, after that, just stop guessing.

Since we have an ssd drive? Or will it be slower then before?

You can try to throw hardware on it. Again lierature and a consultant / tutor can help you greatly with that. But just stop guessing. Really.

Answer 2

I assume the query is not slow all the time. If this is true, the query is not very likely the problem.

You need to know what is using the CPU. Likely a runaway script with an infinite loop.

Try this:

<?php
header('Content-Type: text/plain; charset=utf-8');
echo system('ps auxww');
?>

This should return a list in this format:

USER         PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND

Scan down the %CPU column and look for your user name in the USER column

If you see a process taking 100% CPU, you may want to get the PID number and:

system('kill 1234');  

Where 1234 is the PID

The mysql processes running at 441% and 218% seems very problematic.

Assuming this is a shared server, there may be another user running queries that is hogging the CPU. you may need to take that up with your provider.

I've been watching on one of my shared servers and the CPU for the mysql process has not gone over 16%.

MySQLTuner

From the link it appears you have heavy traffic.

• The Tuner was running 23.5 minutes
• Joins performed without indexes: 69863

69863 in 23.5 min. comes out to almost 50 queries per second.

Does this sound correct? Running a query with a JOIN 150 times per second.

Index JOIN Table

You have a query with a JOIN.

The tables are joined by column(s).

On the joined table add an index to the column that joins the two table together.