MySQLsleep: a perfect solution with too many connections _ MySQL

A perfect solution for too many MySQLsleep connections: bitsCN.com

The perfect solution for MySQL sleep connection too many

If there are too many sleep connections, what will happen to 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.

Some people share it on the Internet, using shell scripts with cron to regularly kill connections that have been sleeping for too long, but this method is very undesirable. a typical violent violence may cause data crash, and, you also need to write the corresponding shell and set cron, which is cumbersome to implement and is not recommended.

The better way is to let mysql decide the fate of these sleep connections, which is simpler and more effective.

Mysql configuration file contains the following items:

Wait_timeout, you can set the sleep connection timeout seconds. if a connection times out, it will be naturally terminated by mysql!

For example, set:

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, which is easier than writing shell scripts.

So what should I do if the service cannot be stopped on a running production server? Log on to mysql as the root user and run the following command:

Set global wait_timeout = 100

You can.

In my production environment, this method has achieved quite good results.

Of course, the more fundamental method is to troubleshoot from the above three points:

1. in the program, do not use persistent links, that is, use mysql_connect instead of mysql_pconnect.

2. after the program is executed, you should explicitly call mysql_close

3. you can analyze the system SQL query step by step, find and optimize the slow query SQL statements.

BitsCN.com