views:

851

answers:

3

Hello,

I was wondering what the best technique for implementing a DB connection pool for a web application which uses shards. From what I can tell most (all?) open-source implementations only support a single database behind. At least, I have not found one that supports shards.

Also, even though I using shards not all of the database will have the same schema as I will have other databases too. I'm not sure if that's important to mention.

The only solution that I can come up with so far is to write a layer that sits on top of multiple and distinct pools. Each distinct pool can be any of the available single database implementations.

Are there already solutions for this? What would be the best technique otherwise?

Thanks in advance,

Stephen.

+1  A: 

I don't think there is an open-source implementations that support sharding. Maybe, there isn't a real need since creating a layer on top of multiple database pools is not too hard. It only takes a shard mapping function(e.g. hash function) and a manager class to track multiple pools.

If you are worried that not all db have the same schemas, you can put additional schema tracking config into your manager class, so it knows which shards can serve the schema. That's, you need to track schema to shard info in additional to db pool. This is not really much additional work since you need the shard config anyway to determine how to pull the right shard from the pool (e.g. User id mod 10 = 1 should pull from Shard 1)

Good luck

Oscar Chan
This is more or less how I imagined doing it. When I mentioned that not all of the databases will have the same schema I meant that I will have other non-sharded databases too. But I think that the basic concept above is sound. Thanks.
Steve
I must have missed Hibernate Shard. If you are using hibernate already for your shard database, it looks like a good option. Given it is beta, you may want to wait for it to go production first.
Oscar Chan
+1  A: 

What is the technology stack that you are using currently? I know that Hibernate has a sharding project, but I have not used it, just listened to some podcasts about it.

More information about it can be found here. Also, the previously mentioned podcast could be found here.

The podcast explains what a few of the issues with sharding in general, some of the hurtles the Hibernate plugin has taken care of, and then explains their anticipated path forward. Hope that helps a little bit!

jnt30
Thanks. I'll have a good look at Hibernate Shards. At first glance it looks promising.
Steve
+1  A: 

There is the hibernate shards project you could take a look at.

daveb