php database permanent connection

A permanent database connection refers to a connection that is not closed when the script finishes running. When a request for a permanent connection is received. PHP will check whether there is already an identical permanent connection (that has been opened previously). If it exists, the connection will be used directly; if it does not exist, a new connection will be established. The so-called "same" connection refers to a connection to the same host using the same username and password.

Readers who do not fully understand the work of a web server and distributed load may misunderstand the role of persistent connections. In particular, a persistent connection does not provide the ability to establish a "user session" on the same connection, nor does it provide the ability to effectively establish a transaction. In fact, strictly speaking, a permanent connection does not provide any special features that a non-persistent connection does not provide.

Why?

This has to do with the way the web server works. There are three ways a web server can use PHP to generate web pages.

The first method is to use PHP as a "shell". Running this way, PHP spawns and terminates a PHP interpreter thread for each PHP page request made to the web server. Because this thread ends with each request, any resources utilized in this thread (such as connections to a SQL database server) will be closed with the end of the thread. In this case, using permanent connections won't change anything - because they aren't permanent at all.

The second and most common method is to use PHP as a module in a multi-process web server. This method currently only works with Apache. For a multi-process server, the typical feature is that there is a parent process and a group of child processes running in coordination, among which the child process actually generates the web page. Whenever a client makes a request to the parent process, the request is passed to the child process that has not been occupied by other client requests. This means that when the same client makes a request to the server for the second time, it may be handled by a different child process. After opening a permanent connection, all subsequent pages that request the SQL service can reuse the established SQL Server connection.

The last method is to use PHP as a plugin for a multi-threaded web server. Currently PHP 4 already supports ISAPI, WSAPI and NSAPI (under Windows environment), which allows PHP to be used as a multi-threaded web server such as Netscape FastTrack (iPlanet), Microsoft's Internet Information Server (IIS) and O'Reilly's WebSite Pro. plugin. The behavior of permanent connections is essentially the same as the multi-process model described previously. Note that PHP 3 does not support SAPI.

If a permanent connection doesn’t have any additional features, what are the benefits of using it?

The answer is very simple - efficiency. Persistent connections are more efficient when clients make very frequent connection requests to the SQL server. The criteria for frequent connection requests depend on many factors. For example, the type of database, whether the database service and the web service are on the same server, how the SQL server loads the load, etc. But we at least know that when connection requests are frequent, permanent connections will significantly improve efficiency. It allows each child process to perform only one connection operation in its life cycle, instead of making a connection request to the SQL server every time a page is processed. This means that each child process will establish its own independent permanent connection to the server. For example, if 20 different child processes run a script that establishes a permanent connection to a SQL server, there are actually 20 different permanent connections established to the SQL server, one for each process.

Note that if the number of permanently connected child processes exceeds the set database connection limit, the system will have some defects. If the database has a limit of 16 simultaneous connections, and in the case of a busy session, 17 threads try to connect, then one thread will fail to connect. If at this time, an error occurs in the script that prevents the connection from being closed (such as an infinite loop), the 16 connections to the database will be quickly affected. Consult the documentation for using your database for information on how to handle abandoned and idle connections.

There are some special issues to pay attention to when using permanent connections. For example, when using a table lock in a permanent connection, if the script cannot release the table lock for any reason, subsequent scripts using the same connection will be permanently blocked, requiring the httpd service or database service to be restarted. In addition, when using transaction processing, if the script ends before the transaction blocking occurs, the blocking will also affect the next script using the same connection. No matter what the situation, you can register a simple cleanup function to open the data table lock or roll back the transaction by using the register_shutdown_function() function. Or a better way is not to use permanent connections in scripts that use data table locks or transaction processing, which can fundamentally solve this problem (of course you can also use permanent connections in other places).

The following is an important summary. Persistent connections are designed to establish a one-to-one distribution for common connections. This means that you must be able to guarantee that the behavior of the script does not change when replacing a persistent connection with a non-persistent connection. Using a persistent connection will (very) likely change the efficiency of the script, but not its behavior!