MySQL often comes up with a solution to the sleep process

The company has a Linux server dedicated to run the company's Web services, due to historical reasons, the number of sites and pages, resulting in a large number of sleep processes, the MySQL server supports the maximum number of connections is capped, because each connection is built to consume memory, So we want the client to disconnect and free up the memory after it has been connected to MySQL server to handle the appropriate operation. If your MySQL server has a large number of idle connections, they will not only consume memory in vain, but if the connection keeps accumulating, it will eventually reach the maximum number of MySQL server connections, which will report the error of ' too many connections '. For the value setting of wait_timeout, it should be judged according to the operation condition of the system. After the system has been running for a period of time, you can view the current system's connection status through the show Processlist command, if you find a large number of connection processes in the sleep state, then the parameter settings are too large to make the appropriate adjustment smaller.

Mysql> Show variables like "%timeout%";

+-----------------------------+----------+
| variable_name | Value |
+-----------------------------+----------+
| Connect_timeout | 10 |
| Delayed_insert_timeout | 300 |
| Innodb_flush_log_at_timeout | 1 |
| Innodb_lock_wait_timeout | 50 |
| Innodb_rollback_on_timeout | OFF |
| Interactive_timeout | 28800 |
| Lock_wait_timeout | 31536000 |
| Net_read_timeout | 30 |
| Net_write_timeout | 60 |
| Slave_net_timeout | 3600 |
| Wait_timeout | 28800 |
+-----------------------------+----------+
Rows in Set (0.00 sec)

After the query found that the original MySQL has not been optimized, or the original default value: 28800 (that is, 8 hours)

Edit/etc/my.cnf, add the timeout parameter under MYSQLD, set to 120 seconds, as follows:

"Mysqld"
wait_timeout=120
interactive_timeout=120

Note: To set interactive_timeout and wait_timeout at the same time will not take effect.

Finally, restart MySQL to take effect!

This article is from the "Square Hill" blog, please be sure to keep this source http://523514.blog.51cto.com/513514/1547908

MySQL often comes up with a solution to the sleep process