Are PHP persistent connections evil ?
As you probably know PHP “mysql” extension supported persistent connections but they were disabled in new “mysqli” extension, which is probably one of the reasons some people delay migration to this extension.
The reason behind using persistent connections is of course reducing number of connects which are rather expensive, even though they are much faster with MySQL than with most other databases.
Not only connects are expensive but you also may run into the trouble establishing number of connections you need. The problem is there can be only so many connections active between Host “Apache” and Host “MySQL”: Port 3306 as connection in TCP/IP protocol is identified by pair of IP addresses and pair of ports (local port and remote port). Yes if you’re establishing thousands of connections per second you normally do not keep it open for long time, but Operation System does. According to TCP/IP protocol Ports can’t be recycled instantly and have to spend some time in “FIN” stage waiting before they can be recycled.
On Linux you can adjust “/proc/sys/net/ipv4/ip_local_port_range” to get more local ports available and “/proc/sys/net/ipv4/tcp_fin_timeout” to reduce recycle delay. Reducing last one however will go against protocol requirements so in theory you can get some problems. It worked fine for me however.
Other ways to workaround this problem is of course to use multiple IPs on your MySQL server (you’re probably using Intranet range IPs anyway). So one way or around you can avoid such limit but creating connections will still waste resources and add latency.
The other problem with persistent connections is using too many MySQL server connections. Some people simply do not realize you can increase max_connections variable and get over 100 concurrent connections with MySQL others were beaten by older Linux problems of not being able to have more than 1024 connections with MySQL.
With modern systems you can have thousands of Connections with MySQL, it however might not be overly efficient - managing large number of threads may be a bit more expensive. Threads also take memory resources both on kernel and MySQL size but most problems seems to happen in case of “overload”. In case your number of connections is limited you start getting connection errors which are easy to handle, in case of large number of connections allowed you may have 4000 of queries running at the same time which may never self-resolve as users will get extremely poor response time and will continue press reload. Plus, especially for Innodb tables throughput may drop dramatically - you may see it to be 1/100 of what you get with just few queries concurrently.
If you have these queries doing large sorts, using temporary tables or having other significant memory requirements you may well out of memory and get crash or have MySQL starting to swap aggressively.
This is where connection pooling would be extremely helpful but with default Processed based PHP installations it does not work.
Interesting enough these problem of thousands of connections in most cases comes from misconfiguration or possibly not willing to spend enough time to optimize Web part of configuration. Thousands of concurrent connections usually result from cases when there are many hundreds of apache children processes are running. Most of them will be just holding keep-alive while still keeping MySQL connection open, others will serve static content such as images which also does not need MySQL connection open.
In optimal configuration with Apache talking to local MySQL installation hand having no remote network accesses would be 20-30 apache children. But you need to keep them busy all the time so they should not handle keep alive serve images or perform spoon feeding. You can place squid in front, use apache proxy module or even use lighttpd with FastCGI all can fix this problem.
Lets talk now about why Persistent connections were disabled in mysqli extension. Even though you could misuse persistent connections and get poor performance that was not the reason. The real reason is - you could get much more problems with it.
Persistent connections were added to PHP during times of MySQL 3.22/3.23 when MySQL was simple enough so you could recycle connections easily without any problems. In later versions number of problems however arose - If you recycle connection which has uncommitted transactions you run into trouble. If you happen to recycle connections with custom character set settings you’re in trouble back again, not to mention about possibly changed per session variables.
Many applications are fine - ie if you have read-only page and everything on your site uses same charset and does not use per session variables in some tricky way it would work perfect for you. In some complex applications it however can lead to very hard to track bugs.
What would be proper solution to this ?
MySQL should support command which resets connection and guarantees it will be the same as freshly created connection, so the fact connection is persistent can be completely transparent. May be it even should get fresh connection ID so if you track web requests in MySQL Logs by connection ID it would still work. MySQL has change_user() call which kind of does some of this but it looks like no one knows if it does everything what needs to be done, plus it surely was broken in some MySQL versions.
So persistent connections are not evil but they need some MySQL support to be properly implemented. Now with new “mysqlnd” driver for PHP effort by MySQL team I hope this issue could be fixed. Internal guys sure have more leverage on server development team than external community 
Print This Post
del.icio.us :: 
digg Comment RSS :: Trackback URI
22 Comments
The other issue with using persistent connections is that they are simply not needed in a well designed web based application. Not every web request on a web site will (if properly designed) need a mysql connection. Most likely, if you are requiring a connection on every Apahce request, your application will not scale and will start having issues very quickly.
Another issue that arrises is that a user will install several “off the shelf” applications, put them all in different databases, and then use persistent connections. Now, the blog, the user counter, the ad program and the forum all make their own connection. So, each Apache process has 4 persistent connections to the database. Solve that user error issue (and saying that dumb users should fix their stuff is not good enough) and maybe persistent connections are valid again.
Comment :: November 12, 2006 @ 2:35 pm
Brian is working on a patch to allow 7000+ connections with only 700 allowed to be active at any given time. With that it makes pconnect a little less evil.
Comment :: November 12, 2006 @ 5:53 pm
Brian,
I can’t agree with you on this. Some applications do not need persistent connections other will benefit from them quite seriously. Now if web page hit does not require connection you do not have to establish it so with proper design overhead will be low.
Now if you’re speaking about having multiple offshelf applications sharing same MySQL Server you’re quite likely speaking about light duty applications this is not something persistent connections are designed for.
I would agree persistent connections are often misused. Unless you have hundreds of connections per second you quite likely do not need persistent connections and unlikely will get major benefit from using them. On the contrary however many people used to always use persistent connections without thinking what they really do.
It is kind of similar as with Indexes you can find some people putting random indexes on all columns without giving thought what these
Comment :: November 12, 2006 @ 6:57 pm
Datan,
That would be interesting. Are you speaking about worker thread model or something else ?
Also Vadim actually had prototype patch for limiting number number of active thread over a year ago but at that time it did not get much of interest within MySQL development team.
Comment :: November 12, 2006 @ 6:58 pm
[...] some time in “FIN” stage waiting before they can be recycled. No Comments Leave a Commenttrackback addressThere was an error with your comment, please try again. name (required)email (will not be published)(required)url [...]
Pingback :: November 12, 2006 @ 8:11 pm
Did anyone tested persistent connection to SQL Relay (http://sqlrelay.sourceforge.net/) which in turns connects to a bunch of mysql server?
Comment :: November 13, 2006 @ 2:32 am
MySQL Performance Blog: Are PHP persistent connections evil ?…
…
Trackback :: November 14, 2006 @ 6:23 am
[...] At the MySQL Performance Blog there is a very interesting article about PHP’s persistent MySQL connections and why there are not enabled by default in the mysqli extension. [...]
Pingback :: November 14, 2006 @ 12:53 pm
I have not seen sqlrelay used much in practice. Myself I do not think such sort of mediator is good idea - it adds up complication and adds latency. It may be good in some extreme cases when you badly need connection to be persistent but you can’t have so many connections to MySQL.
Would be interesting however to see some real benchmarks with it.
Comment :: November 14, 2006 @ 7:36 pm
We’re using SQL Relay in a production environment to pool connections between 12 Apache Web servers and a handful of Oracle databases. Persistent connections are certainly evil once you have enough Web servers maintaining X number of persistent connections per Web server to your database(s). Pretty soon, you’ve got thousands of connections to the database, most of which aren’t even in use concurrently, and then the database starts to run out of memory. SQL Relay takes on this burden, freeing the database to focus on running the queries instead of managing a lot of connections. In practice, yes, this middle man does increase overall complexity and you have to think about redundancy too, but if you have a busy site you probably won’t have much choice after a certain point!
The nice thing about persistent connections is that they reduce latency on the connect/disconnect/reconnect which, for Oracle, is more expensive than with MySQL. The good news is that SQL Relay’s performance here is comparable to PHP’s persistent connections. The reason is because SQL Relay has already established X number of Oracle (or MySQL) connections and strives to maintain that number continually. Then, your PHP app connects and disconnects from the SQL Relay server, which is lightweight.
Getting everything to run smoothly does take a fair bit of tuning and careful management of your SQL Relay connections. If you have leaky code that doesn’t disconnect properly, you can still flood your connection pool, no pun intended. Wish I had benchmarks but, the main thing is that SQL Relay offers similar performance and won’t flood your database with connections, both of which are good things.
Comment :: January 17, 2007 @ 1:12 pm
For an advertisement serving engine with a real/serious degree of concurrency and fairly simple queries, support for persistent connections has been very useful for us. It looks like there are ways to implement connection pooling as I just checked the PHP documentation on MySQLi extension.
Comment :: February 13, 2007 @ 12:03 am
Sure. if queries are simple persistent connection are helpful. It is good mysqlnd driver which is being developed for PHP 6 (and lower in the future) will support them.
Comment :: February 14, 2007 @ 10:22 am
[...] seconds) or increasing the number of allowed connections didn’t ring true. A post on the MySQL Performance Blog pointed me in the right direction. Apparently, persistent connections do not currently play nice [...]
Pingback :: May 15, 2007 @ 5:05 am
[...] http://www.mysqlperformanceblog.com/2006/11/12/are-php-persistent-connections-evil/ [...]
Pingback :: July 29, 2007 @ 1:02 pm
[...] will have again the possibility to use persistent connection (mysqli_connect(’p:localhost’, …);). This is an interesting post about this [...]
Pingback :: November 19, 2007 @ 7:19 am
[...] will have again the possibility to use persistent connection (mysqli_connect(’p:localhost’, …);). This is an interesting post about this [...]
Pingback :: November 19, 2007 @ 3:55 pm
[...] mysqli pour des problèmes de performances et de stabilité (voir ce post très intéressant : http://www.mysqlperformanceblog.com/2006/11/12/are-php-persistent-connections-evil/). Apparemment, ces problèmes ont été résolus avec mysqlnd puisque cette fonctionnalité [...]
Pingback :: December 4, 2007 @ 10:44 am
[...] que ver con el hecho de porque no se deben utilizar conexiones persistentes a la base de datos. http://www.mysqlperformanceblog.com/…nections-evil/ Que es algo que tenamos activado en CemZoo, porque yo que vengo de un mundo de programacin [...]
Pingback :: December 6, 2007 @ 9:28 pm
[...] mysqli pour des problèmes de performances et de stabilité (voir ce post très intéressant : http://www.mysqlperformanceblog.com/2006/11/12/are-php-persistent-connections-evil/). Apparemment, ces problèmes ont été résolus avec mysqlnd puisque cette fonctionnalité [...]
Pingback :: January 12, 2008 @ 5:43 am
[...] que conexão persistente com o banco de dados é cafonérrimoooo, para saber, leia este artigo: Are PHP persistent connections evil?. O pessoal do suporte técnico do Onda me indicou o artigo (adoro quando provam que eu estou [...]
Pingback :: March 15, 2008 @ 4:45 pm
how to increasr max connection limit in mysql i am getting error too many connection as i am using Shared hosting
,. how to change the setting in Cpanel
Comment :: March 27, 2008 @ 1:24 pm
[...] http://www.mysqlperformanceblog.com/2006/11/12/are-php-persistent-connections-evil/ [...]
Pingback :: May 13, 2008 @ 1:46 am