Ulf Wendel

New plugin: Connection multiplexing with PHP and MySQL

Here comes the next open source mysqlnd plugin prototype: PECL/mysqlnd_mux. The PHP mysqlnd connection multiplexing plugin shares a connection to MySQL between multiple user connection handles. Connection multiplexing aims to reduce connect time and lowers the total number of concurrenly opened connections. The first is primarily a benefit for the client, the latter is great for the server. Multiplexing may be most desired in environments with many short-lived connections and weak client-side pooling – such as a PHP web application.

How mysqlnd plugins work in general

The mysqlnd library is part of PHP as of 5.3. Since PHP 5.4 it is the default C client library for all three PHP MySQL APIs (mysqli, PDO_MySQL, mysql).

WordPress, Drupal, Symfony, Zend Framework, Oxid, …
|
mysqli PDO_MySQL mysql
mysqlnd
PECL/mysqlnd_mux
|
MySQL

As a backend C library for the existing APIs, mysqlnd and its plugins can be transparent from a user perspective. They operate inside PHP at a layer beneath the users applications.Thus, the mysqlnd multiplexing plugin can be used with all existing PHP MySQL applications. However, there is a glory detail. The plugin needs the latest version of mysqlnd contained in PHP 5.5.

How the multiplexing is implemented

Whenever a user attempts to open a MySQL connection using any of the PHP MySQL extensions (mysql, mysqli, PDO_MySQL), the corresponding functions of the mysqlnd library are called inside PHP. The plugin hooks those calls. Whenever the user opens a connection, the plugin checks for a cached connection to the requested MySQL server. If none is found, a new connection is opened, cached and returned to the caller.

$handle_1 = mysqli_connect("host", ...)
PECL/mysqlnd_mux
Have cached connection to host?

  1. No
    1. Connect to host
    2. Cache conn_1
    3. Return conn_1

When doing another connect to the same MySQL server as before, no matter what API used, the plugin checks its cache and links the new user handle to the already opened connection. No second connection needs to be established.

$handle_2 = new PDO("mysql:host=host;...", ...)
PECL/mysqlnd_mux
Have cached connection to host?

  1. Yes
    1. Return conn_1

Compared to a traditional connect there is now only one connection opened to MySQL. Two user connection handles share the same underlying connection. Less work for the server.

conn_1
|   |
$handle_1   $handle_2

An optimization for some

Multiplexing is a performance optimization with side-effects. Collisions and wait situations can arise. Concurrent access to the shared connection must be synchronized. Imagine two clients attempt to use the shared connection at the same time. One of the clients will have to wait until the other stopped using the connection before it can execute its query.

$handle_1 Query 1 -> MUX conn_1 Query 1 Query 2 -> MySQL
$handle_2 Query 2 ->
Time ->

Sharing a connection means also sharing its state. A connections state consists of assorted settings, transaction status, SQL session variables, temporary tables and many more. Please, see the replication plugin manual for an in-depth discussion. Shared state is one of the biggest criticism of persistent connections. This does not mean shared state is malicious per-se. It needs to be understood and mastered, that’s it.

Connection multiplexing is at an optimization detail level that requires understanding its properties.

A demo of the plugin API

We are making an initial public release: 1.0.0-prototype. We are not announcing a production-ready stable release. The initial implementation supports buffered queries only. API calls that use buffered queries include mysqli_query(), mysql_query() and all of PDO if PDO::ATTR_EMULATE_PREPARES is set. Good enough for an evaluation of the idea.

The initial release is also great to learn about the mysqlnd C plugin API. There is not too much code. This allows expert users to talk to us at any level they want. If anybody feels for C coding and want to speak to us the international developer language, commit a patch. If you feel the next step should be looking at multiplexing from a users perspective, ask for a feature addition. Andrey understands both languages. Andrey Hristov? Yes, he developed the plugin.

Happy hacking!

@Ulf_Wendel Follow me on Twitter

4 Comments

  1. Ok, i don’t get it from what you wrote -
    the connection that gets shared, is it shared over multiple instances of PHP itself? Meaning if i have for example 10 php-fcgi processes, they wont share the connection, or will they?
    If they do: how is the connection kept alive? if they don’t it comes down to shared connections in one and the same script instance, which kinda is the same state that you would have with a responsible programmed application..

  2. Where can I get source code?

  3. SVN repository at pecl.php.net – https://svn.php.net/repository/pecl/mysqlnd_mux/trunk/