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

This article will explain in detail how PHP uses database permanent connection to operate MySQL, and the editor thinks it is very practical, so I hope you can get something after reading this article.

PHP programmers should all know that mysql_pconnect (permanent connection) function can be used to connect to MySQL databases, and permanent connection to the database can improve efficiency, but in practical applications, permanent connection to the database often leads to some problems, such as intermittent failure to connect to the database on websites with large visits, and error messages such as "Too many connections in." Restarting the server was normal again, but the same failure occurred after a while. For the causes of these problems, I am afraid that not everyone can explain clearly, although there are some relevant information in the PHP document, but the explanation is not easy to understand, here I have the nerve to try to do a simple discussion, the views are not all correct.

First, take a look at the definition of a database permanent connection:

A permanent database connection is a connection that is not closed at the end of the script. When a request for a permanent connection is received. PHP will check to see if an identical permanent connection (which was previously opened) already exists. If it exists, the connection will be used directly; if it does not, a new connection will be established. The so-called "same" connection is a connection to the same host with the same user name and password.

PHP uses permanent connections to operate MySQL on the premise that PHP must be installed as a plug-in or module to a multithreaded or multi-process Web server. The most common form is to use PHP as a module of a multiprocess Apache server. For a multi-process server, its typical feature is that there is a parent process and a group of child processes running harmoniously, in which the child processes actually generate Web pages. Whenever the client makes a request to the parent process, the request is passed to a child process that has not been occupied by another client request. This means that when the same client makes a request to the server for the second time, it is likely to be processed by a different child process. After a permanent connection is opened, the subsequent pages of all different child processes requesting the SQL service can reuse the established SQL server connection. It allows each child process to make a connection operation only once in its life cycle, rather than making a connection request to the SQL server each time it processes a page. Each child process will establish a separate permanent connection to the server. PHP itself does not have the concept of database connection pool, but Apache has the concept of process pool, an Apache child process will be put back into the process pool after the end, which makes the mysql connection resources opened with mysql_pconnect can not be released, but attached to the corresponding Apache child process saved to the process pool. So it can be reused on the next connection request. Everything seems normal, but when Apache concurrent traffic is high, if you use mysql_pconnect, the MySQL connections occupied by the previous Apache child process do not have close, which quickly makes the MySQL reach the maximum number of connections, so that subsequent requests may not be answered.

Some of the above text is an excerpt from the PHP document, which may still be a little difficult to understand, so I'll give you another example in vernacular:

Assuming that the maximum number of connections configured by Apache is 1000, the maximum number of connections configured by MySQL is 100. when the Apache server receives 200 concurrent visits, 100 of them involve database access, and the remaining 100 do not involve database access, because there are no available database connections at this time, so the 100 concurrency involving database access will produce 100 database permanent connections at the same time, reaching the maximum number of database connections. When these operations are not finished, any other connections can no longer obtain database connections. When these operations are finished, the corresponding connections will be put into the process pool. There are 200idle child processes in the process pool of Apache, of which 100 have database connections. Because Apache will randomly pick free child processes for access requests. So the child process you get is probably one of the 100 that does not include a database connection, and the database connection has reached the maximum, and you cannot successfully establish a new database connection. Alas, you have to keep refreshing the page. When you are lucky, you happen to be assigned to a child process with a database connection in order to browse the page normally. If it is a website with a large number of visitors, there may be a lot of concurrency at any time, so visitors may keep finding that they cannot connect to the database.

You might say, why don't we just adjust the maximum number of connections for Apache and MySQL to the same size? Yes, reasonably adjusting the maximum number of connections will avoid this problem to some extent, but the load capacity of Apache and MySQL is different. If it is set according to the load capacity of Apache, this maximum number of connections will be too large for MySQL, resulting in a large number of permanent connections to MySQL databases. For example, it is like having to support millions of troops in peacetime, and the cost outweighs the gain. And if set according to the load capacity of Mysql, for Apache, the maximum number of connections is on the small side, a little bit of killing feeling, can not play the maximum efficiency of Apache.

Therefore, according to the introduction in the PHP manual, it is only suitable to use database permanent connections on websites with little concurrent access, but for a website with little concurrent access, the efficiency improvement brought by using database permanent connections does not seem to make much sense. From this point of view, I think the database permanent connection in PHP is basically a chicken rib role, if you must use the concept of database connection pool. You can try the mod_dbd provided by sqlrelay or Apache itself, and there may be a surprise.

On "PHP how to use the database permanent connection to operate MySQL is and not" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

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.