Persistent DB Connections - Yea or Nay?

Brian_Warshaw_1344 0
Viewed 38981

I'm using PHP's PDO layer for data access in a project, and I've been reading up on it and seeing that it has good innate support for persistent DB connections. I'm wondering when/if I should use them. Would I see performance benefits in a CRUD-heavy app? Are there downsides to consider, perhaps related to security?

If it matters to you, I'm using MySQL 5.x.

pdo database-connection persistence
7 Answers

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.

BlaM_9990

Creating connections to the database is a fairly expensive operation. Persistent connections are a good idea. In the ASP.Net and Java world, we have "connection pooling", which is roughly the same thing, and also a good idea.

Josh_Hinman_25270

IMO, The real answer to this question is whatever works best for you app. I would recommend you benchmark your app using both persistent and non-persistent connections.

Maggie Nelson @ Objectively Oriented posted about this in August and Robert Swarthout made an accompanying post with some hard numbers. Both are pretty good reads.

Mike_H_45630

In my humble opinion:

When using PHP for web development, most of your connection will only "live" for the life of the page executing. A persistant connection is going to cost you a lot of overhead as you'll have to put it in the session or some such thing.

99% of the time a single non-persistant connection that dies at the end of the page execution will work just fine.

The other 1% of the time, you probably should not be using PHP for the app, and there is no perfect solution for you.

Markus_24900

In general, you'll need to use non-persistent connections sometimes, and it's nice to have a single pattern to apply to db connection design (as long as there's relatively little upside to using persistent connections in your context.)

doofledorfer_316410

I was going to ask this same question but rather than ask the same question again I'll just add some information that I've found.

It is also worth noting that the newer mysqli extension does not even include the option to use persistent database connections.

I'm still using persitent connections at the moment but plan to switch to non-persistent in the near future.

user_7960
Related