MySQL sleep process too many connections get stuck problem solution

Cause Analysis of excessive MySQL sleep process connections

When the php page is executed, everything is automatically released. I believe many people share the same idea. It turns out that this is not the case. For example, the session will not be released as the page is executed.

Php's garbage collection mechanism is only for php itself. For mysql, php does not have the right to automatically release its contents. If you do not call mysql_close () before the page is executed, mysql will not close the connection. If you are using the pconnect method, even if you call mysql_close () before the page is executed, you cannot close this connection with another mysql.

Sleeping connections in mysql may affect the mysql server.

It seriously consumes mysql server resources (mainly cpu and memory) and may cause mysql to crash.

What causes excessive sleep connections?

1. Too many persistent connections are used (in my opinion, persistent connections are not suitable for high-concurrency systems)

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

3. The database query is not optimized enough to consume too much time.

If you want to fundamentally solve the problem of too many sleep connections, you have to repeat the above three points, but the effect is not quick.

In the mysql configuration file, set the process that automatically closes the link for a long time:

Modify the mysql configuration file my. cnf, which is my. ini in the window system.

[Mysqld]
......
Wait_timeout = 100
Interactive_timeout = 100

Wait_timeout = 100 # set the mysql connection sleep time to 100 seconds. If the sleep connection sleep time exceeds 100 seconds, it will be naturally terminated by the mysql service.

Restart mysql after setting

Service mysqldrestart

Modify the mysql memory usage parameters as follows:

Change table_open_cache = 2000

Table_open_cache = 256
Table_definition_cache = 1400

Table_definition_cached = 400