The mysqli Extension and Persistent Connections
The idea behind persistent connections is that a connection between a client process and a database can be reused by a client process, rather than being created and destroyed multiple times. This reduces the overhead of creating fresh connections every time one is required, as unused connections are cached and ready to be reused.
Unlike the mysql extension, mysqli does not provide a separate function
for opening persistent connections. To open a persistent connection you
must prepend p:
to the hostname when connecting.
The problem with persistent connections is that they can be left in
unpredictable states by clients. For example, a table lock might be
activated before a client terminates unexpectedly. A new client
process reusing this persistent connection will get the connection
as is
. Any cleanup would need to be done by the new
client process before it could make good use of the persistent
connection, increasing the burden on the programmer.
The persistent connection of the mysqli
extension
however provides built-in cleanup handling code. The cleanup carried
out by mysqli
includes:
-
Rollback active transactions
-
Close and drop temporary tables
-
Unlock tables
-
Reset session variables
-
Close prepared statements (always happens with PHP)
-
Close handler
-
Release locks acquired with GET_LOCK()
This ensures that persistent connections are in a clean state on return from the connection pool, before the client process uses them.
The mysqli
extension does this cleanup by
automatically calling the C-API function
mysql_change_user()
.
The automatic cleanup feature has advantages and disadvantages though. The advantage is that the programmer no longer needs to worry about adding cleanup code, as it is called automatically. However, the disadvantage is that the code could potentially be a little slower, as the code to perform the cleanup needs to run each time a connection is returned from the connection pool.
It is possible to switch off the automatic cleanup code, by compiling
PHP with
MYSQLI_NO_CHANGE_USER_ON_PCONNECT
defined.
Note:
The
mysqli
extension supports persistent connections when using either MySQL Native Driver or MySQL Client Library.