Why persistent connections are bad

Why persistent connections are bad

PHP persistent connections are bad because...

1. they cause transactions, table locks, temporary tables, session variables and most other useful features in MySQL to be very dangerous, potentially causing server-wide deadlocks and database errors during page generation.
2. they occupy hundreds of MySQL sockets and threads, increasing the risk of hitting a limit somewhere (open files, mysql settings, kernel limits?).
3. When something break because of persistent connections, it's certain to be extremely difficult to diagnose, since it will only show up after a certain thread has served certain requests in a certain order.
4. with a pool of web servers, one slow web server can back up and consume so many connections it can't use that the other servers can't create connections to complete their requests.

Because of the above reasons, persistent connections prevent us from implementing important and useful features.

The only disadvantage of disabling persistent connections, is a slight performance hit. As shown below, the impact is less than 1 ms of CPU time per request. Since a typical request takes one or two orders of magnitude more than that, it should not be significant. Currently we run about 25 requests/s on average. A pessimistic calculation, where connection times increase from 0 to 1 ms, would result in an overall performance hit of ~2.5%. All benchmarks were made on a much less impressive computer than any of the wp servers.

Those 2.5% has to be weighed against possible optimisations that can be accomplished with the more advanced features of mysql.

Quote from the php manual [1]:

if the script for whatever reason cannot release the lock, then subsequent scripts using the same connection will block indefinitely and may require that you either restart the httpd server or the database server. Another is that when using transactions, a transaction block will also carry over to the next script which uses that connection if script execution ends before the transaction block does

Benchmarks

The purpose of these benchmarks is to show that non-persistent connections cause an insignificant performance overhead. Note: on connections with latency, the delay may be higher due to TCP handshake and acceleration, but this will not affect overall system performance since the connection setup time simply will be used serving other requests. The delay for the individual request will still be insignificant.

Non-persistent connections with query

<?
       for($i = 0; $i < 10000; $i++){
               $dbc = mysql_connect( "localhost", "root" );
               mysql_select_db( "wikidb", $dbc );
               $res = mysql_unbuffered_query("SELECT 1");
               while( $row = mysql_fetch_object( $res ) );
               mysql_close( $dbc );
       }
?>

Timings

real    0m11.262s
user    0m3.180s
sys     0m1.330s

(About 1 ms per connection)

Non-persistent connections without query

<?
       for($i = 0; $i < 10000; $i++){
               $dbc = mysql_connect( "localhost", "root" );
               mysql_select_db( "wikidb", $dbc );
               mysql_close( $dbc );
       }
?>

Timings

real    0m8.718s
user    0m2.000s
sys     0m1.080s

(About 1 ms per connection)

Persistent connections without query

<?
       for($i = 0; $i < 10000; $i++){
               $dbc = mysql_pconnect( "localhost", "root" );
               mysql_select_db( "wikidb", $dbc );
       }
?>

Timings

real    0m0.973s
user    0m0.350s
sys     0m0.090s

(About 0.1 ms per connection)