Johannes Schlüter

PHP users who attended any of my recent PHP&MySQL related talks or read Ulf's blog will know our mysqlnd_ms plugin. This plugin hooks into PHP's mysqlnd library and provides transparent support for replication and load-balancing features. Without changing your application you get transparent load-balancing and read-writ splitting so all your reading queries will be sent to a slave while the writes go to a master server. The exact strategies for that can be defined in mysqlnd_ms's configuration so quite often no, or only few application changes are needed. But we have one limitation: The MySQL servers have to be configured in each configuration on all your PHP servers, this can be annoying when you're changing your environment like adding a new slave or promoting a machine to master in case the original master fails. But there's help coming!

At this year's MySQL Connect conference we've announced the initial labs release for MySQL Fabric. MySQL Fabric aims to be "an integrated environment for managing a farm of MySQL server supporting high-availability and sharding." Part of this framework is an RPC interface to query available servers which are managed by MySQL Fabric which delivers us the missing piece for mysqlnd_ms.

As this release of Fabric put the focus on sharding, this is what I want to show here, too. A general introduction to MySQL Fabric and its sharding features can be found on VN's blog so I'll be quite fast in some areas, for details please refer to the documentation and the mentiond blogs.

The first thing we need is the MySQL Utilities package with Fabric support which is available from labs.mysql.com. After installing this package you have to locate the main.cfg configuration file and configure the storage instance.

[storage]
database = fabric
user = fabric
address = localhost:3300
connection_timeout = 6
password = i do no now

This is a MySQL server where Fabric will store its configuration and such. After that we can initialize Fabric and start the daemon.

$ mysqlfabric setup
$ mysqlfabric start

The setup step creates tables in the configured database and the start starts the daemon process. Now we can o and configure our server groups. A server group contains a master server where the group's data is being written to and a number of slaves to which MySQL will replicate data. For our sample sharding setup I plan to create two shards and a global group. The purpose of the global group is to hold table definitions and data which Fabric will make available on all systems in our environment. Each of these groups will, in this example, have one master and one slave. This means we need six MySQL server instances running. These six instances should all be running MySQL 5.6. an except from having binary logging enabled and having different server ids there is no replication configuration needed before running these commands. In my example setup I'm running all of those on one machine, obviously that's only useful for tests:

$ mysqlfabric group create global
$ mysqlfabric group add global 127.0.0.1:3301 root secret
$ mysqlfabric group promote global
$ mysqlfabric group add global 127.0.0.1:3302 root secret

$ mysqlfabric group create shard1
$ mysqlfabric group add shard1 127.0.0.1:3303 root secret
$ mysqlfabric group promote shard1
$ mysqlfabric group add shard1 127.0.0.1:3304 root secret

$ mysqlfabric group create shard2
$ mysqlfabric group add shard2 127.0.0.1:3305 root secret
$ mysqlfabric group promote shard2
$ mysqlfabric group add shard2 127.0.0.1:3306 root secret

So this creates the three groups and will configure the servers to replicate the servers as needed. With this setup the server on port 3301 will be the global master. 3302, 3303 and 3305 will e 3301's direct slaves and 304 will be configured to be a slave for 3303, as will 3306 to 3305.

Now we go to define our sharding rules. I'm going to use range based sharding with two shards. The first shard, which will be assigned to the server group shard1 created above will have shard id 1 to 9999 and the second shard, in group shard2 will have data for shard key values 10000+. We also define the table fabrictest in the test schema as our sharding tale and id as the shard column.

$ mysqlfabric sharding define RANGE global
$ mysqlfabric sharding add_mapping 1 test.fabrictest id

$ mysqlfabric sharding add_shard 1 shard1 ENABLED 1
$ mysqlfabric sharding add_shard 1 shard2 ENABLED 10000

Note that for range-based sharding we don't have to define the upper bound as that is defined by the lower bound of the next shard.

Now we have MySQL Fabric and our MySQL Servers configured and can go to PHP. As mentioned in the beginning we need mysqlnd_ms, to be precise the 1.6.0 alpha release which we can install using pecl:

$ sudo pecl install mysqlnd_ms-alpha

To configure PHP we firstly need a mysqlnd_ms configuration file. myslqnd_ms uses json and a simple confiuration using Fabric might look like this:

 fabric.json:

{
        "test" : {
                "fabric":{
                        "hosts": [ { "host": "localhost", "port": 8080 } ]
                }
        }
}

This configures the application test to use a MySQL Fabric based setup where MySQL Fabric's RPC daemon runs on the local machine. Again: We put all on one machine for a test, not what one would do on a production setup.

Next we locate our system's php.ini file and enable mysqlnd_ms to use our config.

php.ini:
extension=mysqlnd_ms.so
mysqlnd_ms.enable=1
mysqlnd_ms.config_file=/path/to/fabric.json

And now we are finally done and run a test script.

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

echo "Creating global table:\n";
mysqlnd_ms_fabric_select_global($c, "test.fabrictest");
var_dump($c->query("CREATE TABLE fabrictest (id INT NOT NULL)"));
echo "Server used: ".mysqlnd_ms_get_last_used_connection($c)['scheme']."\n\n";

echo "Inserting with ID 10:\n";
mysqlnd_ms_fabric_select_shard($c, "test.fabrictest", 10);
var_dump($c->query("INSERT INTO fabrictest VALUES (10)"));
echo "Server used: ".mysqlnd_ms_get_last_used_connection($c)['scheme']."\n\n";

echo "Trying to read id 10 from 10010:\n";
mysqlnd_ms_fabric_select_shard($c, "test.fabrictest", 10010);
$r = $c->query("SELECT * FROM fabrictest WHERE id = 10");
var_dump($r->fetch_row());
echo "Server used: ".mysqlnd_ms_get_last_used_connection($c)['scheme']."\n\n";
?>

With this script we do a few things, first observation is the hostname test. The mysqlnd_ms plugin will recognize that as application name and will refer to its configuration. Second are the mysqlnd_ms_* functions. First we pick the global group and execute a CREATE TABLE operation there. mysqlnd_ms will detect that this is a write operation and therefore connect to the globals master. This should be 127.0.0.1:3301 which hopefully is printed by the echo call. Then we select the shard responsible for id 10 in the fabrictest table and insert data. mysqlnd_ms will, again, detect that this is a write operation and will therefore figure out where writes to that shard have to go to, which is 127.0.0.1:3303. Finally we do an operation which will not really succeed: We select the servers for shard 10010 which is shard2 from our setup and then query for id 10. the data we stored in shard1. This will query 127.0.0.1:3306 (slave of 3305 in shard2 group) which will return an empty result set.

I hope this is enough to get you started, you can now add shards or migrate them or take servers down and promote current slaves to masters etc. and see how the system reacts.

In a future post we will combine this with Doctrine stay tuned.

Note: This blog post features labs/alpha releases. Which aim at demonstrating functionality. They are not for production use. There might be stability issues, there certainly are performance restrictions we're working on. We'd like however to receive feedback.

The MySQL Server has a quite simple interface for plugins to create tables inside INFORMATION_SCHEMA. A minimal plugin for creating a table with nothing but a counter might look like this:

static int counter_fill_table(THD *thd, TABLE_LIST *tables, Item *cond)
{
  ulonglong value= 0;
  
  while (1)
  {
    table->field[0]->store(value++, true);
  }
  
  return 0;
}

static ST_FIELD_INFO counter_table_fields[]=
{
  {"COUNT", 20, MYSQL_TYPE_LONGLONG, 0, MY_I_S_UNSIGNED, 0, 0},
  {0, 0, MYSQL_TYPE_NULL, 0, 0, 0, 0}
};

static int counter_table_init(void *ptr)
{
  ST_SCHEMA_TABLE *schema_table= (ST_SCHEMA_TABLE*)ptr;

  schema_table->fields_info= counter_table_fields;
  schema_table->fill_table= counter_fill_table;
  return 0;
}

static struct st_mysql_information_schema counter_table_info =
{ MYSQL_INFORMATION_SCHEMA_INTERFACE_VERSION };

mysql_declare_plugin(counter)
{
  MYSQL_INFORMATION_SCHEMA_PLUGIN,
  &counter_table_info,          /* type-specific descriptor */
  "COUNTER",                    /* plugin and table name */
  "My Name",                    /* author */
  "An I_S table with a counter",/* description */
  PLUGIN_LICENSE_GPL,           /* license type */
  counter_table_init,           /* init function */
  NULL,                         /* deinit function */
  0x10,                         /* version */
  NULL,                         /* no status variables */
  NULL,                         /* no system variables */
  NULL,                         /* no reserved information */
  0                             /* no flags */
}
mysql_declare_plugin_end;

This is quite straight forward and documented inside the MySQL documentation. It also has an obvious issue: It will run forever (at least if we assume we don't run in an out of memory situation). Luckily we might have a user who foresees this issue and added a WHERE clause like here:

SELECT COUNT FROM INFORMATION_SCHEMA.COUNTER WHERE COUNT < 10

This week there are two big events for the MySQL community: The O'Reilly MySQL Conference and Oracle Collaborate run by the IOUG. At these events our Engineering VP, Tomas Ulin, announced the latest milestone releases for our main products. MySQL 5.6 and MySQL Cluster 7.1 as well as our new Windows Installer. There's lots of cool stuff in there but one feature really excited me: MySQL 5.6 contains a memcache interface for accessing InnoDB tables. This means you can access data stored in MySQL not only using SQL statements but also by using a well established and known noSQL protocol.

This works by having the memcache daemon running as plugin as part of the MySQL server. This daemon can then be configured in three ways: Either

• to do what memcached always did - use an in memory hash table to store its data - or
• to access an InnoDB table to store and read data from or
• to use its own hash table in memory and fall back to InnoDB if data is not found directly in memcache.

This combines the power of MySQL and InnoDB's persistent storage with the lightweight protocol memcache uses, which has faster connecting times (no authorization handshake etc.) and faster data access (no SQL parsing, optimization etc.) while you're still able to query the data using SQL when you're doing more complex operations.

Of course I had to give it a run with PHP.

First step for using this is fetching the MySQL preview release and configuring it accordingly. My colleague Jimmy Yang from the InnoDB team has a nice blog posting showing these first steps. After that we have to configure PHP where we have two choices: We can use the a bit older memcache module or the newer memcached module. I've chosen the first one as that was already configured on my system. On most systems the installation should be as easy as querying your package manager or using PECL:

# pecl install memcache
or
# pecl install memcached

And then adding the corresponding entry (extension=memcache[d].so) to your php.ini file.

So let's do a first test from command line:

$ php -r '$m = memcache_connect("localhost", 11211); ' \
         '$m->add("key", "value"); var_dump($m->get("key"));'
string(5) "value"

So we store a value in memcache and then load it again to see if it was stored properly. Now we verify the results directly in MySQL:

mysql> SELECT * FROM demo_test WHERE c1 = 'key';
Empty set (0.00 sec)

Uh, what's wrong? - O simple: We didn't read Jimmy's article properly:

If you would like to take a look at what’s in the “demo_test” table, please remember we had batched the commits (32 ops by default) by default. So you will need to do “read uncommitted” select to find the just inserted rows

So we can apply that knowledge and query again:

mysql> set session TRANSACTION ISOLATION LEVEL read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM demo_test WHERE c1 = 'key';
+------+------+------+------+-------+------+------+------+------+------+------+
| cx   | cy   | c1   | cz   | c2    | ca   | CB   | c3   | cu   | c4   | C5   |
+------+------+------+------+-------+------+------+------+------+------+------+
| NULL | NULL | key  | NULL | value | NULL | NULL |    0 | NULL |    1 | NULL |
+------+------+------+------+-------+------+------+------+------+------+------+
1 row in set (0.00 sec)

And yay! - We see our value in between the other columns for meta-data and other things.

Both PHP modules provide a session handler so you can store your session data easily in memcacheInnoDB. For configuring this we first need to add two entries to our php.ini file:

; when using the "memcache" extension:
session.save_handler=memcache
; when using the "memcached" extension:
; session.save_handler=memcached

session.save_path="tcp://localhost:11211"

After restarting the web server, so it reads the new configuration we can test it with a simple script:

<?php
session_start();
echo "<pre>Session ID: ".session_id()."\n";
var_dump($_SESSION);
$_SESSION['foo'] = 'bar';
?>

When first requesting this we will receive an output like

m1h4iqmp6hc7e4l85qlld0gtd
array(0) {
}

Then we reload the page and see:

m1h4iqmp6hc7e4l85qlld0gtd1
array(1) {
  ["foo"]=>
  string(3) "bar"
}

After that we can, again, look directly into MySQL:

mysql> select * from demo_test where c1 = 'm1h4iqmp6hc7e4l85qlld0gtd1';
+------+------+----------------------------+------+----------------+------+------+------+------+------+------+
| cx   | cy   | c1                         | cz   | c2             | ca   | CB   | c3   | cu   | c4   | C5   |
+------+------+----------------------------+------+----------------+------+------+------+------+------+------+
| NULL | NULL | m1h4iqmp6hc7e4l85qlld0gtd1 | NULL | foo|s:3:"bar"; | NULL | NULL |    0 | NULL |    4 | NULL |
+------+------+----------------------------+------+----------------+------+------+------+------+------+------+
1 row in set (0.00 sec)

I hope this helps you to get started. If you'd like to learn more about MySQL 5.6, MySQL Cluster 7.1 (which btw, also can be access using memcache!) our new installer or such you can watch a recording of Tomas' keynote or visit dev.mysql.com.