There's a third case for PHP: run on a fastCGI interface. In this case, PHP processes are NOT destroyed after each request, and so persistent connections do persist. Set PHP_FCGI_CHILDREN << mysql's max_connections and you'll be fine.
Persistent connections are links that do not close when the execution of the script ends. When a persistent connection is requested, PHP checks whether an identical persistent connection (that remained open from earlier) already exists; if one does, it is reused, and if not, a new link is created. An 'identical' connection is one opened to the same host with the same username and password (where applicable).
There is no way to request a specific connection, or to guarantee whether the returned connection will be an existing one or a brand new one (if all existing connections are in use, or the request is being served by a different worker, which has a separate pool of connections).
PHP's persistent connections therefore cannot be used to, for example:
Persistent connections do not provide any functionality that was not possible with non-persistent connections.
There are two ways in which a web server can utilize PHP to generate web pages:
The first method is to use PHP as a CGI "wrapper". When run this way, an instance of the PHP interpreter is created and destroyed for every page request (for a PHP page) to the web server. Because it is destroyed after every request, any resources that it acquires (such as a link to an SQL database server) are closed when it is destroyed. In this case, there is nothing to be gained from using persistent connections - they simply do not persist.
The second, and most popular, method is to run PHP-FPM, or PHP as a module in a multiprocess web server (currently only Apache). These setups typically have one process (the parent) which coordinates a set of processes (its children) that actually do the work of serving up web pages. When a request comes in from a client, it is handed off to one of the children that is not already serving another client. This means that when the same client makes a second request to the server, it may be served by a different child process than the first time. Once a persistent connection has been opened, any subsequent page served by the same child process can reuse the already established connection to the SQL server.
Note:
The method in use can be checked by looking at the value of "Server API" in the output of phpinfo() or the value of
PHP_SAPI, run from a web request.If the Server API is "Apache 2 Handler" or "FPM/FastCGI", then persistent connections will be used across requests served by the same worker. For any other value, persistent connections will not persist after each request.
As command-line PHP uses a new process for each script, persistent connections are not shared between command-line scripts, so there is no value in using them in transient scripts such as crons or commands. However, they may be useful, for example, in a long-running application server that serves many requests or tasks, each of which may need its own database connection.
Persistent connections are beneficial when the overhead of creating a link to an SQL server is high. Whether this overhead is significant depends on many factors, such as the type of database, whether it resides on the same machine as the web server, and how loaded that machine is. When the connection overhead is high, persistent connections can help considerably: each child process connects only once for its entire lifespan, rather than every time it processes a page that requires a connection to the SQL server. This means every child that opens a persistent connection will maintain its own connection to the server. For example, if 20 different child processes each run a script that makes a persistent connection to the SQL server, there will be 20 separate connections to that server, one from each child.
Note, however, that this can have drawbacks when using a database with connection limits that are exceeded by persistent child connections. If the database has a limit of 16 simultaneous connections, and during a busy server session 17 child processes attempt to connect, one of them will fail. If there are bugs in the scripts that prevent connections from shutting down (such as infinite loops), a database with only 16 connections may be rapidly swamped.
Persistent connections will usually increase the number of connections open at any given time, because idle workers still hold on to the connections they opened for previous requests. If a large number of workers are spun up to handle a spike in traffic, the connections they opened will remain until the worker is terminated or the database server closes the connection.
Ensure that the maximum number of connections allowed by the database server is greater than the maximum number of web request workers (plus any other usage such as crons or administrative connections).
Check the database documentation for information on handling abandoned or idle connections (timeouts). Long timeouts may significantly increase the number of persistent connections open at any one time.
Some database extensions perform automatic cleanup when the connection is reused; others leave this task at the discretion of the application developer. Depending on the chosen database extension and the application design, manual cleanup may be needed before the script exits. Changes that may leave connections in an unexpected state include:
Table locks and transactions that are not cleaned up or closed may cause other queries to be blocked indefinitely and/or cause subsequent reuse of the connection to cause unexpected changes.
Having the wrong database selected will cause subsequent reuse of the connection to be unable to execute queries as expected (or execute them on the wrong database if schemas are similar enough).
If temporary tables are not cleaned up, subsequent requests will not be able to recreate the same table.
Cleanup can be implemented using class destructors or register_shutdown_function(). Dedicated connection pooling proxies that include this as part of their functionality may also be considered.
Given their behavior and potential drawbacks described above, persistent connections should not be used without careful consideration. They should not be used without implementing additional changes to the application and careful configuration of the database server and web server and/or PHP-FPM.
Consider alternative solutions such as investigating and fixing the causes of connection creation overheads (for example, disabling reverse DNS lookups on the database server), or dedicated connection pooling proxies.
For high volume web APIs, consider using alternative runtimes or long-running application servers.
There's a third case for PHP: run on a fastCGI interface. In this case, PHP processes are NOT destroyed after each request, and so persistent connections do persist. Set PHP_FCGI_CHILDREN << mysql's max_connections and you'll be fine.In IBM_DB2 extension v1.9.0 or later performs a transaction rollback on persistent connections at the end of a request, thus ending the transaction. This prevents the transaction block from carrying over to the next request which uses that connection if script execution ends before the transaction block does.One additional not regarding odbc_pconnect and possibly other variations of pconnect:
If the connection encounters an error (bad SQL, incorrect request, etc), that error will return with be present in odbc_errormsg for every subsequent action on that connection, even if subsequent actions don't cause another error.
For example:
A script connects with odbc_pconnect.
The connection is created on it's first use.
The script calls a query "Select * FROM Table1".
Table1 doesn't exist and odbc_errormsg contains that error.
Later(days, perhaps), a different script is called using the same parameters to odbc_pconnect.
The connection already exists, to it is reused.
The script calls a query "Select * FROM Table0".
The query runs fine, but odbc_errormsg still returns the error about Table1 not existing.
I'm not seeing a way to clear that error using odbc_ functions, so keep your eyes open for this gotcha or use odbc_connect instead.For the oci8 extension it is not true that " [...] when using transactions, a transaction block will also carry over to the next script which uses that connection if script execution ends before the transaction block does.". The oci8 extension does a rollback at the end scripts using persistent connections, thus ending the transaction. The rollback also releases locks. However any ALTER SESSION command (e.g. changing the date format) on a persistent connection will be retained over to the next script.It seems that using pg_pconnect() will not persist the temporary views/tables. So if you are trying to create temporary views/tables with the query results and then access them with the next script of the same session, you are out of luck. Those temporary view/tables are gone after each PHP script ended. One way to get around this problem is to create real view/table with session ID as part of the name and record the name&creation time in a common table. Have a garbage collection script to drop the view/table who's session is expired.If anyone ever wonders why the number of idle db process (open connections) seems to grow even though you are using persistent connections, here's why:
"You are probably using a multi-process web server such as Apache. Since
database connections cannot be shared among different processes a new
one is created if the request happen to come to a different web server
child process."To those using MySQL and finding a lot of leftover sleeping processes, take a look at MySQL's wait_timeout directive. By default it is set to 8 hours, but almost any decent production server will have been lowered to the 60 second range. Even on my testing server, I was having problems with too many connections from leftover persistent connections.