Questions & Answers

What are the disadvantages of using persistent connection in PDO

In PDO, a connection can be made persistent using the PDO::ATTR_PERSISTENT attribute. According to the php manual -

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. The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application.

The manual also recommends not to use persistent connection while using PDO ODBC driver, because it may hamper the ODBC Connection Pooling process.

So apparently there seems to be no drawbacks of using persistent connection in PDO, except in the last case. However., I would like to know if there is any other disadvantages of using this mechanism, i.e., a situation where this mechanism results in performance degradation or something like that.

Answers(8) :

seems to me having a persistent connection would eat up more system resources. Maybe a trivial amount, but still...

2023-01-19 23:01:03
Often a trade of lots of human time for microseconds of computer time

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.

2023-01-19 23:01:03
I hope I had read this answer before running SELECT orders.* FROM orders LEFT JOIN items USING(item_id)
2023-01-19 23:01:03
I know a big website that has been using persistent connections for nearly a decade now. The trick is using a layer above the DB extension, and having it remember the things that need to be cleaned up by using register_shutdown_function(). If the process dies, the connection dies too. If it doesn't, the connection is reset to its clean state (e.g., open transactions are rolled back). If this fails, the connection is closed and a new one will be opened by the next request to the same process. There is no need to demonize persistent connections.
2023-01-19 23:01:04
@MichaelDibbets We replaced the application server a few months ago, and turned of pconnect to see if the three second bug was still around. It wasn't. It's been resolved by proxy, I suppose. The answer below with regard to mysqli_change_user is still probably the best workaround for people that have to do persistent connections in an application not designed to deal with state problems.
2023-01-19 23:01:04
Thats a long lifetime for a server 0_o. Good the issue was resolved though. Goes to show that even the best setups can fail through a broken library or obscure setting
2023-01-19 23:01:04
@Charles, When you say "outsourced IT didn't have the concrete ability to track it down", which company is that? You should have tried Percona, I don't think there are any MySQL issues they couldn't solve.
2023-01-19 23:01:04
@Pacerier, we'd outsourced all of our IT at the time to a local company. Good, smart people just management wasn't willing to pay for enough hours for them to get the job done. We don't believe it was a MySQL issue, but a lower level networking / Linux issue. Since the time this was written, we've switched hardware and OS a few times and the three second bug no longer occurs.
2023-01-19 23:01:04
@Charles, When you say "management wasn't willing to pay for enough hours for them to get the job done", do you mean that there is no work done, or do you mean that you guys have actually paid up but with those paid time the problem cannot be solved?
2023-01-19 23:01:04
@Pacerier, I'm saying that management elected to not pay them to investigate the problem deeply, given that we had a workaround in the form of pconnect.
2023-01-19 23:01:04
@Charles, That's a bad way of doing things. Something else totally unrelated might be horribly wrong somehow somewhere and they gave up their chance to find and fix it.
2023-01-19 23:01:04
@Pacerier, preaching to choir. This occurred during the US financial crisis in 2008 (seven years ago now), and the company was on the verge of going under, so there was little other choice than to not spend money. Thankfully, none of the problems that existed then, financial and technical, are still problems today.
2023-01-19 23:01:04
We had a 5 second delay on connect, which we managed to isolate as a DNS + IPv6 problem. The server was looking for an v6 address, failing, and then using the IPv4 address.
2023-01-19 23:01:04
Out of pure curiosity, what are some practical examples of a script terminating unexpectedly?
2023-01-19 23:01:04
Now this answer was posted 10 years ago, any update on persistent connection? It is still unstable?
2023-01-19 23:01:04
@NgSekLong "Unstable" isn't really a good description of the problem. One major behavior difference here in 2021 is that a whole lot of PHP is now run through FPM. The persistent connection would be coming from the PHP instance spawned by FPM. Only one worker would suffer from the broken connection problem. The workarounds for this problem can be simple. For this application, we ended up just doing a ROLLBACK on every "new" connection to close any open transactions. Please also see this answer below for more options.

Persistent connections should give a sizable performance boost. I disagree with the assement that you should "Avoid" persistence..

It sounds like the complaints above are driven by someone using MyIASM tables and hacking in their own versions of transactions by grabbing table locks.. Well of course you're going to deadlock! Use PDO's beginTransaction() and move your tables over to InnoDB..

2023-01-19 23:01:04
A year late, I realize, but for the record: my tale comes from a database composed entirely of InnoDB tables, with the sole exception of a handful of denormalized clones stuck in the quagmire of MyISAM for fulltext indexing support.
2023-01-19 23:01:04
Pfft, Sphinx is old and busted, ElasticSearch is the new hotness. One fine day, we'll actually use it for our old apps instead of just the new ones...
2023-01-19 23:01:04
Fulltext search in PostgreSQL is the real winner. It's amazing. Doesn't require another tool/server running to do its job. Doesn't have to worry about keeping data in sync. Very granular controls. Multiple dictionaries or write your own. And since PostgreSQL automatically uses multi-index queries you can just drop it in with any other query you're running.
2023-01-19 23:01:04
MySQL 5.6 offers fulltext support for InnoDB tables.

In response to Charles' problem above,

From : -

A common complain about persistent connections is that their state is not reset before reuse. For example, open and unfinished transactions are not automatically rolled back. But also, authorization changes which happened in the time between putting the connection into the pool and reusing it are not reflected. This may be seen as an unwanted side-effect. On the contrary, the name persistent may be understood as a promise that the state is persisted.

The mysqli extension supports both interpretations of a persistent connection: state persisted, and state reset before reuse. The default is reset. Before a persistent connection is reused, the mysqli extension implicitly calls mysqli_change_user() to reset the state. The persistent connection appears to the user as if it was just opened. No artifacts from previous usages are visible.

The mysqli_change_user() function is an expensive operation. For best performance, users may want to recompile the extension with the compile flag MYSQLI_NO_CHANGE_USER_ON_PCONNECT being set.

It is left to the user to choose between safe behavior and best performance. Both are valid optimization goals. For ease of use, the safe behavior has been made the default at the expense of maximum performance.

2023-01-19 23:01:04
+1, if not for the fact that we've cleaned up the mess in other ways, I'd love to see if manually calling change_user would have fixed our bizarre unknown-state issues.
2023-01-19 23:01:04
What is the equivalent for PDO Postgres persistent connections? I have similar issues like @Charles had, where after a while users would be getting error like fetch sql - server closed the connection unexpectedly This probably means the server terminated abnormally When running simple SELECT query (not even transactions).
2023-01-19 23:01:04
@Carmageddon, that's more suited to a new question, but the tl;dr is that Postgres doesn't do pconnect and you should be using one of the external connection pools instead.
2023-01-19 23:01:04
@Charles, what do you mean by that? is using the PDO's persistent connection not equivalent to using "external connection pools"? or what did you mean?
2023-01-19 23:01:04
@Carmageddon, what I mean is that the Postgres community settled on connection pooling as a better solution than pconnect. Check out pgbouncer or pgpool-II. I'm not sure that PDO does Postgres pconnect anyway, but I may be totally off my rocker.

The explanation for using persistent connections is obviously reducing quantity of connects that are rather costly, despite the fact that they're considerably faster with MySQL compared to other databases.

The very first trouble with persistent connections...

If you are creating 1000's of connections per second you normally don't ensure that it stays open for very long time, but Operation System does. Based on TCP/IP protocol Ports can’t be recycled instantly and also have to invest a while in “FIN” stage waiting before they may be recycled.

The 2nd problem... using a lot of MySQL server connections.

Many people simply don't realize you are able to increase *max_connections* variable and obtain over 100 concurrent connections with MySQL others were beaten by older Linux problems of the inability to convey more than 1024 connections with MySQL.

Allows talk now about why Persistent connections were disabled in mysqli extension. Despite the fact that you can misuse persistent connections and obtain poor performance which was not the main reason. The actual reason is – you can get a lot more issues with it.

Persistent connections were put into PHP throughout occasions of MySQL 3.22/3.23 when MySQL was not so difficult which means you could recycle connections easily with no problems. In later versions quantity of problems however came about – Should you recycle connection that has uncommitted transactions you take into trouble. If you recycle connections with custom character set configurations you’re in danger again, as well as about possibly transformed per session variables.

One trouble with using persistent connections is it does not really scale that well. For those who have 5000 people connected, you'll need 5000 persistent connections. For away the requirement for persistence, you may have the ability to serve 10000 people with similar quantity of connections because they are in a position to share individuals connections when they are not with them.

Persistent connections are a good idea only when it takes a (relatively) long time to connect to your database. Nowadays that's almost never the case. The biggest drawback to persistent connections is that it limits the number of users you can have browsing your site: if MySQL is configured to only allow 10 concurrent connections at once then when an 11th person tries to browse your site it won't work for them.

PDO does not manage the persistence. The MySQL driver does. It reuses connections when a) they are available and the host/user/password/database match. If any change then it will not reuse a connection. The best case net effect is that these connections you have will be started and stopped so often because you have different users on the site and making them persistent doesn't do any good.

The key thing to understand about persistent connections is that you should NOT use them in most web applications. They sound enticing but they are dangerous and pretty much useless.

I'm sure there are other threads on this but a persistent connection is dangerous because it persists between requests. If, for example, you lock a table during a request and then fail to unlock then that table is going to stay locked indefinitely. Persistent connections are also pretty much useless for 99% of your apps because you have no way of knowing if the same connection will be used between different requests. Each web thread will have it's own set of persistent connections and you have no way of controlling which thread will handle which requests.

The procedural mysql library of PHP, has a feature whereby subsequent calls to mysql_connect will return the same link, rather than open a different connection (As one might expect). This has nothing to do with persistent connections and is specific to the mysql library. PDO does not exhibit such behaviour

Resource Link : link

In General 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, many webservers accessing one database server

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.

2023-01-19 23:01:04
After line your answer is copy paste from
2023-01-19 23:01:04
"YES, use persistent connections, if: [...] There are only few applications/users accessing the database" contradicts with "Only use it for applications with lots of requests.". The latter is however correct. Situation: thousands of requests per second will result in hundreds of active database connections. When a system scales linearly, it will also linearly scale the amount of connections to the database. So more requests (more users) will result in more connections. So you need limited(!) yet many active connections when you have lots of requests(users)

On my tests I had a connection time of over a second to my localhost, thus assuming I should use a persistent connection. Further tests showed it was a problem with 'localhost':

Test results in seconds (measured by php microtime):

  • hosted web: connectDB: 0.0038912296295166
  • localhost: connectDB: 1.0214691162109 (over one second: do not use localhost!)
  • connectDB: 0.00097203254699707

Interestingly: The following code is just as fast as using

$host = gethostbyname('localhost');
// echo "<p>$host</p>";
$db = new PDO("mysql:host=$host;dbname=" . DATABASE . ';charset=utf8', $username, $password,
    array(PDO::ATTR_EMULATE_PREPARES => false,
2023-01-19 23:01:04
Seems like PDO has difficulty on translating domainnames! Thank you, I was wondering why each connection was taking damn long on my quad core machine!
2023-01-19 23:01:04
@Gunnar Bernstein +1 nice find. "localhost" certainly does take longer and this has improved my web app's speed somewhat (it makes lots of connections).
2023-01-19 23:01:04
This is great. Something is wrong with resolution on my development machine... using an IP took my script from 6.1s to 1.1s
2023-01-19 23:01:04
localhost uses socket connection, socket connection is famous to being bad on big amount of connections
2023-01-19 23:01:04
@mente Any reference, resource that can prove that fact? I would tend to think that UDS are prefered over TCP. Thanks.
2023-01-19 23:01:04
unfortunately the only reference I have is my experience. So it's very subjective. Actually googling it revealed that most of the times UDS is faster. Now I'm questioning my experience as well

I was just wondering whether a partial solution would be to have a pool of use-once connections. You could spend time creating a connection pool when the system is at low usage, up to a limit, hand them out and kill them when either they've completed or timed out. In the background you're creating new connections as they're being taken. At worst case this should only be as slow as creating the connection without the pool, assuming that establishing the link is the limiting factor?