MySQL persistent connections in PHP

By:    Updated: September 14,2017

As of PHP 7.1.9, there is not a real sense of database connection pooling in PHP core. In order to work around this problem, PHP mysqli and pdo_mysql extension using persistent connections to reuse database connections.

The meanings and benefits of PHP connection pooling

The mysqli and pdo_mysql extension supports persistent database connections, which are a special kind of pooled connections. By default, every database connection opened by a script is either explicitly closed by the user during runtime or released automatically at the end of the script. A persistent connection is not. Instead it does not close when the execution of the script ends. When a persistent connection is requested, PHP checks if there's already an identical persistent connection (that remained open from earlier) - and if it exists, it uses it. If it does not exist, it creates a new connection. An 'identical' connection is a connection that was opened to the same host, with the same username and the same password (where applicable). Reuse saves connection overhead.

 

Every PHP process is using its own mysqli/pdo_mysql connection pool (This also has some drawbacks mentioned later). Depending on the web server deployment model, a PHP process may serve one or multiple requests. Therefore, a pooled connection may be used by one or more scripts subsequently. The web server may spawn many PHP processes.

The Drawbacks of PHP connection pooling

If that connection overhead is high, persistent connections help us considerably. They cause the child process to simply connect only once for its entire lifespan, instead of every time it processes a page that requires connecting to the MySQL server. This means that for every child that opened a persistent connection will have its own open persistent connection to the server (one from each child). For example, if you had 20 different child processes that ran a script that made a persistent connection to your MySQL server, you'd have 20 different connections to the MySQL server.

 

This can have some drawbacks if we are using a database with connection limits that are exceeded by persistent child connections. If our database has a limit of N simultaneous connections, and in the course of a busy server session, N+1 child threads attempt to connect, one will not be able to. If there are bugs in the scripts which do not allow the connections to shut down (such as infinite loops), the database with only N connections may be rapidly swamped.

 

There are a couple of additional caveats to keep in mind when using persistent connections. One is that when using table locking on a persistent connection, 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 web 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. In either case, their state is not reset before reuse.

 

The mysqli extension supports both interpretations of a persistent connection: state persisted, and state reset before reuse. The default is reset. Before a persistent connection is reused, the mysqli extension implicitly calls mysqli_change_user() to reset the state. The persistent connection appears to the user as if it was just opened. The mysqli_change_user() function is an expensive operation. For best performance, users may want to recompile the extension with the compile flag MYSQLI_NO_CHANGE_USER_ON_PCONNECT being set. You can use register_shutdown_function() to register a simple cleanup function to unlock your tables or roll back your transactions. Better yet, avoid the problem entirely by not using persistent connections in scripts which use table locks or transactions (you can still use them elsewhere).

 

It is left to the user to choose between safe behavior and best performance. Both are valid optimization goals. For ease of use, the safe behavior has been made the default at the expense of maximum performance.

Related Configuration of persistent connections

;Allow or prevent persistent links
mysqli.allow_persistent = On

;The maximum number of MySQL connections per process. -1 means no limit.
mysqli.max_links = -1

;Maximum of persistent connections that can be made. -1 means no limit.
mysqli.max_persistent = -1

Implementation of persistent connections

<?php

//mysqli persistent connections
$instance_mysqli = new mysqli('p:fs_host', 'fs_user', 'fs_password', 'fs_db');

//pdo_mysql persistent connections
$instance_pdo = new PDO('mysql:host=fs_host;dbname=fs_db', $fs_user, $fs_password, [
    PDO::ATTR_PERSISTENT => true
]);

 

More in Development Center
New on Valinv
Sources
  • http://php.net/manual/en/mysqli.persistconns.php
Related Articles
Sponsored Links