To persist or not to persist?
by maggie on Aug.21, 2008, under entry
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.
-
-->
Recently Linked From- Kühle URLs November 19, 2009Nochmal alle zusammen, und drei, vier: "Cool URLs don't change!" (genauer: Cool URIs don't change), wie schon der Erfinder des Internet, Al Gore Tim Berners-Lee forderte, der damit erreichen will, dass Inhalte immer im Netz auffindbar ... […]Konstantin Klein
- ORM в мире PHP October 21, 2009ORM (англ. Object-relational mapping, русск. Объектно-реляционная проекция) — запись объектов программы в реляционную базу данных, отображение объекта и его представления в виде набора таблиц. – Википедия: ORM ... […]ingvar
- “Micro” Optimizations That Matter October 16, 2009Every few weeks, someone publishes an article talking about how it's faster to use single quotes rather than double quotes and how you should use echo() instead of print(). Most of these are bunk; that is, the time we spend talking ... […]Brandon Savage
- Tour du mois en PHP : mars 2009 April 20, 2009Comme tous les mois, en voici un nouveau qui s'est achevé — avec sa quantité d'articles et d'événements intéressants ! Voici ma compilation de liens m'ayant le plus marqués pour le mois de mars 2009. ... […]Pascal MARTIN
- stop sacrificing readability for efficiency! March 26, 2009much was made last week over the topic of micro optimization in php. most of these argued that micro optimization was a bad idea, from the perspective of unit testing, the idea that “premature optimization is the root of all evil”, ... […]Brandon Savage
- Kühle URLs November 19, 2009
August 21st, 2008 on 4:58 pm
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.
August 22nd, 2008 on 3:19 am
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!
August 22nd, 2008 on 3:30 am
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
August 22nd, 2008 on 5:35 am
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%.