H2 Clusters- load balancing SELECT querries

[Adam McMahon]

H2 Group,

I am checking out the clustering features of H2.  I know that the clustering features are fairly barebones.  I am  interested in using clusters/replication for increased performance, as opposed to simply having a failsafe DB online (which is also a good thing  :). 

In the past, using MySQL replication, I would send write queries to the master (which would eventually be propogated), and then I would load balance between the master/slaves for read queries.  I would do this at the application level by simply changing the JDBC url depending on if the SQL was a write or a read.  The slaves might be a little behind the master, but that was fine for many of my queries.

I know that load balancing is not a built in feature to H2 clustering, but I figured I may be able to accomplish it at the application level using something like the above.  However, it seems that I am not able to do so, as client connections to an individual server with in a cluster are rejected.  I appears we can only connect to using the server list url.

My question: is there any way to load balance reads across a slave? 

Thanks,  -Adam

[Thomas Mueller]

Hi,

I'm afraid the current implementation does not support it. Of course this could be changed, actually I guess it would be relatively easy to remove the check.

At some point I think it's better to re-implement clustering. But first my priority is to work on the new storage engine (the MVStore), I plan to use this mechanism as the base for the new clustering.

Regards,
Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

[Adam McMahon]

Thomas,

This sounds like a good plan, and I realize there is a strong priority for the MVStore. 

When I look at the other major DBs: MySQL, Mongo, etc.  They all seem to have a feature to allow replication for faster reads. While H2 may fit a different market than these other DBs, but it would be nice to have have replication performance boosts.

For my particular use case, I use H2 for rest/web services (similar to how many people use MySQL/Mongo).  Here is my scalability flow/plan:

1) For for most services, I simply embed H2 in the web server - this works great, simple and super fast.

2) If the volume on the services gets too high and heaps sizes are too big, H2 is split to client/server mode on the same CPU

3) If the volume continues to increase, then H2 can be put on a dedicated server.

4) But what next?  there is no way to scale higher via a cluster.  Because of this fact, individuals may pick another DB (which is fine if H2 does not want to compete in this space).

Another potential problem of H2 scalability is that of large garabage collection pauses on large heap sizes.  This is not a problem with H2, but a problem with Java in general.  If someone has a dedicated H2 server and wants to scale higher, they may not get much more power by increasing H2's cache, as I imagine it will (like most Java apps) run into problems with large heap sizes.  So, really H2 will likely cap its scalabilty on a commodity computer with about 4 GB of ram (if the user is concerned with having low latency for requests).

Cluster replication might just save the day here.  When volume of requests or heap sizes get too big, replication can be used to scale horizontally.  But then again, perhaps I am looking at H2 to do something that it is not intended to do, and where another DB may be a better fit, which is fine.

Thanks for your hard work on this great product.

-Adam

[Thomas Mueller]

Hi,

Yes, currently H2 does not offer real scalability (no scalable writes or reads) beyond one process. The current clustering feature is very limited. I do think about adding sharding and replication (basically what MongoDB is doing) on the storage level (MVStore). But this will take a while (not this year, and I'm not sure about next year).

Regards,

Thomas