Shulou(Shulou.com)06/01 Report--

Early in the morning, I received QQ harassment from Haozi, saying that there were a lot of complaints from people moving at the scene, saying that dms could not be connected, and that the query had no data, which almost knocked out his phone.

Quickly let Haozi log on to the database server, confirm that the database is not down, enter the database to use show processlist to find that there are a large number of sleep processes, feel very strange, how there are so many sleep processes.

Seeing that IP is concentrated on the intranet address 176, logging in to 176 to check according to the corresponding port number is not a problem with the application, and it is normal to check and find out.

Take a look at the maximum number of connections, the setting is a bit small, modify the configuration to increase to 1000, after restarting the database, sleep temporarily disappeared, and soon there are many sleep processes.

I looked up the reason on the Internet.

1. Too many persistent connections are used

2. The mysql connection is not closed in time in the program.

3. The database query is not optimized enough and takes too much time.

Looking for Haozi to chat in detail, many mobile users will not turn off Ufiner after checking the data, or even all day, resulting in a large number of processes. This is speechless, the reality is that we can't limit each other's use in this way, okay, think of setting a time limit.

Found on the Internet is to set the values of the following two parameters, that is, to automatically clear the sleep connection after the expiration time

Interactive_timeout:

Parameter meaning: the number of seconds the server waits for activity before closing the interactive connection. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option in mysql_real_connect ().

Default value of parameter: 28800 seconds (8 hours)

Wait_timeout:

Parameter meaning: the number of seconds the server waits for activity before closing a non-interactive connection.

Default value of parameter: 28800 seconds (8 hours)

Add these two parameters to the [mysqld] option of the configuration file / etc/my-ib.cnf, restart the server, and observe for a while before the problem is resolved.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.