PHP uses the database permanent connection method (mysql_pconnect) to operate MySQL

Author: Lao Wang

PHP programmers should know that they can use the mysql_pconnect (permanent connection) function to connect to the MySQL database. Using a permanent connection to the database can improve efficiency. However, in actual applications, permanent connection to the database may lead to some problems, the general performance is that the database connection is often interrupted on websites with high traffic volumes, and similar to "Too connector connections in... "error message: it is normal to restart the server again, but the same fault will occur again in a short time. I am afraid not everyone can make it clear about the causes of these problems. Although the PHP document contains some relevant information, the explanation is not easy to understand, here I am cheeky and try to make a simple discussion. The points mentioned are not necessarily correct. You are welcome to give your feedback.

First, let's look at the definition of permanent database connection:

A permanent database connection is a connection that is not closed when the script ends running. When receiving a permanent connection request. PHP will check whether there is already a permanent connection (previously Enabled. If yes, the connection is directly used. If no, a new connection is established. The "same" connection refers to the connection to the same host with the same user name and password.

PHP uses the permanent connection method to operate MySQL on the premise that PHP must be installed as a plug-in or module of a multi-threaded or multi-process Web server. The most common form is to use PHP as a module of a multi-process Apache server. For a multi-process server, a typical feature is that a parent process is running in coordination with a group of sub-processes, and the actual webpage is a sub-process. When a client sends a request to the parent process, the request is sent to a child process that is not occupied by other client requests. This means that when the same client sends a second request to the server, it may be processed by a different sub-process. After a permanent connection is enabled, All The subprocesses that request SQL services can re-use the established SQL server connection. It allows each sub-process to perform only one connection operation in its lifecycle, rather than submitting a connection request to the SQL server every time it processes a page. Each sub-process establishes 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. After an Apache sub-process ends, it will be put back into the process pool, this allows the mysql connection resource opened with mysql_pconnect to be stored in the process pool on the corresponding Apache sub-process instead of being released. So it can be reused in the next connection request. Everything seems to be normal, but if mysql_pconnect is used when Apache has a high concurrency traffic, the MySQL connection occupied by the Apache sub-process is not closed, soon MySQL reaches the maximum number of connections, and subsequent requests may not be responded.

Some of the above text is excerpted from the PHP document. It may seem a bit difficult to understand, so I will explain the problem with another example in the vernacular:

Assume that the maximum number of connections configured for Apache is 1000, and the maximum number of connections configured for MySQL is 100. When the Apache server receives 200 concurrent accesses, 100 of them involve database access, the remaining 100 do not involve database access, because there is no available database connection at this time, so the 100 concurrency involved in database access will generate 100 permanent connections at the same time, the maximum number of connections to the database is reached. When these operations are not completed, no other connections can be connected to the database. When these operations are completed, the corresponding connections will be placed in the process pool, at this time, there are 200 idle sub-processes in the Apache process pool, of which 100 are connected to the database, because Apache will randomly select idle sub-processes for access requests, so the sub-process you get is probably one of the 100 that does not contain database connections, and the database connection has reached the maximum value, and you cannot establish a new database connection. Alas, you have to refresh the page constantly. When is the page lucky? It happens that you are assigned a sub-process with a database connection before you can browse the page normally. For websites with large traffic volumes, there may be a large number of concurrent connections at any time, so viewers may constantly find that they cannot connect to the database.

Maybe you will say that we can adjust the maximum number of connections between Apache and MySQL to the same size? Yes, reasonable adjustment of the maximum number of connections will prevent this problem to some extent, but the load capacities of Apache and MySQL are different, for MySQL, the maximum number of connections is too large, and a large number of MySQL databases will be Connected permanently. For example, it is like a peaceful age that needs to support millions of troops. The overhead is not worth the candle; however, if you set the load capacity according to Mysql, for Apache, the maximum number of connections is too small, which is a bit cool and cannot maximize the efficiency of Apache.

Therefore, according to the introduction in the PHP manual, it is only suitable for permanent database connections on websites with low concurrent access, but for a website with low concurrent access, the efficiency improvement caused by permanent database connection does not seem to be significant. From this perspective, I think the permanent database connection in PHP is basically a chicken role, if you must use the concept of database connection pool, you can try sqlrelay or mod_dbd provided by Apache itself, which may surprise you.