MySQL 高 CPU 使用率
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1282232/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL high CPU usage
提问by Juddling
Recently my server CPU has been going very high.
最近我的服务器 CPU 一直很高。
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.
CPU 负载平均为 13.91（1 分钟）11.72（5 分钟）8.01（15 分钟），我的网站流量仅略有增加。
After running a top command, I saw MySQL was using 160% CPU!
运行 top 命令后，我看到 MySQL 使用了 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?
最近我一直在优化表，并切换到持久连接。这会导致 MySQL 使用大量 CPU 吗？
回答by Steven Surowiec
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.
其次，我想说您要仔细检查您的 MySQL 用户，以确保任何人都无法从远程服务器进行连接。这也是要检查的主要安全事项。
Thirdly I'd say you want to turn on the MySQL Slow QueryLog 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.
第三，我想说你想打开MySQL 慢查询日志来关注任何需要很长时间的查询，并使用它来确保你没有任何查询锁定关键表太长时间。
Some other things you can check would be to run the following query while the CPU load is high:
您可以检查的其他一些事情是在 CPU 负载很高时运行以下查询：
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).
这将向您显示当前正在运行或在要运行的队列中的任何查询，查询是什么以及它在做什么（如果查询太长，此命令将截断查询，您可以使用 SHOW FULL PROCESSLIST 查看完整的查询文本） .
You'll also want to keep an eye on things like your buffer sizes, table cache, query cacheand 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.
您还需要关注缓冲区大小、表缓存、查询缓存和innodb_buffer_pool_size（如果您使用的是 innodb 表）等内容，因为所有这些内存分配都会对查询性能产生影响，从而导致 MySQL吃掉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 Profilerbut 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.
使用分析器也是一个很好的主意。您可以在需要时打开的功能将显示您的应用程序正在运行哪些查询，是否有重复查询，它们需要多长时间等等。类似这样的例子是我一直在研究的一个例子PHP Profiler但有很多。如果您正在使用像 Drupal、Joomla 或 Wordpress 这样的软件，您需要在社区内询问，因为可能有可用的模块让您无需手动集成任何内容即可获取此信息。
回答by RedPixel
As this is the top post if you google for MySQL high CPU usage or load, I'll add an additional answer:
如果您在 google 上搜索 MySQL 高 CPU 使用率或负载，这是最重要的帖子，我将添加一个额外的答案：
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):
2012 年 7 月 1 日，当前 UTC 时间增加了闰秒，以补偿由于潮汐导致的地球自转速度减慢。当运行 ntp（或 ntpd）时，这一秒被添加到您的计算机/服务器的时钟中。MySQLd 在某些操作系统上似乎不喜欢这额外的一秒，并且会产生很高的 CPU 负载。快速修复是（作为 root）：
$ /etc/init.d/ntpd stop
$ date -s "`date`"
$ /etc/init.d/ntpd start
回答by Rowland Shaw
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)
如果此服务器对外界可见，则值得检查它是否有大量来自外界的连接请求（即试图闯入它的人）
