Begin forwarded message:
From: Shane Cruz
Sent: Thursday, April 05, 2007 12:23 PM
Subject: Resin, MySQL, and the ReplicationDriver
We are currently using Resin Pro 3.0.23, MySQL Community 5.0.37,
and have master/slave databases setup. The MySQL replication is
working perfectly and now we are trying to configure the Connector/
J ReplicationDriver to send all writes to the master and load
balance all read-only requests between the two database servers.
Here is how we are configuring the connection pool:
<init-param autoReconnect="true" />
<init-param autoReconnectForPools="true" />
<init-param roundRobinLoadBalance="true" />
<!—Some other insignificant init parameters left out for
The ReplicationDriver basically round-robins the slave database
that is used when a new connection is created. In the JDBC URL,
the first server is the master and anything after it is a slave.
Therefore, if there are 20 connections in the pool, 10 of them
would read from the first slave (mySlaveHost) and 10 of them would
read from the second slave (myMasterHost). Each connection
actually is a container (ReplicationConnection) that holds a
connection to the master and a connection to one of the slaves.
What we are noticing is that all of the writes are correctly going
to the master, but the slave is getting almost every read (90% or
more). If I switch the host order in the URL to be
myMasterHost,myMasterHost,mySlaveHost, then the master essentially
gets all the traffic. Basically, it looks like the first slave
listed in the URL is getting almost all the read-only queries and
they are not getting correctly load balanced.
I did some looking around and debugging in the Connector/J code and
also in the Resin source and here is what I found:
If I get 10 connections from the pool at once (without returning
any other them before getting another one), the connections are
balanced perfectly (half to each database server). This tells me
that the Connector/J driver is doing what it is supposed to do and
using a round-robin algorithm when creating new connections.
If I get and return 10 connections in a row (returning the
connection to the pool before getting the next one), I always get
the same connection (same physical connection/object to the same DB).
#2 is the source of our problems that leads to an uneven balance of
read-only requests. Looking at the com.caucho.jca.ConnectionPool
class, it looks like Resin uses a LIFO stack for the idle
connections and that is why we almost always get a connection to
the same server. Has anyone encountered a problem like this using
Resin’s connection pools? Are there any good workarounds? We
could be more aggressive with the max-idle-time setting to make
sure that new connections get created more frequently (giving a
better chance of evening out the load balancing), but it would
result in more connections being opened and worse performance. It
would be great if Resin had some more configurability around the
database pool to allow us to specify how to pull a connection from
the pool (LIFO, FIFO, random, etc.). I would like to tell it to
open 20 connections right away and randomly pull a connection from
the pool. That would give us a pretty random distribution.
Are there any other suggestions on how to accomplish this?
Thanks for your help,
resin-interest mailing list