up vote 1 down vote favorite
share [g+] share [fb] 
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
19857 mysql     20   0  514m  47m 3404 S  386  9.7   4511:45 mysqld
15659 www-data  20   0  205m  11m 4856 S    7  2.3   0:00.67 apache2
15701 www-data  20   0  205m  11m 4424 S    5  2.2   0:00.19 apache2
15700 www-data  20   0     0    0    0 Z    1  0.0   0:00.02 apache2 <defunct>

These are my results from top on Ubuntu. It was curious as to why my site keeps going down today. Could this be it?

Why is MySQL using 386% CPU? Is this normal? What can I do to fix it?

link|improve this question
100% accept rate
What DB engines are you using? What steps have you taken to configure MySQL for your server, or are you using a default install? – John Gardeniers Oct 10 '11 at 23:57
@JohnGardeniers, it is a default uninstall. Most (or all) of the tables are InnoDB – Emile Oct 11 '11 at 0:53
1  
Have a look at some of the MySQL tuneup scripts that are around. I don't recall which one I used but it helped me fine tune some parameters that made a really big difference to the resources MySQL uses. – John Gardeniers Oct 11 '11 at 0:56
Thanks for the tip John! – Emile Oct 11 '11 at 17:03
feedback

1 Answer

up vote 4 down vote accepted

...where to start.

It is normal if your website is truly busy and you can justify the usage. Chances are however, you've probably got some non-optimial queries and poor table structures which are causing most of your pain.

I would probably start looking at mysqladmin processlist to see what queries are running the longest and start optimising from there.

You could also enable slow query logging in MySQL to get a longer-term idea of what is going on and which queries are causing you the pain.

To fix this comprehensively you really need to be putting on your DBA hat and going from there. If you don't have the knowledge or skills in this area, it maybe easier to get a DBA on a contract basis to look at this and provide recommendations.

Unfortunately comprehensively solving this requires both time, knowledge and experience of your existing dataset.

link|improve this answer
well worded and great answer. i'll start with mysqladmin then hire a DBA – Emile Oct 10 '11 at 22:33
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.