What Are the Disadvantages of Using Persistent Connection in Pdo

What are the disadvantages of using persistent connection in PDO

Please be sure to read this answer below, which details ways to mitigate the problems outlined here.

---

The same drawbacks exist using PDO as with any other PHP database interface that does persistent connections: if your script terminates unexpectedly in the middle of database operations, the next request that gets the left over connection will pick up where the dead script left off. The connection is held open at the process manager level (Apache for mod_php, the current FastCGI process if you're using FastCGI, etc), not at the PHP level, and PHP doesn't tell the parent process to let the connection die when the script terminates abnormally.

If the dead script locked tables, those tables will remain locked until the connection dies or the next script that gets the connection unlocks the tables itself.

If the dead script was in the middle of a transaction, that can block a multitude of tables until the deadlock timer kicks in, and even then, the deadlock timer can kill the newer request instead of the older request that's causing the problem.

If the dead script was in the middle of a transaction, the next script that gets that connection also gets the transaction state. It's very possible (depending on your application design) that the next script might not actually ever try to commit the existing transaction, or will commit when it should not have, or roll back when it should not have.

This is only the tip of the iceberg. It can all be mitigated to an extent by always trying to clean up after a dirty connection on every single script request, but that can be a pain depending on the database. Unless you have identified creating database connections as the one thing that is a bottleneck in your script (this means you've done code profiling using xdebug and/or xhprof), you should not consider persistent connections as a solution to anything.

Further, most modern databases (including PostgreSQL) have their own preferred ways of performing connection pooling that don't have the immediate drawbacks that plain vanilla PHP-based persistent connections do.

---

To clarify a point, we use persistent connections at my workplace, but not by choice. We were encountering weird connection behavior, where the initial connection from our app server to our database server was taking exactly three seconds, when it should have taken a fraction of a fraction of a second. We think it's a kernel bug. We gave up trying to troubleshoot it because it happened randomly and could not be reproduced on demand, and our outsourced IT didn't have the concrete ability to track it down.

Regardless, when the folks in the warehouse are processing a few hundred incoming parts, and each part is taking three and a half seconds instead of a half second, we had to take action before they kidnapped us all and made us help them. So, we flipped a few bits on in our home-grown ERP/CRM/CMS monstrosity and experienced all of the horrors of persistent connections first-hand. It took us weeks to track down all the subtle little problems and bizarre behavior that happened seemingly at random. It turned out that those once-a-week fatal errors that our users diligently squeezed out of our app were leaving locked tables, abandoned transactions and other unfortunate wonky states.

This sob-story has a point: It broke things that we never expected to break, all in the name of performance. The tradeoff wasn't worth it, and we're eagerly awaiting the day we can switch back to normal connections without a riot from our users.

singleton DB vs presistent DB

A non-persistent singelton connection (PDO) is getting closed at the end of the script (response). Persistent connections are not closed at the end of the script, but are cached and re-used when another script requests a connection using the same credentials. A persistent connection can cause unwanted side-effects and security issues.

What are the disadvantages of using persistent connection in PDO

Persistent DB Connections - Yea or Nay?

You could use this as a rough "ruleset":

YES, use persistent connections, if:

• There are only few applications/users accessing the database, i.e. you will not result in 200 open (but probably idle) connections, because there are 200 different users shared on the same host.
• The database is running on another server that you are accessing over the network
• An (one) application accesses the database very often

NO, don't use persistent connections, if:

• Your application only needs to access the database 100 times an hour.
• You have many webservers accessing one database server
• You're using Apache in prefork mode. It uses one connection for each child process, which can ramp up fairly quickly. (via @Powerlord in the comments)

Using persistent connections is considerable faster, especially if you are accessing the database over a network. It doesn't make so much difference if the database is running on the same machine, but it is still a little bit faster. However - as the name says - the connection is persistent, i.e. it stays open, even if it is not used.

The problem with that is, that in "default configuration", MySQL only allows 1000 parallel "open channels". After that, new connections are refused (You can tweak this setting). So if you have - say - 20 Webservers with each 100 Clients on them, and every one of them has just one page access per hour, simple math will show you that you'll need 2000 parallel connections to the database. That won't work.

Ergo: Only use it for applications with lots of requests.

Too many connections with PDO persistent connection?

From the PHP manual ~ http://php.net/manual/en/pdo.connections.php

Many web applications will benefit from making persistent connections to database servers. Persistent connections are not closed at the end of the script, but are cached and re-used when another script requests a connection using the same credentials.

So I would advise removing the DbConnection#close() method as you would not want to ever call this.

Also from the manual...

Note:

If you wish to use persistent connections, you must set PDO::ATTR_PERSISTENT in the array of driver options passed to the PDO constructor. If setting this attribute with PDO::setAttribute() after instantiation of the object, the driver will not use persistent connections.

So you'll want (at least)

new \PDO("mysql:host=127.0.0.1;dbname=" . DBNAME, DBUSER, DBPASS, [
    PDO::ATTR_PERSISTENT => true
]);

You can also set your other connection attributes in the constructor.

Should PDO::ATTR_PERSISTENT be used every time?

The issue with persistant connections is that the number of connections available to MySQL is limited. If something goes wrong and that connection isn't closed, the server is going to leave it open for a long time. If the server runs out of connections, then every single application tied to it is going to be unavailable until someone intervenes.

You can probably expect something to go wrong from time to time, and under the wrong circumstances the problem of resource over-usage can leak into months if not noticed, leaving you with a very gradual degrade in performance and increase in system utilization over time (all for no gain).

Here is one good article that can help you. It focuses on MySQL, but most of the same thoughts can be generalized across the spectrum of DBMS's.

Are PHP persistent connections evil ?

Fully Understanding PDO ATTR_PERSISTENT

Apaches point of view

Apache has one parent process. This process creates child processes that will handle any requests coming to the web server.
The initial amount of child processes being started when the web server starts is configured by the StartServers directive in the apache configuration. The number goes up as needed with a raising amount of requests hitting the web server until ServerLimit is reached.

PHP and persistent connections

If PHP (ran as mod_php, as CGI all resources are freed at the end of script execution) is now being told to establish a persistent connection with a database for a request, this connection is hold even after the script finishes.
The connection being now hold is a connection between the apache child process which the request was handled by and the database server and can be re-used by any request that is being handled by this exact child process.

If, for some reason (do not ask me exactly why), the child process is being occupied longer than the actual request and another request comes in, the parent apache process redirects this request to a (new) child process which may has not established a connection to the database up to this time. If it has to during the execution of the script, it raises the SID as you have observed. Now there are two connections be hold by two different child processes of apache.

Keep in mind that...

It is important to know, that this can also cause a lot of trouble.
If there is an endless loop or an aborted transaction or some other may be even unpredictable error during the script execution, the connection is blocked and can not be re-used.
Also it could happen that all of the available connections of the database are used, but there is another child process of the apache server trying to access the database.
This process is blocked for the time being until a connection is freed by the database or apache (timeout or voluntarily by termination).
Any further information about this topic on this page: http://www.php.net/manual/en/features.persistent-connections.php

I hope I got all that we have discussed in our comment conversation summarized correctly and did not forget anything.
If so, please, leave me a hint and I will add it. :)

Edit:

I just finished reading the article @MonkeyZeus mentioned in this comment.
It describes the process I summarized above and provides useful information on how to optimize your apache server to work better together with persistent connections.
It can be used with or without oracle database backends, though.
You should give a look: http://www.oracle.com/technetwork/articles/coggeshall-persist-084844.html

PDO persistent connections cache requirement

A persistent connection is, by definition, one that is not closed (i.e. it persists); the benefit is that PHP remains connected to the database for other scripts to use (i.e. there is no need for it to teardown and setup new database connections each time a script runs).

It is not necessary to write $dbh = null, as that is effectively implied on termination of the script's execution; you can however explicitly write it if you want to signal that you are finished with the connection earlier than at the end of the script (PHP's garbage collector will then, at some point, free up the resource and return it to the connection pool for other scripts' use).

If you want connections to get closed and destroyed at the end of each script, then don't use persistent connections!

---

Related Topics