& Continuous Learning

Well this turned out to be quite an exercise.

The goal: scalable reads with MySQL in master-slave configuration, writing to the master, and reading from N slaves, load balanced in round-robin fashion (or something).

The problem: using JPA (Java Persistence API) instead of direct JDBC calls. Turns out the MySQL ReplicationDriver (used to load balance reads to slaves and send writes to the master) relies on the readOnly state of the Connection in order to decide whether it's a read or a write. With direct JDBC calls, I could get the Connection and toggle the readOnly state as needed.

However, buried under JPA and EntityManager and so on, there's no way to do that. So I looked into other solutions (LBPool, for instance), but nothing out there seems to be intended for JPA environment.

So I had to do it myself... in digging around in the MySQL Connector/J source, I discovered the loadbalance option, intended for use w/ MySQL Cluster, because in that environment, you don't need to distinguish between master and slave, they're all just nodes.

The loadbalance option wouldn't work directly for me, of course, because I don't want to load balance writes, just reads.

Solution to the problem is to use two data sources, one for reads, and the other for writes, I can stick the read data source behind all my "fetch stuff" APIs, and use the write data source for everything else. Then I can use the loadbalance option for the read data source, because I know for certain I'm never sending it any writes.

I am currently using Jboss 4.2.1 GA here my mysql-ds.xml

<datasources>
  <no-tx-datasource>
    <jndi-name>MasterA</jndi-name>
    <connection-url>jdbc:mysql://masterDB/databasename</connection-url>
    <driver-class>com.mysql.jdbc.ReplicationDriver</driver-class>
    <user-name>username</user-name>
    <password>password</password>
    <min-pool-size>10</min-pool-size>
    <!-- Don't set this any higher than max_connections on your
         MySQL server, usually this should be a 10 or a few 10's
         of connections, not hundreds or thousands -->
    <max-pool-size>50</max-pool-size>
    <idle-timeout-minutes>10</idle-timeout-minutes>
    <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name>
    <valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>
    <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml -->
    <metadata>
      <type-mapping>mySQL</type-mapping>
    </metadata>
  </no-tx-datasource>
  <no-tx-datasource>
    <jndi-name>SlaveA</jndi-name>
    <connection-url>jdbc:mysql://slaveA,slaveB/databasename</connection-url>
    <driver-class>com.mysql.jdbc.ReplicationDriver</driver-class>
    <user-name>username</user-name>
    <password>password</password>
    <min-pool-size>10</min-pool-size>
    <!-- Don't set this any higher than max_connections on your
         MySQL server, usually this should be a 10 or a few 10's
         of connections, not hundreds or thousands -->
    <max-pool-size>50</max-pool-size>
    <idle-timeout-minutes>10</idle-timeout-minutes>
    <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name>
    <valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>
    <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml -->
    <metadata>
      <type-mapping>mySQL</type-mapping>
    </metadata>
  </no-tx-datasource>
</datasources>

Persistence.xml for the JTA datasources.

<?xml version="1.0" encoding="UTF-8"?>
			<persistence version="1.0"
	xmlns="http://java.sun.com/xml/ns/persistence"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/persistence persistence_1_0.xsd">
<persistence-unit name="Master">
	<provider>org.hibernate.ejb.HibernatePersistence</provider>
	<jta-data-source>
		java:/MasterA
	</jta-data-source>
	<properties>
		<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect" />			
		<property name="hibernate.connection.readOnly" value="true" /> 	</properties>
</persistence-unit>
<persistence-unit name="Slave">
	<provider>org.hibernate.ejb.HibernatePersistence</provider>
	<jta-data-source>
		java:/SlaveA
	</jta-data-source>
	<properties>
		<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect" />			
		<property name="hibernate.connection.readOnly" value="true" /> 	
</properties>
</persistence-unit>
</persistence>

For normal JDBC connections read only state can changed like the following code.

public static void main(String[] args) throws Exception {
    ReplicationDriver driver = new ReplicationDriver();

    Properties props = new Properties();

    // We want this for failover on the slaves
    props.put("autoReconnect", "true");

    // We want to load balance between the slaves
    props.put("roundRobinLoadBalance", "true");

    props.put("user", "user");
    props.put("password", "password");

    //
    // Looks like a normal MySQL JDBC url, with a
    // comma-separated list of hosts, the first
    // being the 'master', the rest being any number
    // of slaves that the driver will load balance against
    //

    Connection conn =
        driver.connect("jdbc:mysql:replication://master,slave1,slave2,slave3/databasename",
            props);

    //
    // Perform read/write work on the master
    // by setting the read-only flag to "false"
    //

    conn.setReadOnly(false);
    conn.setAutoCommit(false);
    conn.createStatement().executeUpdate("UPDATE some_table ....");
    conn.commit();

    //
    // Now, do a query from a slave, the driver automatically picks one
    // from the list
    //

    conn.setReadOnly(true);

    ResultSet rs =
      conn.createStatement().executeQuery("SELECT a,b FROM alt_table");

     .......
  }

Conclusion:

Thus with the help of two data sources I am able to replicate the functionality of Master/Slave Read scalability. If you are using the normal JDBC connection then changing the read-only is as simple as the above code. Hope this will be helpful for someone who needs to setup Replication in the code level.