Now this blog entry is tagged a s PHP. Hs is that coming into play? - Well, on the one side we have this fast memcache interface, which allows to access almost arbitrarry data from the database. On the other side we have our PHP mysqlnd plugin interface where we can add special features, like query caching or load balancing, transparently to any PHP application. Why not combine those two things? - Good question. That's what we have done in the PECL mysqlnd_memcache PHP extension.

He includes some sample PHP code showing it in action - two examples: one using the "mysqlnd_memcache_set" method to set a memcache server on the MySQL connection and another showing two queries and how they're handled behind the scenes by this driver plugin.

New in the PHP manual: a quickstart for the mysqlnd query cache plugin. PECL/mysqlnd_qc, the mysqlnd query cache plugin, is transparent and ease to use. But, how? Some pointers have been given in assorted presentations, here on my blog and in some, few examples from the manual. Fixed. You can now browse a quickstart to gain a quick overview.

The query cache plugin can replace the query caching MySQL does and can help with things like multiple storage options (memory, APC, Memcache, etc) and almost no changes to your application. There's also a method (mysqlnd_qc_get_query_trace_log) that comes with the plugin that gives you a "stack trace" of every query run through the MySQL interface.

Symfony provides a nice feature, which is the Symfony Profilier, an extensive logging and reporting system for Symfony developers to understand what's going on. A part of it is the Doctrine query logger which lists all database queries executed by Doctrine and their execution time. This is nice but when we're using mysqlnd in our PHP build we have more information available. "So why not use that information," I thought and built a new bundle for Symfony 2 doing exactly that.

His bundle takes the "150 or so" statistics and drops them into the profiler, giving you even more insight into how your script is running. The bundle is available on github, making it simpler to fork and extend to add other features (some he suggests include caching and replication decisions using information from other mysqlnd plugins).

Recently I was asked if PECL/mysqlnd_ms should be used to add MySQL replication support to a yet to be developed PHP application. The mysqlnd plugin, which supports all PHP MySQL extensions (PDO, mysqli, mysql), stood up against a classical, simple, proven and fast approach: one connection for reads, one connection for writes. Let's compare. This is a bit of an unfair challenge, because PECL/mysqlnd_ms was designed as a drop-in for existing applications, not optimized for those starting from scratch, *yell*... The plugin stands up quite well, anyway!

He starts with a look at the "classical pattern" of using a factory or singleton to make a database object instance that gives back different connections for reads versus writes (slave vs master). The mysqlnd_ms plugin allows you to define configuration settings to tell the queries to automatically go to certain places for different actions. For example, you could use "master_on_write" to tell it to use a master node if you're doing an INSERT or UPDATE versus a SELECT. He also shows a more complex example using a SQL hint and one issue that might come from the "human element" - not paying attention to database character sets.

In my spare time I'm currently writing a shiny Web 2.0 application where I'm heavily using AJAX-like things, so what I do quite often in this application is, basically this: Check some pre-conditions (permissions etc.) then select some data from the database, do a fetch_all to get the complete result set as an array and run it through json_encode; Of course that example is simplified as I'm using the Symfony 2 framework for this project. [...] So I wrote a mysqlnd plugin.

His plugin (that he admits is "a hack") gives you the mysqlnd_query_to_json function that automagically transforms the results from the passed in query into JSON output. He notes some things lacking - like Unicode encoding and no MySQL bitfield support.

PHP 5.3 shipped with a new driver for MySQL, mysqlnd or MySQL Native Driver. While one of it's biggest features is that it gives us a mysql driver that is part of PHP so it doesn't suffer from licensing issues that the previous driver did. However, in the redesign, the architecture was changed.

He mentions the mysqlnd_uh that's a part of this new MySQL driver and an interesting article from Ulf Wendel about its use in transparent load balancing and sharding. The article includes code that randomly picks a MySQL server for the script to use.

The article is a bit difficult to read at time but trust me, stick with them because the stuff in there is the stuff you want in your head when you are working on your next application.

A new approach to implementing a query logger and potentially more complex features such as monitoring or read/write-splitting is the MySQLnd Userland Handler Extension (mysqlnd_uh, pecl website). The extension lets you register a PHP class as a proxy for every MySQLnd connection. Every call to a function to MySQLnd (usually indirect through mysqli, mysql, pdo_mysql) is passed to the PHP class, which then calls the original MySQLnd function.

They give a simple example to start - a logging function and how to configure it - followed by a more real-world scenario of logging inside of a project plugin.

This is about twice as much as it was when I blogged about the 59 tuning screws for mysqlnd. While the basics have not not changed and the API calls for accessing the data remained the same (see previous posting) the new figures have never been described before.

Each of the items on the list is described and some include some sample use cases. Here's just a few:

• packets_sent, packets_received
• bytes_received_eof_packets, packets_received_eof
• bytes_received_rset_field_meta_packet, packets_received_rset_field_metabytes_received_change_user_packet, packets_received_change_user
• result_set_queries
• slow_queries
• flushed_normal_sets, flushed_ps_sets
• rows_fetched_from_client_ps_cursor
• connect_success, connect_failure
• in_middle_of_command_close
• command_buffer_too_small

Due to the tight integration into PHP 5.3 (and later), mysqlnd eliminates the dependency on the MySQL client programming support when the database extension(s) and the database driver are built with the support for mysqlnd. mysqlnd is not another PHP extension like mysqli nor it has an exposed API to the userland. It is a library that provides almost similar functionality as that of the MySQL client library, libmysql. mysqlnd and libmysql libraries implement the MySQL communication protocol - hence both of those libraries can be used to connect to the MySQL Server.

The Sun blog entry also walks you through the installation and configuration of a PHP instance with the native driver installed (with the upcoming PHP version, 5.3).

Ever wondered what your PHP application and MySQL actually do? An experimental mysqlnd branch will give you full access to the network communication stream. Using a custom PHP stream filter you can then intercept the communication.

He shows how to attach a filter on to the stream and what sort of output it would give for a query that wasn't valid SQL (including all of the "behind the scenes" queries that the database does to get table information from the schema).