Hey guys,

I'm in the process of replacing a single mysql server by two replicated hosts.

To get more performance we've chosen to have the slave server answer queries (as opposed to updates). So basically I have master : answers queries and updates (select, update, insert...) slave: answers queries

I'm looking for a bit of help about how to implement this scheme in our jdbc client code (servlets).

Here are the basic ideas I'm considering:

- Have 2 db pools, one for read only connections, one for read/write connections. Ensure the code uses connections from the proper pool, ie. uses executeUpdate only on statements created from Connections got from the writePool. This is going to work but is error prone, and I guess a single mistake could break things in a very bad fashion

- Modify Connector/J. Basically each connection would be able to create 2 actual db connections (one to the master, one to the slave), and would select the connection to use depending on the operation to run (query or update). I guess this could work, but I'd like to know what you think (mark ?)

I would very much appreciate any comment/idea/hint ... especially about the 2nd part :)

Thanks to you all and happy new year :)