0001672: jdbc pool enhancements

Viewing Issue Simple Details [ Jump to Notes ]

[ View Advanced ] [ Issue History ] [ Print ]

Category

Severity

Reproducibility

Date Submitted

Last Update

0001672

[Resin]

minor

always

04-05-07 14:57

06-14-07 17:03

Reporter

ferg

View Status

public

Assigned To

ferg

Priority

normal

Resolution

fixed

Status

closed

Product Version

Summary

0001672: jdbc pool enhancements

Description

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).

0000002 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.

Additional Information

Attached Files

Relationships

Notes
(0002053) ferg 06-14-07 17:03	server/14j{0,1,2,3}

Issue History
Date Modified	Username	Field	Change
04-05-07 14:57	ferg	New Issue
06-14-07 17:03	ferg	Note Added: 0002053
06-14-07 17:03	ferg	Assigned To	=> ferg
06-14-07 17:03	ferg	Status	new => closed
06-14-07 17:03	ferg	Resolution	open => fixed
06-14-07 17:03	ferg	Fixed in Version	=> 3.1.2
06-14-07 17:03	ferg	Description Updated

Mantis 1.0.0rc3[^]

30 total queries executed.
26 unique queries executed.