[Resin-interest] Fwd: Resin, MySQL, and the ReplicationDriver

Thu, 05 Apr 2007 14:58:23 -0700 


Begin forwarded message:



From: Shane Cruz
Sent: Thursday, April 05, 2007 12:23 PM
To: '[email protected]'
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:




    <database>

      <jndi-name>jdbc/myDataSource</jndi-name>

      <driver>

        <type>com.mysql.jdbc.ReplicationDriver</type>

        <init-param autoReconnect="true" />

        <init-param autoReconnectForPools="true" />

        <init-param roundRobinLoadBalance="true" />


        <!—Some other insignificant init parameters left out for  
simplicity à


        <password>myPassword</password>


        <url>jdbc:mysql://myMasterHost,mySlaveHost,myMasterHost/ 
myDatabase</url>


        <user>myUsername</user>

      </driver>

      <connection-wait-time>10s</connection-wait-time>

      <max-active-time>600s</max-active-time>

      <max-connections>30</max-connections>

      <max-idle-time>120s</max-idle-time>

      <max-overflow-connections>10</max-overflow-connections>

      <max-pool-time>2400s</max-pool-time>

      <ping>true</ping>

      <ping-interval>60s</ping-interval>

      <ping-table>testTable</ping-table>

      <prepared-statement-cache-size>8</prepared-statement-cache-size>

    </database>




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,

Shane







_______________________________________________
resin-interest mailing list
[email protected]
http://maillist.caucho.com/mailman/listinfo/resin-interest






















					Reply via email to