Monthly Archives: October 2010

Reverse Sharding (unsharding).

Sharding seems to be all the rage these days (as well as NoSQL). Unfortunately, reverse sharding is never addressed. What happens when you’ve sharded your application into 10 different servers and usage goes down? You’re stuck paying for 10 servers!

Understandably, everyone is an optimist in this field, but realistic business practices require cost savings which can only be realized via unsharding.  Be aware that unsharding, is not the same as merging.  If you’ve sharded by uid, and you can effectively dump all that data back into the main db, then lucky you, skip this article.   Unfortunately, sharding in the field sometimes consists of binary duplication of data from the original db, so doing a direct sql merge is not always possible and doing it programatically with huge databases will result in high loads and a very lengthy merge.

I will show you a quick and dirty way to unshard, while still retaining individual shards, and, most imporantly, retaining the ability to re-shard again.  This little tutorial assumes you’re an old hand at setting up db replication.  If not, brush up on it NOW.

We’ll start with a scenario where an application has been sharded once.  It doesn’t really matter how it was sharded, by pk or some other way.  In fact, the tables don’t even have to match!

The players:  MariaDB ServerA , MariaDB ServerB

Each db server contains the db ‘myapp’.  Your goal is to unshard the db unto ServerB (it’s arbitrary where it goes..).

On ServerA:

time mysqldump –single-transaction –master-data=2 myapp > ./myapp_sa.sql

<… some time goes by …>

copy ‘myapp_sa.sql’ to ServerB.

On ServerB:

mysqladmin create myapp_sa

time mysql myapp_sa < ./myapp_sa.sql

<… more time goes by …>

At this stage you’ve dumped and imported the db’s.  Now comes the crucial part, the most sensitive part.  The part the begs the question, have you made backups?  Have you punk?  Huh?  I hope you have, because if you make a typo, you’re SOL without backups.

On ServerB edit /etc/my.cnf and make sure the following are added:



You will need to restart MariaDB/Mysql at this point.   Remember, you have NOT enabled replication from ServerA to ServerB at this point.  HAVE NOT, as in DO NOT.   If you skip any steps, do so at your own peril.

Now that’s you’ve restarted MariaDB, login and check to make sure everything is halal:

MariaDB [(none)]> show variables like ‘log_slave_updates’\G
*************************** 1. row ***************************
Variable_name: log_slave_updates
Value: ON
1 row in set (0.00 sec)

What’s that?  “show variables like ‘repli%’\G” doesn’t bring up the replication variables?  That’s correct.  Apparently, the rewrite occurs at a level below Mariadb, so the db doesn’t actually see these variables.  There’s no way to check to see if it’s working.  If you’re unsure, restart your db.  Make SURE that you have not misspelled the db names.  If you have, you’ll corrupt your primary shard!

Now that you’ve restarted MariaDB, enable replication from ServerA -> ServerB.  Let them catch up (you should be able to see via innotop that queries are going into the _sa table), or you can mysqlbinlog the master log to see what’s what.  Log_slave_updates is not crucial btw.  It assumes you have a slave server on ServerB, as well you should (for snapshots, etc.  More on this in a later post).

When they catch up, point the application’s shard to ServerB.myapp_sa instead of ServerA.myapp and you’re golden.

Step 2. Now get rid of ServerA!

Step 4. Profit!

Enjoy your cost savings!