The perfect solution for MySQL sleep connection too many

The perfect solution for too many MySQL sleep connections is 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? Www.2cto.com 1. too many persistent connections are used (in my opinion, persistent connections are not suitable for high-concurrency systems. the mysql connection is not closed in time. 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. In the mysql configuration file, there is one item: wait_timeout. You can set the number of sleep connection timeout seconds. If a connection times out, it will be naturally terminated by mysql. How can this be done! For example, wait_timeout = 100 # sets the mysql connection sleep time to 100 seconds. If the sleep connection sleep time exceeds 100 seconds, the mysql service will naturally terminate, it 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 set global wait_timeout = 100. 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_close3. You can analyze the system SQL query step by step, locate the slow query SQL, and optimize it.