Ulf Wendel

mysqlnd plugins: alternative to MySQL Proxy ?!

The mysqlnd plugin API is a well hidden gem of mysqlnd. Mysqlnd plugins operate on a layer between PHP applications and the MySQL server. This is comparable to MySQL Proxy. MySQL Proxy operates on a layer between any MySQL client application, for example, a PHP application and, the MySQL server. Plugins can take over classical MySQL Proxy tasks such as Load Balancing, Monitoring and Performance optimizations. But due to the different architecture and location mysqlnd plugins do not share some common MySQL Proxy annoyances: no single point of failure, no dedicated proxy server to deploy, no new programming language to learn (Lua).

Slides from the IPC Spring conference

This blog posting and today’s presentation at the IPC Spring 2010, a PHP conference in Berlin, are the first public
information on the new toy. Enjoy the slides or continue reading the blog posting.

What mysqlnd plugins can do!

A mysqlnd plugin is kind of an extension to mysqlnd. Plugins can hook virtually all mysqlnd functions. The mysqlnd functions are called by the PHP MySQL extensions (ext/mysql, ext/mysqli, PDO_MYSQL). Consequently it can be said that a mysqlnd plugin can hook all PHP MySQL userspace functions.

Internal mysqlnd function calls cannot only be hooked but also be replaced. There are no limits for manipulating mysqlnd internal function tables: maximum freedom!

Drupal, phpMyFAQ, phpMyAdmin, Oxid, …
ext/mysql, ext/mysqli, ext/PDO_MYSQL
Mysqlnd plugin
Load Balancing Monitoring Performance
MySQL Server

Plugins operate on the C level inside PHP and mysqlnd. They can be made 100% transparent to PHP applications. No application changes are needed because plugins operate on a different layer. A mysqlnd plugin adds a new layer to your setup. However, the new layer is part of a software you already deploy: PHP! A mysqlnd plugin is just another PHP extension in your existing PHP infrastructure.

  • Load Balancing
    • Read/Write Splitting
    • Failover
    • Round-Robin, least loaded
  • Monitoring
    • Query Logging
    • Query Analysis
    • Query Auditing
  • Performance
    • Caching
    • Throttling
    • Sharding

Mysqlnd plugins are a different technology than MySQL Proxy. Both are valid tools for solving a variety of common tasks ranging from Load Balancing over Monitoring to Performance. Both have their room, their disadvantages and advantages. An obvious difference: MySQL Proxy works with all MySQL Clients whereas mysqlnd plugins are specific and limited to PHP.

mysqlnd plugin vs. MySQL Proxy: architecture

A mysqlnd plugin gets installed on the PHP application server. MySQL Proxy can either be run on the PHP applications server or be installed on a dedicated machine to handle multple PHP application servers.

Deploying a proxy layer on the application machines has two advantages:

  • no single point of failure
  • easy to scale out (horizontal scale out, scale by client)

MySQL Proxy is a wonderful and unique piece of software. MySQL Proxy (and mysqlnd plugins) can solve problems easily which otherwise would have required massive changes to existing applications. But MySQL Proxy comes at a price:

  • MySQL Proxy is a new component and technology to master and deploy
  • MySQL Proxy Lua scripts are not PHP: Lua is a new additional language

MySQL Proxy can be customized with C and Lua programming. Lua is the preferred scripting language of MySQL Proxy. For most PHP experts Lua is a new language to learn. A mysqlnd plugin can be written in C (or PHP – as will be shown in future blog posts). C and PHP should be a natural fit for your existing man power and teams.

Hardware Software
Application server PHP application C/Java/PHP/… application
mysqlnd plugin MySQL Proxy  
Dedicated machine     MySQL Proxy  
Database server MySQL

Lifecycle: deamon vs. PHP lifecycle. MySQL Proxy is a deamon. It runs forever. MySQL Proxy can recall earlier decisions. A mysqlnd plugin is bound to the PHP lifecycle of one or multiple web requests. MySQL Proxy can share once computed results among multiple application server. To do the same a mysqlnd plugin needs to store its knowledge in a persistent medium, for example, using another daemon such as Memcache. MySQL Proxy has the edge.

mysqlnd plugin: choose C API or wire protocol

MySQL Proxy works on top of the wire protocol. With MySQL Proxy you have to parse and reverse engineer the MySQL Client Server Protocol. Actions are limited to what can be done by manipulating the communication protocol. If the wire protocol changes, which happens very rarely, MySQL Proxy scripts need to be changed as well.

Layer Software
PHP application C/Java/PHP/… application
C API mysqlnd plugin
Wire protocol mysqlnd plugin MySQL Proxy

Mysqlnd plugins work on top of the C API (and thus also on top of the wire protocol). You can hook all C API calls. PHP makes use of the C API. Therefore you can hook all PHP calls. There is no need to go down to the level of the wire protocol.

Mysqlnd implements the wire protocol. Plugins can parse, reverse engineer,manipulate and even replace the communication protocol. However, there are few use cases which require you to work on this low level.

b>Mysqlnd plugins usually operate the C API layer but they can, if need be, operate on the wire protocol as well..

Plugins let you do your manipulations on two layers: C API and wire protocol. This is more than what MySQL Proxy has to offer. Wire protocol changes do not require plugin changes.

Get the mysqlnd plugin API today!

If you have recently downloaded a PHP 5.3 development code snapshot you already downloaded the "mysqlnd plugin API". No, there is no downloadable mysqlnd plugin which gives you all the fabled features sketched above. The mysqlnd plugin API is under development since at least christmas 2009. It is developed in the PHP source repository and available to public.

The "mysqlnd plugin API" has its roots in the ancient history of mysqlnd. It is a bit of a side effect of Andrey’s attempt to modularize the more than 15k loc of mysqlnd. It took us some time to realize the potential of Andrey’s changes. But this is another story, this is for another blog posting.

The mysqlnd plugin talk at the IPC Spring 2010

Like it? More blogging to come

Several companies have expressed severe interest in the mysqlnd plugin idea after todays presentation at the IPC Spring. If you see some potential in the new toy, please drop us a note. You can reach Andrey, me and Johannes through the usual channels.

More blog postings will follow describing more and more aspects of the new toy. Ideally we manage to teach you how to hack your own mysqlnd plugins – start your GCC’s boys!

Background: mysqlnd is a libmysql replacement

The MySQL native driver for PHP (mysqlnd) is a C library which implements the MySQL Client Server Protocol. It serves as a drop-in replacement for the MySQL Client Library (AKA libmysql AKA libmysqlclient AKA Connector/C). mysqlnd is also a special kind of a PHP extension. Similar to ext/PDO it does not export any userland functions. It serves as a C library for other extensions.

mysqlnd is part of the PHP source code repository as of PHP 5.3. Every PHP source code distribution contains it.

Server API (SAPI)
CGI CLI Embed ISAPI NSAPI phttpd thttpd
Zend Engine PHP Runtime
PHP Extensions
bcmath mysql mysqli mysqlnd pdo pdo_mysql xml

All PHP-MySQL APIs can either make use of the MySQL Client Library or mysqlnd to connect to MySQL. The decision to use one or the other library is made at compile time. Within a PHP binary you can mix mysqlnd and the MySQL Client Library as you like: one PHP MySQL API may use mysqlnd and another PHP MySQL extension may use the MySQL Client Library.

To use the MySQL Client Library, you must have it installed on your system (at least when building PHP), whereas mysqlnd ships with PHP and thus has no pre-requisites.