some input / guidance wanted

[Aleksander M. Stensby]

Hello everyone! I have tried the hibernate forums but it seems that
the shard activity is rather low, and i noticed that there was a reply
on this group some days ago, so I thought I'd give it a shot :)

I'm currently considering the use of Hibernate Shards in our system.
We are at the moment using a semi-sharding approach through the use of
the AbstractRoutingDataSource (the approach that Fisher talks about
here: http://blog.springsource.com/main/2007/01/23/dynamic-datasource-routing/).
This has worked "ok" for us but it is rather poor with regard to
scalability. As our DB is growing rather rapidly we are in need for
new ways to partition our data and are therefore looking at Shards. In
addition we are looking at mysql partitioning in combination with
mysql cluster.

I have rather limited knowledge about shards, but enough to understand
the basics. The sharding scenario I belive is best for us is to
partition based on the entry id, since this is the number one criteria
we use in our application stack.

Conseptually, let's say that we shard on entry id into shards of size
10 million entries. (or something like that). Then shard 1 will have
posts with ID 1 to 10 million, and so on.

So, this is where i get stuck... We are using MySQL and my initial
thoughts were to read the highest ID when building the factory, then
creating an atomic integer to keep track of the next id value. Then
use this value in the shard selection strategy to determine the shard
where the object is to be created. (simply by taking the nextID /
entriesPerShard).
Problem with this is that using mysql and auto increment is not safe
with regard to transactions that may be rolled back. The auto
increment counter is not rolled back and thus my approach is flawed
since it may result in posts with higher ID than the intended 10
million are put into the first shard and so on...

One could use assigned Id's but i recon this might lead to trouble...
What do you guys suggest that I do? For me, this appears to me as a
rather trivial way of sharding, and it most probably is... maybe I'm
just blinded here...


Thanks for any responses!
Best regards,
Aleksander M. Stensby