Playing with Hibernate Shards

After hearing a lot of discussion at QCon and elsewhere about the notion of database sharding (a new, hipper name for the older, more mundane name "partitioning"), I decided to download the Hibernate Shards project and give it a whirl (what else do you do when you can't sleep?). I wrote an earlier blog entry on running multiple MySQL instances that will help when playing with Hibernate Shards.

As I'm reasonably proficient with Hibernate, I figured that I could be able to jump in and get a pretty good understanding just with a few days effort. Luckily, I was correct - my basic assumptions and paradigms for using Hibernate remain mostly intact (Configuration, SessionFactory, and Session object are almost the exact same). The details, of course, lie more in the distribution if data between multiple database instances.

Just a few quick Hibernate tech notes before talking about the data implications. You need to have a little bit more going on in your Hibernate config file(s), one file for each shard. Nothing big there. When creating a SessionFactory, you have to provide implementations for the following interfaces:

• ShardAccessStrategy - a strategy for accessing sharded databases for queries (not loading an item by it's id). The provided implementations offer either sequential (including a round-robin, load balanced version) or parallel access.
• ShardResolutionStrategy - a strategy for determining which shard to access when loading an entity.
• ShardSelectionStrategy - a strategy for determining which shard to store a new entity in.

As relates to data, let's first discuss the idea of entity ID generation. As your data is now spread out multiple databases, you really can't use a database sequence for generating ids - unless you restrict each database's valid sequence values to a restricted range (db1 gets ids 0 - 3000, db2 gets ids 3001 - 6000, and so on). Somehow, this sounds easy but rather self-limiting. Out of the box, Hibernate Shards provides two mechanisms for managing ID generation:

• ShardedUUIDGenerator - this implementation basically creates a large random number, and also encodes the shard id into the id. What you get is a really big number (my sample tests had about 30-36 digits each).
• ShardedTableHiLoGenerator - this implementation creates a hilo table in one of the shards and uses that to generate all IDs. Of course, the shard then becomes not only a bottle neck for performance, but is a single point of failure.

After playing with it and thinking for a few days, the UUID generator seems the better choice, as long as your db is cool with big primary keys. MySQL worked fine with this, but I needed to make the column of type numeric(64,0) - 64 may be a bit large, but it made my sample code work so I left it. Of course, as your entity's id is shard specific, all the data related to that entity must reside on the same shard. This makes total sense and is definitely what you want from both management and performance perspectives.

Of course, deciding exactly how spread data amongst the shards is a big decision. Unfortunately, Hibernate Shards does not provide a facility to live resharding (not that it would be easy, mind you). Essentially, you would have to do some slight-of-hand while then data was being moved/redistributed, then update your Hibernate configurations and entity to shard mappings. Hibernate Shards does, however, provide a concept of virtual shard ids, and you can point multiple virtual shards to physical shards. This seems like a good idea as you can define many virtual shards which can map to just to two or three physical shards, then as you grow, it's only a slight config update to repoint the virtual shard. The documentation says "Virtual shards are cheap", so it's probably reasonable to create a bunch of virtual shards as long as it doesn't hurt performance.

Hibernate Shards does have a few gotchas:

• HQL is pretty much unuseable at this point, and there seem to be some incomplete pieces with Criteria queries. This is mainly due just to the immaturity of the project; I expect they'll be coming along soon.
  
• Master values from lookup tables. As Hibernate demands one and only one instance of an object with a given id, if you use master objects from a lookup table, you need to be careful that you don't get into strange cross-shard problems. This should only happen when saving/updating, but it is something to be aware of. For some advice about this problem, I have another blog entry about that.

All in all, Hibernate Shards is a rather nice wrapper for hiding all the complexity of spreading your data around multiple databases. As it's still in beta, I'm not sure if it's production-ready. As a web site, especially e-commerce sites, are more than just read-repositories for front-end HTML pages, there may be a need for more robust functionality before making the dive.

Running multiple instances of MySQL on the same machine

This may be documented elsewhere on the internet, but I thought I capture my own notes about how to setup and run multiple MySQL instances on the same box. I was playing with this mainly for experimenting with sharding, in particular with the Hibernate Shards project). I'm going to make several assumptions for this discussion: that the databases will, data excluded, be mirror of the other in terms of essential config and schema, that we're running on *nix (I'm using solaris x86), and that we're setting up two MySQL databases.

After the obligatory "download and install", create two directories that will hold all of the database files. For simplicity, I created these directories in the MySQL home directory. Next, and this is the most important part, create two separate MySQL configuration files, one for each database. You only need to specific the unique attributes per instance, which include the directory for files (datadir), socket, port, and the name of the pid file. Here's my first config file:

[mysqld]
datadir=/opt/csw/mysql5/shard0
port=3307
socket=/tmp/mysqld-shard0.sock
pid-file=/opt/csw/mysql5/shard0/pid

And here's my second:

[mysqld]
datadir=/opt/csw/mysql5/shard1
port=3308
socket=/tmp/mysqld-shard1.sock
pid-file=/opt/csw/mysql5/shard1/pid

As you can see, I've just tweaked the values slightly for the second instance.

The next thing to do is setup each database with the proper MySQL master tables and db files. As this is the standard way to setup a database instance, here's the command you need to run twice, one time for each insatnce:

<mysql_home>/bin/mysql_install_db --datadir=/opt/csw/mysql5/shard0
<mysql_home>/bin/mysql_install_db --datadir=/opt/csw/mysql5/shard1

All you need to do now is start up both instances, and you are good to go. The only special thing you need to when starting up is pass the name of the configuration file, like this:

<mysql_home>/bin/mysqld_safe --defaults-file=/opt/csw/mysql5/shard0/shard0.cnf

Note that, and this comes straight from the MySQL documentation, the defaults-file flag must be the first parameter on the command line.

And that's it!