MySQLのプロセスが大量にsleep状態を解決する対策
1.大量sleepプロセスが表示される
mysql> show processlist;
+———+———–+———–+———+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+———+———–+———–+———+———+——+——-+——————+
| 6736889 | ya_startnews24_f | localhost | ya_startnews24 | Sleep | 0 | | NULL |
| 6736917 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 6737039 | ya_startnews24_f | localhost | ya_startnews24 | Sleep | 0 | | NULL |
| 6737042 | ya_startnews24_f | localhost | ya_startnews24 | Sleep | 0 | | NULL |
| 6737044 | ya_startnews24_f | localhost | ya_startnews24 | Sleep | 0 | | NULL |
| 6737047 | ya_startnews24_f | localhost | ya_startnews24 | Sleep | 0 | | NULL |
| 6737049 | ya_startnews24_f | localhost | ya_startnews24 | Sleep | 0 | | NULL |
| 6737050 | ya_startnews24_f | localhost | ya_startnews24 | Sleep | 0 | | NULL |
| 6737052 | ya_startnews24_f | localhost | ya_startnews24 | Sleep | 0 | | NULL |
| 6737054 | ya_startnews24_f | localhost | ya_startnews24 | Sleep | 0 | | NULL |
| 6737055 | ya_startnews24_f | localhost | ya_startnews24 | Sleep | 0 | | NULL |
| 6737056 | ya_startnews24_f | localhost | ya_startnews24 | Sleep | 0 | | NULL |
| 6737057 | ya_startnews24_f | localhost | ya_startnews24 | Sleep | 0 | | NULL |
| 6737058 | ya_startnews24_f | localhost | ya_startnews24 | Sleep | 0 | | NULL |
| 6737059 | ya_startnews24_f | localhost | ya_startnews24 | Sleep | 0 | | NULL |
| 6737060 | ya_startnews24_f | localhost | ya_startnews24 | Sleep | 0 | | NULL |
+———+———–+———–+———+———+——+——-+——————+
16 rows in set (0.00 sec)
2.解決対策
方法1
# vi /etc/my.cnf
[mysqld]
wait_timeout=10
# /etc/init.d/mysql restart
方法2
mysql> set global wait_timeout=10;
mysql> show global variables like 'wait_timeout’;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| wait_timeout | 10 |
+—————————-+——-+