cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <>
Subject Re: read-write splitting and load balancing databases
Date Mon, 26 Mar 2012 13:00:57 GMT
We are using this extensively, and in even more streamlined fashion - each web container instance
is designated as read or read/write, and configured with appropriate DataSource URL. Just
to make sure the apps do not write to slaves due to some code bugs, we also create 2 separate
MySQL accounts, one with read-only permissions. 

Routing is done via a load balancer external to the appserver, so we don't have to use MySQL
driver replication features (and as a result I am not very familiar with driver failover behavior).
Don't remember whether this is a DNS or a full proxy balancer. But either should do the trick
I guess.

Validation query should do the trick to close already opened connections to a failed DB (although
some tweaking of the pool parameters is needed). And of course if the pool tries to open a
connection against a failed DB, it will fail, and it will attempt to reconnect before returning
a connection to the user. 

If you use the load balancer approach, the load balancer may itself detect a server failure,
and route all following requests to the available servers.


On Mar 26, 2012, at 2:29 PM, Aristedes Maniatis wrote:

> Has anyone tried to split read and write commands across databases in a pool? We are
using tomcat and we have several requirements:
> 1. Balance requests across two mysql databases (master/slave) for an application which
is read-only. We could do that within the mysql JDBC adapter (jdbc:mysql:replication), within
tomcat (not sure how but there is some pooling mechanism) or within Cayenne itself (with some
code to route requests).
> 2. Same as the above but for an application which is read-write. So read-write requests
must go to the master.
> Right now, I am more concerned about (1) and about the ability for the app to automatically
stop using a database which has failed. The tomcat connector has validationQuery, but I'm
not sure how this applies across multiple databases in this way.
> Has anyone tried to do anything like this?
> Cheers
> Ari
> -- 
> -------------------------->
> Aristedes Maniatis
> ish
> Level 1, 30 Wilson Street Newtown 2042 Australia
> phone +61 2 9550 5001   fax +61 2 9550 4001
> GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A

View raw message