The MySQL Fabric framework brings two major features: automatic client- and server-side failover and sharding. The manual hints, you need a “Fabric aware driver” for this but it does not list one for PHP. First, you don’t necessarily need new drivers! Second, the "driver" for PHP is the current development version of the PECL/mysqlnd_ms replication and load balancing plugin. The plugin covers the sharding but not the failover/HA quickstart example of Fabric: how the plugin works and why you should not use the plugin – yet.

Partial replication: the unlimited mode

At some point divide-and-conquer is the only know strategy to scale database replication. A huge data set must be devided into smaller sets that are distributed over many servers. There are middleware, in-core and hybrid architectures for such a system. The Fabric approach could be described as a middleware approach: a middleware manages servers and data distribution, clients talk to the middleware to learn about servers. Such an architecture minimizes the dependencies on the database. Power users can easily adapt the middleware to their needs.

PECL/mysqlnd_ms now also handles the "learn about servers/shards" part in the background. It talks to Fabric through XML RPC over HTTP.

The client view: all the standard MySQL APIs

PECL/mysqlnd_ms is a client-side proxy which tries to hide as much of the complexity of using any kind of MySQL cluster (MySQL Replication, MySQL Cluster, 3rd party, MySQL Fabric sharding/HA) from the developer. This includes tasks such as load balancing, read-write splitting, failover and so forth. In the most basic case it is entirely transparent on the API level (mysqli, PDO_MySQL). Given the appropriate configuration, this is a load balanced connection, the SELECT goes to the slaves, the DROP to the master…

$link = new mysqli("myapp", "user", "password", "db");
$link->query("SELECT 1");
$link->query("SELECT 2");
$link->query("DROP TABLE IF EXISTS test");

What happens is that mysqli respectively PDO_MySQL extensions call functions in the mysqlnd library. PECL/mysqlnd_ms plugs in to the mysqlnd library to hooks these calls. If, for example, mysqli_connect() tries to open a connection to the host myapp, PECL/mysqlnd_ms captures the call and checks it config for an entry named myapp. Let the entry be for a MySQL Replication cluster. Then, later when mysqli_query() is executed, the plugin inspects the query and picks the a master or slave from the config to run the query on. Connecting the acutal servers is (mostly) transparent from an application user perspective as you can see from the code example.

The PHP manual has all the details, including the pitfalls and why you can overrule any automatic decision the plugin makes.

Fabric: similar, yet very different

The plugin works much the same with MySQL Fabric. The major difference is that instead of reading a list of servers from a local configuration file, the plugin now connects to a Fabric host to get the list. This makes no difference with regards to the application code itself. When exactly the plugin calls Fabric is work in progress.

Because Fabric monitors and manages the MySQL servers in a farm, it always reports a fresh snapshot of the servers available. In case a server fails and Fabric decides to replace it, the client (here: PECL/mysqlnd_ms) will learn with the next RPC. When using local configuration files, the client will not recognize new or failed servers in the cluster until the configuration file has been deployed. The XML RPC over HTTP call from the client to Fabric to fetch the server adds overhead but it ensures fresh information. Choose between runtime overhead and deployment complexity as you like…

Measuring and taming the XML RPC overhead

To help with the choice, we’ve begun to add new performance statistics, which are subject to change.

To avoid long wait periods for Fabric, there is also a new timeout setting for the plugin configuration. If case of a timeout, you end up with no server list. Then you cannot do much but the most important: tell the impatient user about the problem before he begins hitting the Browsers reload button like crazy.

{"myapp":{"fabric":{"hosts":[{"host":"127.0.0.1","port":8080}],"timeout":2}}}

Setting a timeout has the same effect as setting a timeout for a PHP stream in userland. Judging from a quick glance of the streams code, my uneducated guess is that it sets a timeout for connect and read but not for write. Whatever it does is beyond the control of PECL/mysqlnd_ms.

Use case supported today: sharding

Out of the two major use cases for Fabric, the PHP plugin so far covers the sharding one only (in parts). Fabric has opted for a design where the clients have to give keys ex-bound. The client has to ask Fabric for a list of servers responsible for a given table and key. Then, Fabric replies and the client picks the appropriate server to run the query on. The MySQL manual and various blog postings over the details of the sharding logic. Johannes gives it a quick walk through and shows the following PHP code (comments removed):

$c = new mysqli("test", "root", "", "test");

mysqlnd_ms_fabric_select_global($c, "test.fabrictest");
var_dump($c->query("CREATE TABLE fabrictest (id INT NOT NULL)"));

mysqlnd_ms_fabric_select_shard($c, "test.fabrictest", 10);
var_dump($c->query("INSERT INTO fabrictest VALUES (10)"));

mysqlnd_ms_fabric_select_shard($c, "test.fabrictest", 10010);
$r = $c->query("SELECT * FROM fabrictest WHERE id = 10");
var_dump($r->fetch_row());

The two functions mysqlnd_ms_fabric_select_global() and mysqlnd_ms_fabric_select_shard() encapsulate the procedure of asking Fabric for the shards to use for the table test.fabrictest. They also tell Fabric what you want to do: perform DDL operations (here: CREATE TABLE) or manipulate data asociated with a shard key.

This short snippet calls Fabric three times. It is not hard to predict that this is a potential bottleneck – use the statistics to measure it. Caching is planned for the future.

Open ends, pitfalls, plugin logic breaks

The Fabric code in the plugin is almost untested. It was developed against a pre-pre-release of Fabric. Statistics are bells and whistles compared to – long ago – frequently changing XML formats. Meanwhile Fabric is stabilizing and we can look into a better integration with the rest of the 25k lines of the plugins C code.

Here are two examples of the details not taken care of. Fabric itself does not (yet) bother much about transactions but PECL/mysqlnd_ms has a transaction stickiness feature that will prevent switching servers in the middle of a transaction. The plugin tries to detect transaction boundaries and will not load balance between servers before the current transaction has ended. What should the plugin do in this case?

$c->begin_transaction();
mysqlnd_ms_fabric_select_shard($c, "test.fabrictest", 10);
var_dump($c->query("INSERT INTO fabrictest VALUES (10)"));

mysqlnd_ms_fabric_select_shard($c, "test.fabrictest", 10010);
$r = $c->query("SELECT * FROM fabrictest WHERE id = 10");
var_dump($r->fetch_row());

It is clearly a user error to start a transaction and then switch between shards. What should the plugin do if transaction stickiness is set? Shouldn’t we at least warn the user, if we can – I’ve pushed an first attempt for a warning mode today.

{"myapp":{"fabric":{"hosts":[{"host":"127.0.0.1","port":8080}],"trx_warn_serverlist_changes":1}}}


The Quality-of-Service filter likely needs a couple of tweaks too.

There are just too many cases not tested yet. For example, I would not be surprised if the following code failed without a useful message. In the current implementation mysqli_connect() will not do an actual connect to Fabric, the server lists used by the plugin will be empty and it will bark…

$c = new mysqli("fabric", "root", "", "test");
$c->query("SELECT 1");
mysqlnd_ms_fabric_select_global($c, "test.fabrictest");

Certainly, this is a minor issue. Be warned that there could be more and be warned about the RPC overhead.

The plugin has 26752 lines of C code and 47481 lines of .phpt test code. I guess we have to push it to 50kloc until we can release an PECL/mysqlnd_ms 1.6 alpha. Not sure if we ever got a bug report between alpha and GA, in any case, I would not want to change that…

Happy hacking!

@Ulf_Wendel

PS: I’ve done a mysqlfabric help today using the latest (internal) development version. There was a proper help message no error telling me my cry for help was not understood! Everything around Fabric is a fast moving target.