211

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?

Juddling
  • 4,594
  • 8
  • 34
  • 40
  • 4
    Persistent connections are _almost_ always not the right thing to use. – jason Aug 15 '09 at 16:20
  • i'll take them off now and watch for a difference because i never remember the cpu being above 2 a month ago! – Juddling Aug 15 '09 at 16:33
  • 2
    Servers tend to have more than one core. Percent CPU usage is calculated relative to one core, anotherwords a process using up two cores completely will have a CPU usage of 200%. Here, MySQL is using up 100% of one core and 60% of another core. That does not mean all CPUs are used up, most likely he still has at least two free CPUs. – xaav Aug 18 '11 at 16:00
  • High CPU almost always means inefficient queries. Such are usually solved via better indexing (especially 'composite') and/or reformulating the query. – Rick James Oct 09 '18 at 20:52

3 Answers3

288

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.

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.

Steven Surowiec
  • 10,030
  • 5
  • 32
  • 37
  • 13
    thanks very much for this, i removed persistant connections and then set up the slow query log. i read the log and most of the queries came from two table and the tables hadn't been indexed properly! it's only been about 10 minutes but here's the result: CPU load averages 0.48 (1 min) 0.95 (5 mins) 2.42 (15 mins) thanks very much – Juddling Aug 15 '09 at 18:16
  • 1
    same problem, solved by indexing the tables that slow down the process, thank you Steven and Juddling – gabrielem Aug 26 '15 at 22:21
  • @Juddling Could you elaborate on how to Index a table please? Perhaps some link? I know it has been a while, but I am really new to this thing. Sorry fr the noobish question – JayVDiyk May 07 '17 at 11:06
  • Logging the slow queries helped me find my particular issue of high CPU utilization. In my case, it was a Wordpress plugin (ultimate-tag-cloud-widget) which was doing a monstrous query with every hit just to show the popular tags. It's a great plugin, but needs to be enhanced with caching of some kind (I ended up customizing it to get my issue resolved). – jkincali Jun 20 '17 at 09:06
  • 1
    Another thing that helped a different issue was modifying the parameter innodb_buffer_pool_size mentioned above. While trying to find the cause of the high CPU utilization, I read somewhere that innodb_buffer_pool_size should be at least the size of the file ibdata1, which is located in /var/lib/mysql. It seems InnoDB works much more efficiently when it is able to be resident in memory. This may be difficult to do in some situations because ibdata1 can be huge! It was also suggested somewhere to ensure innodb_log_buffer_size is 25% of the size of innodb_buffer_pool_size. – jkincali Jun 20 '17 at 09:15
  • There is also SHOW FULL PROCESSLIST command which shows full query, for me it showed huge join, generated by ORM system... – Aleksandar Pavić Sep 09 '19 at 13:13
173

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
Raghav RV
  • 3,938
  • 2
  • 22
  • 27
RedPixel
  • 1,904
  • 1
  • 11
  • 11
  • 23
    Since the original post was about 3 years ago, I doubt it's the cause of the original poster's problem. But it was the cause of my problem, and saved me just now - so thanks! More info: http://blog.mozilla.org/it/2012/06/30/mysql-and-the-leap-second-high-cpu-and-the-fix/ – Russell G Jul 02 '12 at 14:18
  • 5
    Same problem & solution for me on Ubuntu 12.04. Steps to resolve slightly different: service ntp stop && date -s "`date`" && service ntp start MySQL CPU usage instantly dropped from 50 - 100% down to 0 - 1% – David Laing Jul 02 '12 at 17:55
  • yup that did it on Amazon's EC2 linux AMI as well, just swap 'service ntpd stop/start' for the init.d stuff. – Ty W Jul 02 '12 at 20:04
  • Worked for me, too. Have an upvote. – Lawrence D'Oliveiro Jul 03 '12 at 06:45
  • Sweet, thanks @wwwhizz! Another fix is to just restart your server. Cleared it right up for me. – Jonathan Jul 03 '12 at 17:18
  • Thanks so much! You just saved my bacon under Fedora during a mythtv install. Much obliged! – Mr.Ree Jul 04 '12 at 02:47
  • Wow. Never in a million years would have remembered or figured that out. Thanks @wwwhizz! – zmonteca Jul 04 '12 at 19:18
  • This helped me too, I would never have thought about it and never thought the leap second issue impacted mysql. – Sairam Jul 05 '12 at 15:54
  • Restarting mysqld didn't resolve it, the logs showed nothing, `SHOW PROCESSLIST` had nothing to tell me. Then google pointed me here for the right solution :) – ivy Jul 06 '12 at 17:38
  • Yep, this instantly fixed my issue! – Kyle Fox Jul 09 '12 at 02:31
  • Yes, this saved me as well. Ubuntu needs to do something. – jcampbell1 Jul 13 '12 at 00:47
  • not sure how you figured this out, but it works. Cheers! – Brenden Jul 23 '12 at 04:48
  • It worked for me instantly. Thank you so much! – Telmo Dias Sep 23 '12 at 01:40
  • 3
    Can this be executed only to make sure ? I mean, is it safe to run it even if it's not the reason ? – Muhammad Gelbana Sep 01 '13 at 09:28
  • 2
    July 1, 2015 - I just experienced this very leap second bug on a current AWS EC2 server running Amazon Linux. Use `sudo service ntpd stop` on this configuration. – Matt van Andel Jul 02 '15 at 04:27
  • Me too in a OpenSUSE server... solved it thanks to this thread. Thanks a lot!! – icordoba Jul 02 '15 at 10:40
  • corrupted my date system-wide.. – Kerem Jul 28 '15 at 16:57
  • 1
    +1 for this solution. My MySQL was running at 50-60% for months for no reason, after applying this solution it went down to now all of 0.0-0.3% which was how it supposed to be. Thanks a lot. – zeeshan Aug 05 '15 at 19:52
  • What do you do if you use VMWARE time instead of ntpd as suggested here? http://serverfault.com/questions/95655/why-does-my-ntpd-not-work – Andreas Jan 10 '16 at 19:30
  • 1
    @Andreas VmWare syncs the time with the host machine, therefore make sure the time is updated on the host machine, then synced with the guest machine. – RedPixel Jan 12 '16 at 07:18
  • 1
    Well, looks like a leap second was added again in 2017, because as of 1st Jan our load average increased from 0.05 to 0.50 and stayed there consistently. MySQL was running at around 50% CPU which didn't seem right. After doing the above, it returned to normal instantly. Thank you so much. – MrCarrot Jan 07 '17 at 10:36
  • Great, that solved my problem as well (also started on Jan 1st, 2017) – grobmotoriker Jan 09 '17 at 14:59
  • 1
    I had to change slightly the `date` command to avoid an `invalid date` error (langage issue): `date -s "$(LC_ALL=C date )"`. – MBR Jan 23 '17 at 11:07
  • Wow, can't believe this was what the issue was. `mysqld` was running at 77% CPU for months at idle and this dropped it down to 0.3%. – Joshua Pinter Feb 15 '17 at 16:37
  • 1
    To make it easier for Ubuntu 12.04 LTS users, here is the single chained command: `service ntp stop && date -s "\`date\`" && service ntp start` – Joshua Pinter Feb 15 '17 at 16:38
  • Please is this is safe to use, e.g. master slave replication can be affected, ? – Hamza AZIZ Jul 22 '21 at 14:01
40

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)

Rowland Shaw
  • 37,700
  • 14
  • 97
  • 166
  • 1
    Not sure why this attracted an anonymous downvote, given this has been a cause in the past for some systems. – Rowland Shaw Jun 30 '13 at 08:57
  • 2
    I think the down vote is because having MySQL visible to the outside world is not a good idea. – MikeKulls Jul 19 '13 at 00:55
  • 10
    @MikeKulls No, it's not a good idea, as it will act as a target for lots of people to try and gain entry, which will give a high CPU load - hence my answer as one possible reason. – Rowland Shaw Jul 19 '13 at 08:02
  • 17
    I hate it when someone just down-votes and go ! – Muhammad Gelbana Sep 01 '13 at 09:29
  • 1
    +1 because this is absolutely a legitimate reason for MySQL to have high CPU usage, and anyone for whom this is the answer really really does need this information! – Chris Browne Sep 06 '19 at 11:16