In depth: The pros and cons of PHP using database persistent connection to operate MySQL

PHP programmers should all know connection MySQL database can use mysql_pconnect (permanent) function, using the database of permanent connection can improve the efficiency, but in the practical application of the database 1 some problems often lead to permanent connections, performance is usually in the big traffic on the website of frequent intermittent unable to connect to the database, a similar "Too many connections in... "Error message, restart the server again normal, but a short time later the same failure. I am afraid that not everyone can be clear about the causes of these problems. Although there are some relevant materials in the PHP document, the explanation is not easy to understand. Here I shamelessly try to make a simple discussion.

First, take a look at the definition of a persistent database connection:
A permanent database connection is one that is not closed when the script ends running. When a request for a permanent connection is received. PHP will check to see if there is already 1 (previously opened) identical permanent connection. If it exists, the connection is used directly; If none exists, a new connection is established. A "same" connection is a connection to the same host with the same username and password.

PHP operates on the premise that MySQL USES a permanent connection: PHP must be installed as a plug-in or module for a multithreaded or multiprocess Web server. The most common form is to use PHP as a module for a multi-process Apache server. For a multi-process server, the typical feature is that there is a parent process and a set of child processes running in coordination, where the child process actually generates Web pages. Whenever a client makes a request to the parent process, the request is passed to the child process that is not already occupied by another client request. This means that when the same client makes a second request to the server, it will probably be handled by a different child process. After a permanent connection is enabled, subsequent pages of the SQL service requested by all different child processes can reuse this established SQL server connection. Instead of making a connection request to the SQL server every time a page is processed, it causes each child process to make only one connection operation during its lifetime. Each child process will establish a separate permanent connection to the server. PHP itself does not have the concept of a database connection pool, but Apache does have the concept of a process pool, where one Apache child is put back into the process pool after the end of the process, which means that the mysql connection resource opened with mysql_pconnect is not released, but attached to the corresponding Apache child and saved to the process pool. It can then be reused on the next connection request. 1 cut seems to be very normal, but when the concurrent traffic of Apache is large, if you use mysql_pconnect, because the previous Apache child process occupied MySQL connection is not close, soon make MySQL reach the maximum number of connections, so that the request may not be responded to.

Some of the above text is from the PHP document. It may still seem a little too academic to understand, so Let me give you another example to illustrate the point:

Hypothesis Apache configuration maximum number of connections for 1000, MySQL configuration maximum number of connections to 100, when Apache server received 200 concurrent access, 100 of which involves the database access, the remaining 100 does not involve the use of database access, did not exist at this time because of the available database connection, so it involves database access 100 concurrent will produce 100 permanent database connection at the same time, the maximum number of connections to database, when these operations without end, any other connection is unable to obtain a database connection, When the operation is over, the corresponding connection pool will be in the process, the process of Apache pool had 200 free child processes, of which 100 are with the database connection, due to the access request Apache be randomly selected free child process, so you get the child process is probably not include database connections that one in 100, and the database connection has reached the maximum value, you can't succeed to create a new database connection, alas, you have to constantly refresh the page, which was good luck, happened to be assigned to the child with a database connection, To browse the page normally. If it's a heavily visited site, there's likely to be a lot of concurrency going on at any given time, so visitors can't stop finding that they can't connect to the database.

You might say, why don't we just make the maximum number of connections for Apache and MySQL one size? Yes, reasonable adjustment of the maximum number of connections to some extent can avoid the happening of the problem, but Apache and MySQL load capacity is different, if according to the load capacity of Apache to set, for MySQL, the maximum number of connections is larger, can produce a large number of permanent MySQL database connection, for example, like peacetime to support 1 millions of army 1 sample, its cost was not worth the cost; However, if the load capacity of Mysql is set, for Apache, the maximum number of connections is too small, which is a bit like killing a cat and killing a cat, and the maximum efficiency of Apache cannot be played.

So according to the PHP introduction brochure, not only suitable for concurrent access to the website using the database permanent connections, but not for a concurrent access to the site, using the database of permanent connection efficiency does not seem to be too big significance, from this perspective, I think PHP database of permanent link is basically a small role, if you need to use the concept of database connection pool 1, l can try sqlrelay or Apache itself provides mod_dbd, maybe there will be a surprise.