MySQL high CPU usage

Question

Recently my server CPU has been going very high   CPU load averages 13 91  1 min  11 72  5 mins  8 01  15 mins  and my site has only had a slight increase in traffic   After running a top command  I saw MySQL was using 160  CPU   Recently I ve been optimizing tables and I ve switched to persistent connections   Could this be causing MySQL to use high amounts of CPU

User · Answer

If this server is visible to the outside world  It s worth checking if it s having lots of requests to connect from the outside world  i e  people trying to break into it

User · Answer

First I d say you probably want to turn off persistent connections as they almost always do more harm than good   Secondly I d say you want to double check your MySQL users  just to make sure it s not possible for anyone to be connecting from a remote server  This is also a major security thing to check   Thirdly I d say you want to turn on the MySQL Slow Query Log to keep an eye on any queries that are taking a long time  and use that to make sure you don t have any queries locking up key tables for too long   Some other things you can check would be to run the following query while the CPU load is high   SHOW PROCESSLIST    This will show you any queries that are currently running or in the queue to run  what the query is and what it s doing  this command will truncate the query if it s too long  you can use SHOW FULL PROCESSLIST to see the full query text    You ll also want to keep an eye on things like your buffer sizes  table cache  query cache and innodb buffer pool size  if you re using innodb tables  as all of these memory allocations can have an affect on query performance which can cause MySQL to eat up CPU   You ll also probably want to give the following a read over as they contain some good information    How MySQL Uses Memory MySQL System Variables   It s also a very good idea to use a profiler  Something you can turn on when you want that will show you what queries your application is running  if there s duplicate queries  how long they re taking  etc  etc  An example of something like this is one I ve been working on called PHP Profiler but there are many out there  If you re using a piece of software like Drupal  Joomla or Wordpress you ll want to ask around within the community as there s probably modules available for them that allow you to get this information without needing to manually integrate anything

User · Answer

As this is the top post if you google for MySQL high CPU usage or load  I ll add an additional answer   On the 1st of July 2012  a leap second was added to the current UTC-time to compensate for the slowing rotation of the earth due to the tides  When running ntp  or ntpd  this second was added to your computer s server s clock  MySQLd does not seem to like this extra second on some OS es  and yields a high CPU load  The quick fix is  as root       etc init d ntpd stop   date -s   date      etc init d ntpd start

[mysql] MySQL high CPU usage

Examples related to mysql

Examples related to cpu-usage