I talked yesterday with Cory Isaacson of CodeFutures, and hence can follow up on my previous post about dbShards. dbShards basics include:
- dbShards gives you, in effect, an MPP DBMS based on MySQL or PostgreSQL, meant for OLTP (OnLine Transaction Processing). dbShards always did distributed queries, and now does distributed transactions as well.
- dbShards works by sharding the database and automagically sending work to the correct shard.
- For safety, dbShards of course replicates each shard. Contrary to what I said in the previous post, the replication method is not log-shipping.
- At this time, dbShards only works in a single data center.
- dbShards can handle any SQL that would work through, say, a JDBC driver, and is not particularly sensitive to data type. However, dbShards’ stored procedure support is iffy — if a procedure touches data in more than one shard, it simply fails.
One dbShards customer writes 1/2 billion rows on a busy day, and serves 3-4,000 pages per second, naturally with multiple queries per page. This is on a 32-node cluster, with uninspiring hardware, in the cloud. The database has 16 shards, aggregating 128 virtual shards. I forgot to ask how big the database actually is. Overall, dbShards is up to a dozen or so signed customers, half of whom are in production or soon will be.
dbShards’ replication scheme works like this:
- A write initially goes to two places at once — to the DBMS and a dbShards agent, both running on the same server.
- The dbShards agent streams to the dbShards agent on the replica server, and receipt of the streamed write is acknowledged.
- At that point the commits start. (Cory seemed to say that the commit on the primary server happens first, but I’m not sure why.)
In essence, two-phase database commit is replaced by two-phase log synchronization. Once it’s known that the transaction logs on two machines agree, the underlying DBMS is responsible for getting each of the databases updated.
Edit: A subsequent post clarifies dbShards’ shard replication.
Cory added that dbShards now uses the same mechanism to do distributed transactions. As an example of why somebody in the dbShards target market would care, he cited a game company that shards according to player_ID. If two players interact with each other, a distributed transaction could ensue.
Cory stresses that dbShards’ sharding is configurable, as opposed to being an all-or-nothing black box. That is, you can configure which tables are sharded; the rest are replicated across all the servers (which is of course highly beneficial to JOIN performance); and the whole thing works automagically. There also are true parallel queries when needed, along with the distributed transactions.
As for how dbShards shards, there are three methods.
- Most common is a hash table on the shard key.
- The other somewhat common one is what Cory calls “session-based” sharding, but which I might prefer to call “value-list sharding.” If you deliberately put users from the same geography into the same shard, that would be list-based. Ditto if you shard by tenant in a multi-tenancy use case.
- dbShards also can be used for time-based sharding. While Cory didn’t say so, I imagine that could be extended to other value-range scenarios as well.