Persistent Database Connections

Persistent connections are links that do not close when theexecution of your script ends. When a persistent connection isrequested, PHP checks if there's already an identical persistentconnection (that remained open from earlier) - and if it exists, ituses it. If it does not exist, it creates the link. An 'identical'connection is a connection that was opened to the same host, withthe same username and the same password (where applicable).

People who aren't thoroughly familiar with the way web servers workand distribute the load may mistake persistent connects for whatthey're not. In particular, they do not giveyou an ability to open 'user sessions' on the same link, theydo not give you an ability to build up atransaction efficiently, and they don't do a whole lot of otherthings. In fact, to be extremely clear about the subject,persistent connections don't give you anyfunctionality that wasn't possible with their non-persistentbrothers.

Why?

This has to do with the way web servers work. There are three waysin which your web server can utilize PHP to generate web pages.

The first method is to use PHP as a CGI "wrapper". When run thisway, an instance of the PHP interpreter is created and destroyedfor every page request (for a PHP page) to your web server.Because it is destroyed after every request, any resources that itacquires (such as a link to an SQL database server) are closed whenit is destroyed. In this case, you do not gain anything from tryingto use persistent connections -- they simply don't persist.

The second, and most popular, method is to run PHP as a module in amultiprocess web server, which currently only includes Apache. Amultiprocess server typically has one process (the parent) whichcoordinates a set of processes (its children) who actually do thework of serving up web pages. When a request comes in from aclient, it is handed off to one of the children that is not alreadyserving another client. This means that when the same client makesa second request to the server, it may be served by a differentchild process than the first time. When opening a persistent connection, every following page requesting SQL services can reuse the same established connection to the SQL server.

The last method is to use PHP as a plug-in for a multithreaded webserver. Currently PHP 4 has support for ISAPI, WSAPI, and NSAPI (onWindows), which all allow PHP to be used as a plug-in on multithreadedservers like Netscape FastTrack (iPlanet), Microsoft's Internet InformationServer (IIS), and O'Reilly's WebSite Pro. The behavior is essentiallythe same as for the multiprocess model described before.

If persistent connections don't have any added functionality, whatare they good for?

The answer here is extremely simple -- efficiency. Persistentconnections are good if the overhead to create a link to your SQLserver is high. Whether or not this overhead is really high dependson many factors. Like, what kind of database it is, whether or notit sits on the same computer on which your web server sits, howloaded the machine the SQL server sits on is and so forth. Thebottom line is that if that connection overhead is high, persistentconnections help you considerably. They cause the child process tosimply connect only once for its entire lifespan, instead of everytime it processes a page that requires connecting to the SQLserver. This means that for every child that opened a persistentconnection will have its own open persistent connection to theserver. For example, if you had 20 different child processes thatran a script that made a persistent connection to your SQL server,you'd have 20 different connections to the SQL server, one fromeach child.

Note, however, that this can have some drawbacks if you are using adatabase with connection limits that are exceeded by persistentchild connections. If your database has a limit of 16 simultaneousconnections, and in the course of a busy server session, 17 childthreads attempt to connect, one will not be able to. If there arebugs in your scripts which do not allow the connections to shutdown (such as infinite loops), the database with only 16 connectionsmay be rapidly swamped. Check your database documentation forinformation on handling abandoned or idle connections.

Warning

There are a couple of additional caveats to keep in mind whenusing persistent connections. One is that when using tablelocking on a persistent connection, if the script for whateverreason cannot release the lock, then subsequent scripts using thesame connection will block indefinitely and may require that youeither restart the httpd server or the database server. Another isthat when using transactions, a transaction block will also carryover to the next script which uses that connection if script executionends before the transaction block does. In either case, you canuse register_shutdown_function() to register asimple cleanup function to unlock your tables or roll back yourtransactions. Better yet, avoid the problem entirely by not usingpersistent connections in scripts which use table locks ortransactions (you can still use them elsewhere).

An important summary. Persistent connections were designed to haveone-to-one mapping to regular connections. That means that youshould always be able to replace persistentconnections with non-persistent connections, and it won't changethe way your script behaves. It may (andprobably will) change the efficiency of the script, but not itsbehavior!

See also fbsql_pconnect(),ibase_pconnect(), ifx_pconnect(),ingres_pconnect(),msql_pconnect(), mssql_pconnect(),mysql_pconnect(), ociplogon(),odbc_pconnect(), oci_pconnect(),pfsockopen(), pg_pconnect(), andsybase_pconnect().

add a note add a note

User Contributed Notes 13 notes

php at alfadog dot net
4 years ago
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.
ynzhang from lakeheadu canada
9 years ago
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.
pacerier at gmail dot com
2 years ago
Did anyone else notice that the last paragraph contradicts everything above it?

( cached page: https://archive.is/ZAOwy )
Tom
8 years ago
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.
ambrish at php dot net
7 years ago
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.
christopher dot jones at oracle dot com
10 years ago
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.
andy at paradigm-reborn dot com
11 years ago
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.
fabio
12 years ago
You can in fact provide a port for the connection, take a look at http://de2.php.net/manual/en/function.mysql-pconnect.php#AEN101879

Just use "hostname:port" for the server address.
whatspaz at g NO dot SPAM mail dot c o m
11 years ago
in response to web at nick, have you tried FLUSH PRIVILEGES. this should reload those privileges.
RQuadling at GMail dot com
12 years ago
If you have multiple databases on the same server AND you are using persistent connections, you MUST prefix all the table names with the specific database name.

Changing the database using the xxx_select_db functions alters the database for the connection for all users who are sharing that connection (assuming PHP is running shared and not CGI/CLI).

If you have 2 databases (live and archive) and your script is talking to both, you cannot use 2 persistent connections and change the database for each one.

Internally, persistent connections are used even if you do not specify that you want to use persistent connections. This is why new_link was added to mysql_connect/mssql_connect (PHPV4.2.0+).
jean_christian at myrealbox dot com
15 years ago
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."
aaryal at foresightint dot com
14 years ago
this one bit quite a bit of chunk out of my you-know-what. seems like if you're running multiple database servers on the same host (for eg. MySQL on a number of ports) you can't use pconnect since the port number isn't part of the key for database connections. especially if you have the same username and password to connect to all the database servers running on different ports. but then it might be php-MySQL specific. you might get a connection for an entirely different port than the one you asked for.
jorgeleon85 at gmail dot com
7 years ago
I've been looking everywhere for a benchmark or at least comparison of the overhead used by oci_connect and oci_pconnect.
Just saying "oci_connect is slower because the overhead..." is not enough for me. For than I wrote a couple scripts to compare perfomance.
At the end I found out an average of 34% more time using a oci_connect than oci_pconnect, using a query of 50 rows and 100 columns.
Obviously this wasn't a real benchmark however it gives a simple idea of the efficiency of each function.
To Top