mysql - 如何判断和解决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，优化了所有表，但没有任何改变。

有什么想法吗？

Answer 1

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

SHOW FULL PROCESSLIST;

找到导致 1600 慢查询的违规 sql 并对其进行性能调整。

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

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