Solutions to frequent sleep processes in MySQL

Solutions to frequent sleep processes in MySQL

The company has a Linux server dedicated to running the company's WEB services. due to historical reasons, there are a large number of sites and pages, which often lead to a large number of sleep processes; the maximum number of connections supported by the MySQL Server is limited, because the creation of each connection consumes memory. Therefore, we hope that after the client connects to the MySQL Server to process the corresponding operations, disconnect and release the occupied memory. If your MySQL Server has a large number of idle connections, they will not only consume the memory in vain, but if the connections continue to accumulate and continue to open, it will eventually reach the maximum number of connections of the MySQL Server, this will report the 'too has connections' error. The value of wait_timeout should be determined based on the system running condition. After the system runs for a period of time, you can run the show processlist command to view the connection status of the current system. If a large number of connection processes in sleep status are found, this parameter is set too large, you can make some adjustments.

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 |
+ ----------------------------- + ---------- +
11 rows in set (0.00 sec)

After query, it is found that mysql has not been optimized, or the default value is 28800 (8 hours)

Edit/etc/my. cnf and add the timeout parameter under mysqld. Set it to 120 seconds, as shown below:

[Mysqld]
Wait_timeout = 120
Interactive_timeout = 120

Note: interactive_timeout and wait_timeout must be set at the same time to take effect.

Restart mysql to take effect!

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: