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!
2009/02/19 at 10:42
The problem with current implementation is that mysql_change_user() is called in mysqli_common_connect(). So the tables remains locked, transactions open, etc. until the same process tries to connect again. I think it should be called in mysqli_link_free_storage().
2009/02/19 at 12:39
… we need something better than COM_CHANGE_USER. We need a variety of COM_RESET_XYZ commands. You want to be able to define a) what gets resets and b) when things get reset. For example, you probably want to free locks when you put the connection back into the pool. But you may not care about rolling back transactions at all because you know that your application does not use them as it is using MyISAM tables only.
The implementation is yet another compromise. I believe it is the best we have to offer at the moment. At least you, as a power user, can decide at compile time if you want COM_CHANGE_USER or not (-DMYSQLI_NO_CHANGE_USER_ON_PCONNECT).
2009/02/19 at 12:44
And, BTW, if you have only COM_CHANGE_USER and you do the unlock when putting the connection into the pool, what about checking the permissions. Do you want to do that when you add the connection to the pool or when you fetch it from the pool … the current way is the safe way of using COM_CHANGE_USER.
2009/02/19 at 14:30
Jakub, Andrey hears you. Its not said that the current implementation is the one and only possible. We are seeking for more feedback on what to do.
2009/02/20 at 15:06
Even if the idea of persistent connections sound really good I’m still undecided whether this feature should be used in shared hosting environments. The costs of caching/keeping connections open could be higher than reconnect them on every request.
2009/02/20 at 15:31
Absolutely. Persistent Connections as such are nothing new. One of the traditional arguments against using them is that a misconfiguration of the connection pool can do more harm than good. Like to many optimizations its not an optimization that is good for everybody.
2009/02/23 at 10:44
The persistent connections should be as transparent as possible. If unlock, rollback and friends are performed in script shutdown without the persistent connections then it should be performed in the same phase with them.
2009/02/23 at 11:10
Jakub, it’s clear that if COM_CHANGE_USER is issued on re-use till then lock are held, transactions run. It is possible to move COM_CHANGE_USER to link_free_storage and thus re-init the connection. One may think that we need to do double job because we need to authenticate the user again, with COM_CHANGE_USER, on re-use (connections are held hashed on host+port+db). However, there is something very specific in the C/S protocol. During the initial handshake a salt buffer is sent to the client, to be used during password hashing. This very same buffer is re-used for COM_CHANGE_USER till the connection is shut-down. Thus, the client can do local authentication without a trip to the server, but with all these optimisations, connections which are established will continue to hold rights which might have been revoked, similar to the normal connections.