This is my first project with Hibernate and so far I'm reasonably pleased. I've run into a few snags and it's taken a bit of time to learn the 'hibernate' way but I certainly embrace not having to write my own SQL anymore!

Anyways, one topic I haven't seen addressed much is just how to make Hibernate scale horizontally across machines and DB's. One solution is to use a clustered DB solutions such as MySQL Cluster, but I've had very bad experience with MySQL cluster's stability.

I'd much rather use replication and use a master/slave pattern, since as with most web apps, mine is mostly read and rarely write.

How is this addressed in Hibernate?

MySQL supports it's own master/slave DB connection:
http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-replication-connection.html

But I'm not sure how to plug this into c3p0 and Hibernate. What is the typical pattern to mark a connection as not read only and therefore that it should use the master DB and not one of the replicated slaves?

If this isn't the typical path, what is? Is everybody just living under the assumption that their app will always scale with one machine and DB and sticking their head in the sand with respect to high availability?

Please say it ain't so! :)

Thanks,

-Nic

300 views and not a single reply?

Is it actually the case that Hibernate just doesn't support this? Big hardware is the only way?

-Nic

Nicpottier,

I've been facing a similar problem... I had intended on using MySQL Cluster (which works well) with Hibernate, however the problem of the cluster's lack of foreign key support has me re-thinking the solution.

My suggestion is to implement UltraMonkey (heartbeat/ldirector) to facilitate virtualized ip and load balancing, against a multiple master (circular replication) setup.

This way, you get High Availability, Data Redundancy (asynchronous), and the possibility to script automatic fail over (see this article: http://www.onlamp.com/pub/a/onlamp/2006 ... ation.html). You then avoid having to code DB logic into the app about which server(s) to write against and which server(s) to read from.

N.B. As you add nodes to this type of setup, you risk possible performance impacts as each node has to have it's changes replicated across all other nodes.

Good Luck!

razorpointer,

Check this link:
http://dev.mysql.com/tech-resources/articles/mysql-enforcing-foreign-keys.html

Talks about using triggers in mysql cluster to make up for the lack of foreign keys. That's what we're currently planning on doing. We're moving away from a mysql master/slave cluster ... basically as part of our move to hibernate. Talked to a couple of DBAs about the trigger thing.. they think it should work very well, that it's basically what happens behind the scenes anyway.

--
Marshall