MySQL高CPU使用率 [英] MySQL high CPU usage
问题描述
最近我的服务器CPU一直很高.
Recently my server CPU has been going very high.
CPU负载平均为13.91(1分钟)11.72(5分钟)8.01(15分钟)，而我的网站的访问量仅略有增加.
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.
运行一条最高命令后，我看到MySQL使用的CPU占160％！
After running a top command, I saw MySQL was using 160% CPU!
最近，我一直在优化表，并切换到持久连接.这会导致MySQL使用大量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?
推荐答案
首先，我可能要关闭持久性连接，因为它们几乎总是弊大于利.
First I'd say you probably want to turn off persistent connections as they almost always do more harm than good.
第二，我想说的是要仔细检查MySQL用户，以确保没有人可以从远程服务器进行连接.这也是要检查的主要安全事项.
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 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.
您可以检查的其他一些事情是在CPU负载很高时运行以下查询:
Some other things you can check would be to run the following query while the CPU load is high:
SHOW PROCESSLIST;
这将向您显示当前正在运行或正在运行的队列中的所有查询，查询的内容以及查询的状态(如果该查询过长，该命令将截断该查询，您可以使用SHOW FULL 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).
您还需要注意缓冲区大小之类的内容，查询缓存和 innodb_buffer_pool_size (如果您使用的是innodb表)，因为所有这些内存分配都可能影响查询性能，从而导致MySQL吞噬CPU.
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
使用探查器也是一个很好的主意.您可以在需要时启用一些功能，以向您显示应用程序正在运行的查询，是否存在重复的查询，查询需要花费多长时间等，等等. PHP Profiler ，但是那里有很多.如果您使用的是Drupal，Joomla或Wordpress之类的软件，您可能想在社区中四处询问，因为可能有适合他们的模块，这些模块使您无需手动集成任何内容即可获取此信息.
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.
这篇关于MySQL高CPU使用率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
