Recently, in a new PHP + MySQL application I'm building, I was faced with a choice of using persistent connections. The benefit of persistent connections is that you don't have to keep opening new connections every time you need to hit the database for something. There's a connection already waiting for you. Yay, right? Well, with MySQL, connecting is actually really really cheap, and frankly, if you are using persistent connections, you might encounter some issues with Apache going zombie on processes that use a connection, effectively taking that connection out of use. Grrr.
"To Google!", I said. "The Interwebs must have a solution for this!" I've searched and searched trying to find a definite answer about whether to use or not to use persistent connections in a LAMP application. There seem to be lots of differing opinions, namely this famous presentation by Rasmus which basically "proves" that persistent connections are awesome. However, the PHP community seemed to be pretty against them, despite that evidence.
At this point, it was just annoying. Do I use persistent connections or not? Having this conversation internally with Ben Ramsey and Robert Swarthout, it seemed a good idea to have some hard numbers to back up our claims, so we wouldn't have to say "well, guy x said so" as part of the argument.
Here are the results of Robert's tests: Benchmarking of MySQL Persistent Connections vs Non-Persistent Connections. As Robert says:
Basically what the numbers above shows us is that in an isolated environment it makes no difference which connection type you are going to use.
Jay Pipes says that "if you use Apache, Apache can zombie a PHP process and cause the mysql connection to be held until the mysql server restarts..." Given the risk, the possible management overhead and the results of the test, we're not going to use persistent connections on this upcoming project.
Comments (4)
My experience with persistent connections is from years ago (circa 2001) but the problem we found was that on a very busy server the Apache processes would get so numerous that they would suck up all of the available MySQL connections and then the next Apache process was screwed because it's connection would then get refused.
This was mostly because Apache was used to serve all static content, so a process serving a static image would still have it's own persistent connection.
Posted by Richard Harrison | August 21, 2008 4:58 PM
Posted on August 21, 2008 16:58
Good run-down of the issues and documentation available. I've been teaching "don't bother with persistent connections" for years, but haven't had a good source. Now I do. Thanks!
Posted by Carsten Pedersen | August 22, 2008 3:19 AM
Posted on August 22, 2008 03:19
Maggie,
Don't forget that with persistent connections, MySQL won't know to drop temporary tables if you use them in the connection. It's bit me in the past. Good luck making your decision!
Daniel
Posted by Daniel Smith | August 22, 2008 3:30 AM
Posted on August 22, 2008 03:30
Actually, I'd say it depends on the use case. We're running some ad delivery software which does about 2k inserts per second at peak times. We have been running this without persistent connections for a long time under Apache. We had long learned that enabling persistent connections under Apache is a no-go.
However when we made the switch from Apache to lighttpd several years ago, we got loads more control of the number of running PHP processes. Previously we were running hundreds of Apache processes on one single webserver, which got replaced by one single lighttpd process and a fixed amount of 20 PHP FastCGI processes.
Recently we decided to evaluate persistent connections again because we realized that the math had became easy: servers * 20 = max_connections. The number of connections to our MySQL server is now constant and the system load has gone down by about 20%.
Posted by M. van der Klip | August 22, 2008 5:35 AM
Posted on August 22, 2008 05:35