如何确定和解决 MySQL 高 CPU 使用率？

Question

'top'的作用：

top - 13:07:35 up 827 days,  8:18,  2 users,  load average: 5,26, 5,38, 5,45
KiB Mem:  32848512 total, 32265240 used,   583272 free,   261908 buffers
KiB Swap:  1046520 total,   313784 used,   732736 free, 28190744 cached

  PID USER      PR  NI  VIRT  RES  SHR S  %CPU %MEM    TIME+  COMMAND                                                                                                                  
23927 mysql     20   0  917m 243m 8152 S 553,7  0,8  65:09.27 mysqld





mysql> SHOW FULL PROCESSLIST;
+------+--------+-----------------+-------------+---------+------+--------------+--------------------------------------------------------------------+
| Id   | User   | Host            | db          | Command | Time | State        | Info                                                               |
+------+--------+-----------------+-------------+---------+------+--------------+--------------------------------------------------------------------+
|  600 | oneill | localhost       | NULL        | Query   |    0 | NULL         | SHOW FULL PROCESSLIST                                              |
|  956 | oneill | 127.0.0.1:42219 | FoxPoker    | Sleep   |    0 |              | NULL                                                               |
| 1318 | oneill | 127.0.0.1:44207 | FoxPoker    | Sleep   |    0 |              | NULL                                                               |
| 1319 | oneill | 127.0.0.1:44210 | FoxPoker    | Sleep   |    0 |              | NULL                                                               |
| 1321 | oneill | 127.0.0.1:44215 | FoxPoker    | Sleep   |    0 |              | NULL                                                               |
| 1489 | oneill | 127.0.0.1:45218 | FoxPoker    | Sleep   |    0 |              | NULL                                                               |
| 1490 | oneill | 127.0.0.1:45217 | FoxPoker    | Sleep   |    0 |              | NULL                                                               |
| 1700 | oneill | 127.0.0.1:46246 | FoxPoker    | Sleep   |    0 |              | NULL                                                               |
| 1701 | oneill | 127.0.0.1:46248 | FoxPoker    | Sleep   |    0 |              | NULL                                                               |
| 2203 | oneill | 127.0.0.1:49218 | FoxPoker    | Sleep   |    0 |              | NULL                                                               |
| 3262 | oneill | 127.0.0.1:54855 | FoxPoker    | Sleep   |    0 |              | NULL                                                               |
| 3263 | oneill | 127.0.0.1:54858 | FoxPokerDev | Sleep   |    0 |              | NULL                                                               |
| 3462 | oneill | 127.0.0.1:56029 | FoxPoker    | Sleep   |    0 |              | NULL                                                               |
| 4269 | oneill | 127.0.0.1:60551 | FoxPoker    | Sleep   |    0 |              | NULL                                                               |
| 4945 | oneill | 127.0.0.1:36032 | FoxPoker    | Query   |    3 | Sending data | SELECT COUNT(deviceId) FROM devices WHERE BINARY deviceId="pMGmpY" |
| 4950 | oneill | 127.0.0.1:36073 | FoxPoker    | Query   |    1 | Sending data | SELECT COUNT(deviceId) FROM devices WHERE BINARY deviceId="tAf994" |
| 4952 | oneill | 127.0.0.1:36083 | FoxPoker    | Query   |    1 | Sending data | SELECT COUNT(deviceId) FROM devices WHERE BINARY deviceId="ea6Kv2" |
| 4954 | oneill | 127.0.0.1:36097 | FoxPoker    | Query   |    1 | Sending data | SELECT COUNT(deviceId) FROM devices WHERE BINARY deviceId="yFYvur" |
| 4955 | oneill | 127.0.0.1:36098 | FoxPoker    | Query   |    1 | Sending data | SELECT COUNT(deviceId) FROM devices WHERE BINARY deviceId="73qTcv" |
| 4956 | oneill | 127.0.0.1:36099 | FoxPoker    | Query   |    1 | Sending data | SELECT COUNT(deviceId) FROM devices WHERE BINARY deviceId="q64MfG" |
+------+--------+-----------------+-------------+---------+------+--------------+--------------------------------------------------------------------+



mysql> show status like '%onn%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 1     |
| Connections              | 5528  |
| Max_used_connections     | 25    |
| Ssl_client_connects      | 0     |
| Ssl_connect_renegotiates | 0     |
| Ssl_finished_connects    | 0     |
| Threads_connected        | 19    |
+--------------------------+-------+
7 rows in set (0.00 sec)

(连接不断增长。)

mysqladmin status -h localhost -u  -p
Uptime: 924  Threads: 21  Questions: 92803  Slow queries: 1677  Opens: 1025  Flush tables: 1  Open tables: 265  Queries per second avg: 100.436

我重启了mysql，优化了所有表，但仍然没有改变。有什么想法吗？

- yarek

---

在你的代码中，你有关闭连接或关闭查询吗？ - treyBake

@yarek，看起来你的应用程序没有重用连接或者没有关闭数据库连接。首先查看应用程序如何连接到数据库。 - sfgroups

连接似乎只是一个计数器，如果我正确地阅读了手册的话。"连接尝试的次数（成功或不成功）"...请参见：https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html#statvar_Connections - Paul T.

如果您已经安装了sysstat，您能否在iostat命令的输出中进行编辑？这将显示您的硬盘驱动器是否成为瓶颈/抖动。您是否为该进程分配了足够的内存？您是否正在进行交换操作？free和vmstat的输出情况如何？ - Matt Clark

这里最重要的是慢查询数量。你似乎有一些慢查询。查看慢查询日志，并发布任何有趣的内容。 - e4c5

Answer 1

登录MySQL并使用以下命令检查昂贵的查询：

SHOW FULL PROCESSLIST;

找出导致1600个慢查询和性能下降的有问题的SQL语句，并进行性能调优。

您还可以查看以下文档以启用慢查询日志记录。

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

- Dom DaFonte