Connector/J load balancing with Galera cluster

Table of contents

Preamble

After I set up my Galera cluster I wanted to test it from a Java application point of view (Java is the standard for applications development where I currently work) and get prepared to fight with issues like when I was testing Connector/J with MySQL Replication. This time except a small blocking point it went very well and Connector/J load balancing feature is quite straightforward and powerful.

This blog post has been written using MariaDB Galera Cluster 5.5.34 Stable running on Oracle Linux Server release 6.5. For Java application I have used Eclipse as development interface and Connector/J 5.1.28.

Even if not suggested as a standard Galera deployment I’m using a two nodes cluster:

  1. First cluster node is called server1.domain.com and has 192.168.56.101 as non-routable IP address
  2. Second cluster node is called server2.domain.com and has 192.168.56.102 as non-routable IP address

Testing

I start with the below test table:

MariaDB [(none)]> drop database test;
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [(none)]> create database test character set=utf8 collate=utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
MariaDB [test]> CREATE TABLE test1(id int, descr varchar(50), primary key (id));
Query OK, 0 rows affected (0.08 sec)
 
MariaDB [test]> insert into test.test1 values(1,1);
Query OK, 1 row affected (0.00 sec)

I have also used the below Java code:

package jdbcdemo2;
 
import com.mysql.jdbc.Driver;
import com.mysql.jdbc.jmx.LoadBalanceConnectionGroupManager;
 
import java.sql.ResultSet;
import java.util.Properties;
import java.sql.Connection;
import java.sql.SQLException;
 
public class jdbcdemo2 {
  public static void main(String[] args) throws Exception {
    Driver driver = new Driver();
    Properties props = new Properties();
    ResultSet rs;
    LoadBalanceConnectionGroupManager jmx1 = new LoadBalanceConnectionGroupManager();
    String variable_value;
    Connection conn = null;
    int count;
    String JDBC_URL = "jdbc:mysql:loadbalance://192.168.56.101:3316,192.168.56.102:3316";
 
    props.put("loadBalanceConnectionGroup","galera_cluster01");
    props.put("loadBalanceEnableJMX","true");
 
    props.put("user", "yjaquier");
    props.put("password", "secure_password");
 
    System.out.println("\n------------ MySQL Connector/J and MySQL Load Balance Testing ------------\n");
 
    try {
    	Class.forName("com.mysql.jdbc.Driver");
    	}
    catch (ClassNotFoundException e) {
		  System.out.println("Where is your MySQL JDBC Driver?");
		  e.printStackTrace();
		  return;
	  }
 
    System.out.println("MySQL Connector/J driver loaded");
 
    System.out.println("Trying connection...");
 
    try {
    	conn = driver.connect(JDBC_URL, props);
    	conn.setAutoCommit(false);
		}
    catch (SQLException e) {
    	System.out.println("Connection Failed! Check output console");
    	e.printStackTrace();
    	return;
    	}
 
    System.out.println("Connection established...");
 
    System.out.println("Number of servers in cluster: "+jmx1.getActiveHostCount("galera_cluster01"));
    System.out.println("Server names(s) in cluster: "+jmx1.getActiveHostsList("galera_cluster01"));
 
    for(int i=1; i <= 1000; i++) {
    	System.out.println("\nQuery "+i+": ");
	try {
		rs = conn.createStatement().executeQuery("SELECT variable_value FROM information_schema.global_variables where variable_name='hostname'");
			}
		catch (SQLException e) {
			System.out.println("Select has failed...");
			System.out.println("Error cause: "+e.getCause());
			System.out.println("Error message: "+e.getMessage());
			continue;
		}
		while (rs.next()) {
			variable_value = rs.getString("variable_value");
			System.out.println("Working on node : " + variable_value);
		}
		rs.close();
 
		System.out.println("Inserting in test table...");
		try {
			count = conn.createStatement().executeUpdate("insert into test.test1 select max(id)+1,max(id)+1 from test.test1");
		}
		catch (SQLException e) {
			System.out.println("Insert has failed...");
			System.out.println("Error cause: "+e.getCause());
			System.out.println("Error message: "+e.getMessage());
			continue;
		}
 
		try {
			conn.commit();
		}
		catch (SQLException e) {
			System.out.println("Error cause: "+e.getCause());
			System.out.println("Error message: "+e.getMessage());
			System.out.println("Commit has failed...");
			continue;
		}
		System.out.println("Commit succeeded... :-)");
		Thread.sleep(2000);
    }
  }
}

This small Java code is inserting a row in my tst table and what I’m expecting is Connector/J to load balance the queries on all my cluster nodes. The failover scenario should ba handle automatically i.e. if one cluster node is down the surviving node should ahndle the workload wit no further Java issue. When back to life the problematic node should be used again with no intervention…

Notes:

  • You must set autocommit to false unless Connector/J does not load balance the queries on all nodes. Even if strange it is written in official Connector/J documentation:

    While the three fail-over conditions enumerated earlier suit most situations, if autocommit is enabled, Connector/J never re-balances, and continues using the same physical connection.

  • I have enabled JMX-based management of load-balanced connection groups not to live add/remove nodes from load-balancing pool but to be able to display that my Connector/J has taken into account all nodes of my cluster and display number of used nodes.

All the tests went very well:

  • When all nodes are up and running the queries are load balanced on all cluster nodes, it’s not one different each time but at least load balancing works.
  • If one node is stopped (or fail) then the remaining node is taking the load and when failed node is restarted then the Java application is using it almost automatically.

A small screenshot of my Java application running in Eclipse:

loadbalance1
loadbalance1

When enabling JMX implementation you can also connect to your application using JConsole and dynamically change or display some properties:

loadbalance2
loadbalance2

To get for example the list of active hosts:

loadbalance3
loadbalance3

References

2 thoughts on “Connector/J load balancing with Galera cluster

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>