How to reduce the empty connection process of sleep in php and mysql

There are a large number of empty connections in the sleep state of the database in the developed system. At the same time, through the Log, it is found that there are a large number of exceptions in the feedback of the third-party API interface requested through curl of php in the system. I can't help but connect the two to analyze the reasons. The log reflects that the third-party interface responds slowly and the result is empty. The reason is unknown, but it is conceivable that PHP waits for the connection to return after issuing a curl request. During the waiting process, the database connection begins to sleep until curl times out and the database link is released after the process is executed.

1. php+mysql+memcache practical technical test

There are two abnormal questions, the questions are very abnormal, but they are all real cases encountered in actual combat,

1: I write a program that uses both mysql and memcache.

The first line is mysql_connect, and the second line is memcache_connect

Write it over, the first line is memcache_connect, the second line is mysql_connect

caoz found that there is a big difference between the two writing methods in practice. What is the difference?

2: I wrote a program, using mysql, generated a page and finally used echo $html; to output

One way to write it is

mysql_close();

echo $html;

The other is

echo $html;

mysql_close();

caoz found that there is a big difference between the two in practice. What is the difference?

Both two cases are discovered and adjusted in practice.

caoz is not someone who pursues BT when writing programs. caoz often emphasizes with engineers that it does not pursue extreme technical expression or technical showoff. Therefore, if readers think that the questions here are for so-called reasons such as a certain writing method is better than a certain If the writing resource overhead is smaller or something else, this is really not the original intention of caoz.

These two questions are typical cases encountered in real operating environments. Where are the typical cases? That is, when you encounter a system failure, how do you analyze, how do you think, and how do you judge the impact of multiple related factors. Therefore, it does not matter whether you can answer this question or not. What is important is how to think about the relationship between systems.

A typical system failure is that mysql has too many connections, or too many connections. This problem has troubled us for a long time. If this is caused by indexing or concurrent data requests, locate the cause. It's not complicated, but after the above problem was solved, a strange phenomenon occurred. There was almost no pressure on the database, no blocked processes, and no slow queries, but there were many mysql connections, and they were all sleep connections. At this time, there are many links to the webserver. In other words, because PHP execution is blocked, the MySQL link cannot be released quickly. So, why does PHP block? Breakpoint-by-breakpoint analysis revealed that echo delayed the most time.

This incident has given caoz some experience. I have never considered echo to be a time blocking point before (if you test on this machine, you will think that its time delay is almost 0), but instance tracking found that, Echo actually represents the process of network transmission in our working environment. In other words, it will wait due to routing and bandwidth factors. At this time, the mysql link is still there and has not been released. Yes, everyone sees the question. Everyone will think that mysql_close should be placed after echo, but why echo will delay time, few people will think of it. Of course, this is also related to the working environment. Caoz only knows that the webserver we configured will have this situation. Is there any other configuration mode? Caoz has not tested it, so I dare not say nonsense, but the experience here is that mysql_close is placed in front of echo. , a large number of sleep links will quickly decrease.

echo does not consume too much system resources, but it will wait for network transmission. In a high-concurrency network environment, it is good for the database to pay attention to this.

After this problem was solved, mysql became much healthier, but occasionally the problem of too many links would occur, which troubled me for a long time. Until one day, according to some error messages reported by users, It was found that the memcached server had instability factors. It turned out that the memcached traffic was too high and blocked, and the php process was waiting for the link, resulting in a large number of mysql link waits. This is the origin of the first question. In fact, there is no standard answer to this question itself, but you should have an awareness. , when you start two links A and B at the same time in a script, then if you cannot guarantee that these two links are necessarily reliable (usually cannot be guaranteed), then once the latter is blocked, it will cause the former to wait for a large number of links , while the former blocks, it usually does not affect the latter. So, the answer depends on which link is more important to your application and which link has greater concurrency support.

Both questions mean the same thing in the final analysis. When encountering system problems and failures, think more about the impact of some related factors and the logic of the response sequence of the entire architecture. There are too many database connections. It must be caused by the database. There are too many web links. It is not necessary to optimize the webserver. Related factors may be the root cause. Only by solving the root cause will the symptoms be completely solved.

2. Effective method to reduce MySQL’s Sleep process

1. Generally speaking, the reason why MySQL has a large number of Sleep processes is because it adopts PHP's MySQL long link database method, that is, using mysql_pconnect to open the linked database. The solution is to use "short" links. That is, the mysql_connect function.
2. When using the mysql_connect short link method to open the database, each page will execute SQL after opening the database. When the page script ends, the MySQL connection will automatically close and release memory. However, there are still a large number of Sleep processes. You can check whether the website has the following problems.
A. There are a large number of static files on the hard disk, or the WEB server is too heavily loaded, and the response to HTTP requests becomes too slow. This may also lead to a large number of Sleep processes. The solution is to appropriately adjust the WEB service parameters and files, blindly Static or cached web content is not a panacea.
B, in web scripts, some calculations and applications may be very time-consuming. For example, after opening the database at 0 seconds and executing a SQL code, the web script then spends 20 seconds to perform a complex operation, or requires a huge PHP file (such as a filter function containing thousands of illegal keywords). At this time, in the process seen in the MySQL background, MySQL did not do anything during this 20-second process and was always in Sleep. status until the page completes execution or reaches the wait_timeout value (is forcibly closed), optimize the web page script and try to make the program run as quickly as possible, or execute mysql_close to forcibly close the current MySQL link during this time-consuming running process.
C, in the collection station, the phenomenon of a large number of Sleep processes in MySQL is particularly obvious (for example, many netizens asked about the large number of Sleep processes in DeDeCMS’s MySQL), because most of the collector pages are opened in advance during the running process. Open a MySQL link (perhaps to verify user permissions, etc.), and then start using operations such as file_get_contents to obtain a remote web page content. If the remote site access speed is too slow, for example, it takes 10 It takes seconds to retrieve the web page, so the current acquisition script is always blocked here, and MySQL does nothing and is always in Sleep state. The solution is the same as above. When issuing file_get_contents to collect remote web pages, use mysql_close to forcefully close the MySQL connection. Wait for the collection to be completed and then re-mysql_connect when necessary.

The above is the detailed content of How to reduce the empty connection process of sleep in php and mysql. For more information, please follow other related articles on the PHP Chinese website!