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 | ||
| 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.
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.
June 2nd, 2010 at 10:21 pm
I’m very interested in seeing what come out as a load-balancing module. I would love to see PHP get functionality similar to the Java connector http://blogs.sun.com/carriergrademysql/entry/how_to_use_jdbc_connector. As NDB Cluster has matured it makes more sense for Web environments. Having native PHP load-balance module would further reduce the requirements for expensive or complicated dedicated load balancing hardware in those environments.
June 2nd, 2010 at 10:41 pm
First of all there is no load-balancing plugin yet. And I am not going to promise that there will be ever such a product from MySQL. BUT I’ll do my best to support any developer who wants to start an Open Source project on developing such a plugin.
Implementing a basic load-balancing plugin for mysqlnd is pretty trivial, if you have some PHP extension development skills. To gain those skill you just have to read the book of the Über-Lady (Sara Golemon, Extending and Embedding PHP).
However, some load balancer may be using state information for their decisions. And this is where the concept of proxies/plugins/load balancing solutions which exist on the application server only may fall short. One load balancer may not be aware of the decisions of another. And, in case of mysqlnd plugins things get even worse: plugins are part of PHP, PHP may die at the end of the request and your state is either gone or has to be persistet.
If all you want is simple round-robin those worries may not apply.
In future posts I plan to demo how one could write mysqlnd plugins in the PHP user land. We’ll have a look at how to get from the dirty C level into PHP world - something you may want to have for rapid prototyping. If anybody ever implements it - there are good reasons not to do it - you could write your round-robin load balancing logic in PHP and add it to your PHP apps using, for example, the PHP auto_prepend feature.
Whatever - thanks for the feedback! This is exactly what I am looking for. I, someone from the glass cage, would like to hear : what is needed by the users from the real world.
June 3rd, 2010 at 12:43 am
I’d be interested how would database-sharding plugin do. I.e. do stuff like redirecting queries to different servers based on a shard-key, querying different shards in parallel, or parallelizing writes to the databases (maybe couple this with gearman to achieve an eventually consistent replication over replicated shards) …
June 3rd, 2010 at 8:41 am
Pavel, I cannot comment on the sharding logic that may be used in any such plugin. I’m sitting in my Connectors glass house not developing and deploying large PHP web sites. I do not know what logic would be needed. Thus, I can only comment on implementation aspects.
Sharding probably requires a proper SQL parser for inspecting queries. I heard that Mayflower/thinkPHP has once used the MySQL SQL parser syntax to build such a SQL parser in C/C++ for use in a PHP extension. I was told that it had been almost a no-brainer for them. So, that part should be quite doable, if MySQL server license works for you…
Reading and writing to shards in parallel can be achieved using multiple connections and non-blocking I/O. One can do asnychronous DB calls using mysqli since about two years, http://blog.ulf-wendel.de/?p=201 . The necessary calls are even exported to userspace - you can do it in your *.php files. What is likely not possible is using one connection to run multiple SELECT in parallel. That is simply not supported by the server. Thus, a client will always have to use multiple connections. However, those multiple connection logic could be hidden from the application - that’s what plugins are about.
When it comes to replication another scenario and solution comes into my mind. A common issue with replication are lags. It may take some time until all replicated clients got the latest updates. This may be an issue with certain types of queries. Now, a plugin could inspect queries and filter those queries which must return current data and may not be send to any of the replication clients, if they are behind the master. If the plugin detects a critical query and a replication lag, it could redirect the query to the master until the replication client has catched up. Again, this could be implemented in a transparent way.
June 3rd, 2010 at 8:44 am
Pavel,
ah, of course, write-splitting… if you got a SQL parser in your plugin, which is doable, why not. Happy hacking! C API details will follow.
June 3rd, 2010 at 12:26 pm
Hey Ulf,
I really enjoyed the sneak peak you gave us Tuesday evening and judging from the comments then and here mysqlnd is pretty exciting - both for PHP and MySQL.
Keep rocking!
Till
June 3rd, 2010 at 12:34 pm
Till, I owe you a beer or two. See you at the BEPHPUG BBQ …
June 3rd, 2010 at 1:08 pm
Pavel,
it is quite possible and I have done prototyping of plugins that take the SQL apart and try different thigns. A very simple plugin, for example, which opens behind the scenes more connections, to slave servers, and once the query is thrown at mysqlnd it uses LIMIT to read parts of the results from the slaves and then assemble in the client. The reads in the clients are transparent, if all the data from one slave is read, the next is used until there are no more data to be read.
Sharding can be also done, a scanner with a parser + some hints in the SQL to mysqlnd. Don’t forget that mysqlnd can’t add new functions to the API you are using and can’t change the semantics of the functions (by adding new parameters), so the only way to instruct/hint mysqlnd is through the SQL.
Read/write splitting is about 300 loc, the whole C file.
All these are only proof of concepts and because we are using Oracle rules now we can’t say whether something will become a product or when a feature will appear in a product.
Andrey