As of PHP 5.3 the PHP MySQL extension mysqli (ext/mysqli) supports Persistent Database Connections. Persistent Connections are new to ext/mysqli but not new to ext/mysql or PDO. The news behind the news is the usage of COM_CHANGE_USER (C-API: mysql_change_user()) by ext/mysqli – no side effects any more. The "flagship" has idiot-proof Persistent Connections.

If you do not know what Persistent Database Connections are and you speak german I would like to point you to my guest posting at http://www.phphatesme.com. If you speak english, go for the PHP manual section and glance over this MySQL Newsletter from 2002(!).

What’s different?

Ever since Persistent Connections have been a mixed bag. They can give you a significant performance boost by caching (pooling) connections although MySQL is already comparatively fast at establishing connections. However,connections are stored "as-is" in the cache. They are not "cleaned up". If a connection has uncommitted when it is put into the pool, it will still have an open transaction when the next application fetches it from the pool. As a result the application has to deal with a connection that is not in "clean" state. Even worse, the open transaction blocks server resources. This may not sound too bad, because MySQL should be able to handle open transactions efficiently, but what about, for example, table locks.

These problems are history because ext/mysqli does the clean up, it does:

• rollback active transactions
• close and drop temporary tables
• unlock tables
• reset session variables
• close prepared statements (happens always with PHP)
• close handler
• release locks acquired with GET_LOCK()

As a result a persistent connection returned from a pool is "as new". The mysqli trick is to automatically call the C-API function mysql_change_user() (= COM_CHANGE_USER) for you.

This is good and bad at once. It is good for beginners who got confused by side effects in the past. It is good for Joe Dolittle because there is one pitfall less.

The little bad news for power users

It is bad for power users because it is slower. I do know that it is slower but I do not know how much it will impact real life applications. Feedback is appreciated. The picture below shows the impact of COM_CHANGE_USER. For the time being we gave security and comfort precedence. It is a new feature for ext/mysqli. There is no need to behave exactly the same way as in ext/mysql.

Hint for performance experts, who know what they do. You can get the faster but less idiot-proof ext/mysql style persistent connections if you define MYSQLI_NO_CHANGE_USER_ON_PCONNECT when compiling PHP. Have a look at the source of ext/mysqli/mysqli_nonapi.c. It your choice. The masses will use the secure defaults, the experts can get the full speed, if need be. However, please test if and how much it is slower in real life before you take the stony road!