'How to determine and solve mysql high CPU usage?
'top' gives:
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)
(Connections keep on growing.)
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
I restared mysql, optimized all tables and nothing changed.
Any idea ?
Solution 1:[1]
Login to MySQL and check for expensive queries using:
SHOW FULL PROCESSLIST;
Find the offending sql that's causing the 1600 slow queries and performance tune it.
You can also look to enable slow query logging via the below documentation.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |