56

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.

Brian Warshaw
  • 22,657
  • 9
  • 53
  • 72

7 Answers7

74

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
  • 28,465
  • 32
  • 91
  • 105
  • 2
    Also, don't use persistent connections if you're using Apache in prefork mode. It uses one connection for each child process, which can ramp up fairly quickly. – Powerlord Nov 10 '08 at 19:28
  • @BlaM, I'm not getting your last paragraph. Doesn't persistent connections allow you to reuse them (that's the whole point?) so you don't need 2000 connections since they will be reused? Or do you mean **each client** has a unique username? Even so, wouldn't the old persistent connections automatically close when they are done to make way for new connections? – Pacerier Jun 25 '15 at 07:01
  • @Pacerier: I meant clients each with its own user name. In that case old connections will **NOT** close to make new connections possible, because the **limit** of allowed connections is managed by MySQL and the persistence of connections is managed from the PHP side - so from PHPs end of view the database just does not allow to connect. It does not know that the limit was reached and that closing old connections will help. – BlaM Jun 26 '15 at 07:22
  • @BlaM, Hmm, are you saying that the default C libmysql also has this problem? As for PHP, this seems to be a bug that needs to be fixed on the PHP side then. The PHP side should query the max from the server and release it's LRU if the number it has reaches max. – Pacerier Jun 26 '15 at 22:58
  • I never used the C mysql library, so I don't know. However I don't think clients (PHP or C) can do a lot about this fully automated. I'm not sure if there is a PHP.INI setting to allow manual configuration, but fully automated solutions would probably start to act unpredictable as soon as the situation gets more complicated (e.g. more than just one client/webserver accessing one database server). – BlaM Jun 28 '15 at 09:14
  • "because there are 200 different users shared on the same host" I am confused by this. What if my app is the only one running on the server using the same username and password. How will persistent connections work then? – frostymarvelous Oct 26 '15 at 15:38
  • Still depends on the server configuration (Apache? MySQL?). If there is just one database user account for all scripts feel free to give persistent connections a try. As long as "max_connections" (my.cnf) is bigger than "MaxClients" (Apache config) you should be fine. On the other hand: As long as both processes run on the same machine (no actual network overhead) you will probably not notice a lot of performance improvement. – BlaM Oct 27 '15 at 16:39
  • Hi @BlaM - "Ergo: Only use it for applications with lots of requests." I did not get this. You said that if there are 1000 users trying to access application via persistent connection then it will open 1000 links which will be idle. So do you mean we should use persistent connection when we expect a lot of users or not? – Ravi Maniyar Apr 05 '18 at 17:14
  • @RaviManiyar: Yes, persistent connections only make sense when connections can and will be reused, which only happens if there are many users. Maybe think of it like this: If you have 10 requests per day and each could win a theoretical 0.1s per connection established you only gained 1 second. If there are 10000 requests you end up at 1000 seconds won. Only one of those outweighs the loss of blocked parallel connections. – BlaM Apr 06 '18 at 18:56
  • What if I have thousands of users generating hundreds of queries per second resulting in 400 to 500 connections at any given time, sometimes peaking up to 1000 connections. Is this a bad scenario, or is this just fine as long as my machine has the resources to handle it? – Vincent Aug 31 '21 at 16:51
  • @Vincent: You need to differentiate between "Website Users" and "MySQL Users". If there is just a few mysql users and a lot of website users then persistent connections could be a viable option. If there are thousands of users in your mysql.users table then there is no generic answer that can be given and you will have to benchmark yourself. – BlaM Sep 09 '21 at 14:31
  • In my case they're all website users, so thank you for your response. – Vincent Sep 09 '21 at 17:57
14

In brief, my experience says that persistent connections should be avoided as far as possible.

Note that mysql_close is a no-operation (no-op) for connections that are created using mysql_pconnect. This means persistent connection cannot be closed by client at will. Such connection will be closed by mysqldb server when no activity occurs on the connection for duration more than wait_timeout. If wait_timeout is large value (say 30 min) then mysql db server can easily reach max_connections limit. In such case, mysql db will not accept any future connection request. This is when your pager starts beeping.

In order to avoid reaching max_connections limit, use of Persistent connection need careful balancing of following variables...

  1. Number of apache processes on one host
  2. Total number of hosts running apache
  3. wait_timout variable in mysql db server
  4. max_connections variable in mysql db server
  5. Number of requests served by one apache process before it is re-spawned

So, pl use persistent connection after enough deliberation. You may not want to invite complex runtime issues for a small gain that you get from persistent connection.

Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
LionHeart
  • 781
  • 1
  • 7
  • 24
  • 2
    Citation needed for "mysql_close is a no-operation (no-op) for connections that are created using mysql_pconnect". For mysqli, it's certainly not a no-op: http://php.net/manual/en/mysqli.persistconns.php – Pacerier Jun 25 '15 at 06:58
4

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
  • 6,745
  • 7
  • 38
  • 47
3

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
  • 369
  • 2
  • 11
  • 2
    "Objectively Oriented" link is down. – Pacerier Jun 25 '15 at 07:23
  • 1
    I dislike this answer because benchmarking is often done in a very inaccurate way. It could look very good with your computer accessing it in "other than real-world access patterns" (which is typical of tests). Timing is hugely important as are several other "hard to replicate" conditions that occur often in real life. I'd suggest thinking it through and avoiding it unless you're definitely going to need it. Pre-optimization is the first sin. – Joe Love Jun 15 '16 at 16:16
1

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.)

dkretz
  • 37,399
  • 13
  • 80
  • 138
1

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
  • 1,539
  • 1
  • 18
  • 22
0

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.

ejunker
  • 10,816
  • 11
  • 41
  • 41