Connector/J - Load Balancing for a MySQL Cluster

Creating a simple Load Balance client for a MySQL Cluster

There are few key points in using the Connector/J with Load Balancing on MySQL Cluster
- Dynamic JMX with Connector/J

• loadBalanceEnableJMX=true&loadBalanceConnectionGroup=<group>

- Timeout value in the blacklist with Connector/J

• loadBalanceBlacklistTimeout

The following code piece is used on my notebook with MySQL Cluster running on 2 x VMs.  IPs for accessing the 2 MySQL Nodes on the Cluster are 192.168.56.104 and 192.168.56.105.   The port number with the 2 x MySQL Nodes are the same with the value of "3306".

Here is the MySQL Cluster status with MySQL Cluster Manager (MCM)




MySQL Client Code is to firstly create the test.mytable with 2 columns.

1. create table if not exists test.mytable (f1 int not null auto_increment primary key, f2 varchar(200)) engine=ndb;

2. Creating 3 threads running concurrently to insert records totally 3000 times with random wait time.  The execution time is recorded without the wait time.

3. At the end of the execution, the total execution for the 3000 insertion is printed.

Testing Scenarios
1. The loadBalanceBlacklistTimeout is not configured.  That means it is 0.   When the execution is started, one of the MySQL instance is stopped.

2. The loadBalanceBlacklistTimeout is configured to be 60000 - (60 seconds).  When the execution is started, one of the MySQL instance is stopped.

Scenario	(1) loadBalanceBlacklistTimeout=0	(2) loadBalanceBlacklistTimeout=60000
Execution Time (ms)	7,513,017	131,074

With loadBalanceBlacklistTimeout configured, it is obviously the the execution time will not be affected even though there is a failed server. 



Using loadBalanceEnableJMX

The property "loadBalanceEnableJMX=true" is useful with the JMX
Once the JMX is enabled, the JConsole or JMX browser is able to lookup the jmx properties on the Client Application.

Expanding the Operations and selecting the "getActiveHostsList".  The parameter is replaced with the loadBalanceConnectionGroup setting which is 'first' in my case.  It returns the list of the hosts (MySQL Server with port number).

---

---

Client Code
package mysql.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class LBDemo01 {
   
    private static long ttltime=0;
    private static String driver = "com.mysql.jdbc.Driver";
    private static String database = "test";
    private static String baseUrl =   "jdbc:mysql:loadbalance://" +
            "192.168.56.104:3306,192.168.56.105:3306/" + database + "?" +
            "loadBalanceConnectionGroup=first&loadBalanceEnableJMX=true";
    private static String user = "root";
    private static String password = "";

    public static void main(String[] args) throws Exception {

        createTable();
       
        ArrayList threads = new ArrayList(10);;
        for (int i=0;i<3;i++) {
            Thread t = new Thread(new Repeater());
            t.start();
            threads.add(t);
        }
       
        System.out.println("Spawned threads : " + threads.size());
        for(int i=0;i<threads.size();i++) {
            ((Thread) threads.get(i)).join();
        }
        System.out.println("Finished - " + ttltime);
       

    }
   
    private static void createTable() throws ClassNotFoundException, SQLException {
        Connection c = getNewConnection();
       
         try {
                c.setAutoCommit(false);
                Statement s = c.createStatement();
                s.executeUpdate("create table if not exists test.mytable (f1 int auto_increment not null primary key, f2 varchar(200)) engine=ndb;");
                c.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        c.close();
   
    }
   
    static Connection getNewConnection( ) throws SQLException, ClassNotFoundException {
        java.util.Properties pp = new java.util.Properties();
       
        pp.setProperty("user", user);
        pp.setProperty("password", password);
        // black list for 60seconds
        pp.setProperty("loadBalanceBlacklistTimeout", "60000");
        pp.setProperty("autoReconnect", "true");
        Class.forName(driver);
        return DriverManager.getConnection(baseUrl, pp);
    }
   
    static void executeSimpleTransaction(Connection c, int conn, int trans){
        try {
            c.setAutoCommit(false);
            Statement s = c.createStatement();

            s.executeUpdate("insert into test.mytable (f2) values ('Connection: " + conn + ", transaction: " + trans +"');" );
            c.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
   
     public static class Repeater implements Runnable {
            public void run() {
                for(int i=0; i < 100; i++){
                    try {
                        Connection c = getNewConnection();
                        long mystart, myend, myttl=0;
                        for(int j=0; j < 10; j++){
                            // To register the start time
                            mystart = System.currentTimeMillis();
                            executeSimpleTransaction(c, i, j);
                            // To time the execution time and save it onto the totaltime
                            myend = System.currentTimeMillis();
                            myttl += (myend - mystart);
                            incTTL(myttl);
                           
                            Thread.sleep(Math.round(100 * Math.random()));
                        }
                       
                        c.close();
                        Thread.sleep(200);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }
   
     public synchronized static void incTTL(long m) {
         ttltime += m;
     }
}