In his latest post Ulf Wendel looks at the performance issues around the recently introduced MySQL Fabric support included in the mysqlnd extension.

PECL/mysqlnd_ms 1.6 is currently being modified to support sharding and fully automatic server and client failover when using MySQL Fabric (slides) to manage a farm of MySQL servers. PECL/mysqlnd_ms is a mostly transparent load balancer that works with all PHP MySQL APIs (PDO_MySQL, mysqli, ...). The idea is, that if, for example, a MySQL server fails, the plugin talks to MySQL Fabric to learn about alternative servers that Fabric has provisioned automatically. This "talks to" gives implies a performance penalty for applications.

He takes a look at what's happening "behind the scenes" when it comes to using the Fabric functionality and sharding (based on the use of mysqlnd_ms_select_shard). He traces through the execution path and how much slower then end result is. He includes some results from the connection debugging and the number of queries a single request makes.

Johannes Schlüter has a recent post highlighting an interesting PHP extension that can connect memcache with your MySQL server via PHP, the PECL mysqlnd_memcache extension.

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.

In a new post to his blog Ulf Wendel has pointed out that the mysqlnd query cache plugin quickstart is posted on the docs.php.net site.

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.

Johannes Schlüter has a new post to his blog today about a bundle he's created for the Symfony2 framework that integrates some of the advanced statistics mysqlnd provides back to the framework's profiler tool - JSMysqlndBundle.

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).

Ulf Wendel has a new post that compares the performance of a classic method for using the mysqlnd plugin in MySQL replication to mysqlnd_ms, the replication and load balancing plugin for the mysqlnd driver (that works with the mysql and mysqli functionality and is, as of this beta of PHP, the default driver for MySQL connections).

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.

Johannes Schluter has shared a handy mysqlnd plugin that he's written up that does two common things at once when you're pulling data from your database and pushing it into JSON - a mysqlnd_query_to_json function.

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.

On the Zend Developer Zone today there's a new post from Cal Evans mentioning some goodies that are available to PHP programmers in the world of MySQL.

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.

On the ThinkPHP blog there's a recent post looking at writing a query logger in PHP as a mysqlnd plugin using the mysqlnd_uh extension.

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.

New on the Internet Super Hero blog today Ulf Wendel has a huge post with a complete listing of all one hundred and twenty different kinds of statistics that the mysqlnd driver for PHP can gather during its connections:

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

This new post on the PHP::Impact blog links to a Sun blog post about the MySQL driver that provides a more direct connection between your PHP instance and MySQL - mysqlnd.

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).