MySQL Fabric and Sharding


Last time we set up a High Availability server farm with MySQL Fabric. Now it is time to set up sharding. I will be using the good old World database and sharding the City table on the ID field. There are 4,079 cities in this table and they will be split in two. So one shard, that we will call CityLow will have the records 2,000 and below and the other records at 2,001 and above will be called CityHigh. We also need a global group for setting up sharding that will be called CityGlobal.

Sadly, the first step is to remove the previous setup with mysqlfabric manage teardown. This will remove the fabric database from the Fabric controller. Fabric itself has to be stopped with mysqlfabric manage stop. The command mysqlfabric manage setup will set up a fresh, clean fabric database. And then we can start the Fabric controller with mysqlfabric manage start --daemonize.

Now we crate the groups for CityLow, CityHigh, and CityGlobal.

mysqlfabric group create CityLow
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

uuid finished success result
------------------------------------ -------- ------- ------
a7cafabf-1550-4f7c-a8e7-79458e47b9d5 1 1 1

state success when description
----- ------- ------------- ------------------------------------------------------------------
3 2 1428436552.17 Triggered by .
4 2 1428436552.2 Executing action (_create_group).
5 2 1428436552.26 Executed action (_create_group).

mysqlfabric group create CityHigh
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

uuid finished success result
------------------------------------ -------- ------- ------
81a92aa2-1c71-4327-9fc5-4e8098ebff5c 1 1 1

state success when description
----- ------- ------------- ------------------------------------------------------------------
3 2 1428436556.61 Triggered by .
4 2 1428436556.64 Executing action (_create_group).
5 2 1428436556.7 Executed action (_create_group).

mysqlfabric group create CityGlobal
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

uuid finished success result
------------------------------------ -------- ------- ------
ee83da52-fae2-4355-95b3-1ac58039c8d7 1 1 1

state success when description
----- ------- ------------- ------------------------------------------------------------------
3 2 1428436562.23 Triggered by .
4 2 1428436562.26 Executing action (_create_group).
5 2 1428436562.32 Executed action (_create_group).

Servers are added with mysqlfabric group add CityLow 10.10.13.10, myslfabric group add CityHigh 10.10.13.20, and mysqlfabric group add CityGLobal 10.10.13.30. Then we promote a sever in each group with mysqlfabric group promote CityLow, mysqlfabric group promote CityHigh, and lastly mysqlfabric group promote CityGlobal.

A unique sharding ID is needed and I missed noting this unique number as I did not see it (here the docs are not matching the output of the commands). And this gave me problems with the step following this one.
mysqlfabric sharding create_definition range CityClobal
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

AttributeError: ‘NoneType’ object has no attribute ‘master’
I ignored the above error and carried on hoping all was well.

And I ran smack into a problem trying tell Fabric which table to use. mysqlfabric sharding add table_table 1 City.World ID The error message I recevied was DatabaseError: Command (INSERT INTO shard_tables(shard_mapping_id, table_name, column_name, range_check) VALUES(%s, %s, %s, %s), (‘1’, ‘World.City’, ‘ID’, False)) failed accessing (localhost:3306). 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`fabric`.`shard_tables`, CONSTRAINT `fk_shard_mapping_id` FOREIGN KEY (`shard_mapping_id`) REFERENCES `shard_maps` (`shard_mapping_id`)).!! I had seen ‘1’ in the docs but that was not working for me. So I peeked in the fabric.shard_maps table and found the magic number ‘2’.

mysqlfabric sharding add_table 2 World.City ID
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

uuid finished success result
------------------------------------ -------- ------- ------
ab98c0a6-390d-4334-992e-557fc37b7a2d 1 1 1

state success when description
----- ------- ------------- ------------------------------------------------------------------
3 2 1428437358.46 Triggered by .
4 2 1428437358.49 Executing action (_add_shard_mapping).
5 2 1428437358.56 Executed action (_add_shard_mapping).

Now tell Fabric how slice and dice the table into shards. Remember we want two — Low with 0-2000, and High with the rest.
mysqlfabric add_shard 2 CityLow/0,CityHigh/2000 --state=ENABLED
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

uuid finished success result
------------------------------------ -------- ------- ------
f20da46d-6cc1-4464-a0dd-991b35d83e97 1 1 1

state success when description
----- ------- ------------- ------------------------------------------------------------------
3 2 1428437428.99 Triggered by .
4 2 1428437429.03 Executing action (_add_shard).
5 2 1428437429.3 Executed action (_add_shard).
3 2 1428437429.22 Triggered by .
4 2 1428437429.3 Executing action (_add_shard_range_check).
5 2 1428437429.42 Executed action

So now we check to see which shard hold the record with the ID of 15 and it should be the CityLow shard on 10.10.13.10!
$mysqlfabric sharding lookup_servers World.City 15
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

server_uuid address status mode weight
------------------------------------ ----------- ------- ---------- ------
09d13be1-cdd0-11e4-8643-0800273b9c80 10.10.13.10 PRIMARY READ_WRITE 1.0

And is ID 3333 on the CityHigh shard?

$mysqlfabric sharding lookup_servers World.City 3333
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

server_uuid address status mode weight
------------------------------------ ----------- ------- ---------- ------
0f611996-cdd0-11e4-8643-0800273b9c80 10.10.13.20 PRIMARY READ_WRITE 1.0

Yes!

The table is sharded! Next blog will cover some sharding magic.

One Response to MySQL Fabric and Sharding

  1. […] a farm of MySQL servers. In the last two posts you can see how High Availability Fail over and Sharding work. When things are working right, the group lookup_servers will look something like this: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s