peristent PDO mysql connection alternatives

Question

I currently create a DB connection via PDO for my website like so:

try {
    self::$dbh = new PDO("mysql:host={$host};dbname={$dbName}", $dbUser, $dbPass);
    self::$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
} 
catch (PDOException $e) {
    return $e->getMessage();
}

I've been reading about persistent connections so I wanted to add the persistent flag like so:

self::$dbh = new PDO("mysql:host={$host};dbname={$dbName}", $dbUser, $dbPass,
    array(PDO::ATTR_PERSISTENT => true      
));

From what I've been reading this could be dangerous if something happens mid query etc. It sounds like this isn't really a recommended method.

Are there any other alternatives to maintain a persistent DB connection with MySQL?

Answer 1

The reason to use persistent connections is that you have a high number of PHP requests per second, and you absolutely need every last fraction of a percent of performance.

Even though creating a new MySQL connection is really pretty inexpensive (compared to connecting to Oracle or something), you may be trying to cut down this overhead. Keep in mind, though, that most sites get along just fine without doing this. It depends on how heavy your traffic is. Also, MySQL 5.6 and 5.7 have made it even more efficient to create a new connection, so the overhead is lower already if you upgrade.

The risk described in the post you linked to was that session-specific states didn't get cleaned up as a given DB connection was inherited by a subsequent PHP request.

Examples of session state include:

• Unfinished transactions
• Temporary tables
• User variables
• Connection character set

This can even be a security problem, for instance if one PHP user populates a temp table with privileged information, and then another PHP user finds they can read it.

Fortunately, in the 4 years since @Charles gave his answer, the mysqlnd driver has addressed this. It now uses mysql_change_user(), which is like a "soft disconnect" that resets all those session state details, but without releasing the socket. So you can get the benefit of persistent connections without risking leaking session state from one PHP request to another. See http://www.php.net/manual/en/mysqlnd.persist.php

This needs the mysqlnd driver to be enabled, which it should be if you use any reasonably up to date version of PHP.

Answer 2

Why do you need a persistent connection? PHP is stateless and reinitializes every time you make a request, so there is mostly no advantages and quite more disadvantages (i.e. sudden disconnectons with no handlers) in working with a persistent connections.