This guide will cover:

• The load balancing and high availability concepts in Mariadb Connector/J.
• The different options.

Load balancing and failover distinction

Failover occurs when a connection to a primary database server fails and the connector opens up a connection to another database server.
For example, server A has the current connection. After a failure (server crash, network down …) the connection will switch to another server (B).

Load balancing allows load (read and write) to be distributed over multiple servers.

Replication cluster type

In MariaDB (and MySQL) replication, there are 2 different replication roles:

• primary role: Database server that permits read and write operations
• replica role: Database server that permits only read operations

This document describes configuration and implementation for 3 types of clusters:

• Multi-primary replication cluster. All hosts have a primary role. (example: Galera)
• Primary/replicas cluster: one primary host with one or multiple replicas.
• Hybrid cluster: multiple primary hosts with one or multiple replicas.

Load balancing implementation

Random picking

When initializing a connection or after a failed connection, the connector will attempt to connect to a host with a certain role (primary/replica). The connection is selected randomly among the valid hosts. Thereafter, all statements will run on that database server until the connection will be closed (or fails).

The load-balancing includes a pooling mechanism. Example: when creating a pool of 60 connections, each one will use a random host. With 3 master hosts, the pool will have about 20 connections to each host.

Primary/replica distributed load

For a cluster composed of primary and replicas on connection initialization, there will be 2 underlying connections: one with a primary host, another with a replica host. Only one connection is used at a time.
For a cluster composed of primary hosts only, each connection has only one underlying connection.
The load will be distributed due to the random distribution of connections..

Primary/replica connection selection

It’s the application that has to decide to use primary or replica connection (the primary connection is set by default).
Switching the type of connection is done by using JDBC connection.setReadOnly(boolean readOnly) method. Setting read-only to true will use the replica connection, false, the primary connection.

Example in standard java:

connection = DriverManager.getConnection("jdbc:mariadb:replication://primary1,replica1/test");
stmt = connection.createStatement();
stmt.execute("SELECT 1"); // will execute query on the underlying primary1 connection
connection.setReadOnly(true);
stmt.execute("SELECT 1"); // will execute query on the underlying replica1 connection

Some frameworks render this kind of operation easier, as for example Spring @transactionnal readOnly parameter (since spring 3.0.1). In this example, setting readOnly to false will call the connection.setReadOnly(false) and therefore use the master connection.

@Autowired
private EntityManager em;

@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
public void createContacts() {
  Contact contact1 = new Contact();
  contact1.setGender("M");
  contact1.setName("JIM");
  em.persist(contact1);
}

Generated Spring Data repository objects use the same logic: the find* method will use the primary connection, other use primary connection without having to explicitly set that for each method.

On a cluster with primary hosts only, the use of connection.setReadOnly(false/true) won't have any impact.

Failover behaviour

When a failover/high availability parameter is set. Check the configuration section for an overview on how to set the parameters.

There can be multiple fail causes. When a failure occurs many things will be done:

• connection recovery
• re-execute command if possible

During failover, the fail host address will be put on a blacklist (shared by JVM) for 60 seconds.The only time a blacklisted host can be used is if all hosts of the same type (primary/replica) are blacklisted.

Failover on replica connection

(connection.setReadOnly(true) was called)

If driver fails to recover connection, and connection was a replica, driver will try to connect to another replica if any and reexecute the command. If replica reconnection fails, driver will temporary use the primary connection, reexecuting the command on the primary connection, silently ignoring the error. driver won't try to reconnect to replica for 30s.

Failover on primary connection

The driver will try to reconnect to any valid host (not blasklisted, or if all primary host are blacklisted trying blacklisted hosts). If reconnection fail, an SQLException with be thrown with SQLState "08XXX". If using a pool, this connection will be discarded.

on successful reconnection, there will be different cases.

If driver identify that command can be replayed without issue (for example connection.isValid(), a PREPARE/ROLLBACK command), driver will execute command without throwing any error.

Driver cannot transparently handle all cases : imagine that the failover occurs when executing an INSERT command without a transaction: driver cannot know that command has been received and executed on server. In those case, an SQLException with be thrown with SQLState "25S03".

option `transactionReplay` :

Most of the time, queries occurs in transaction (ORM for example doesn't permit using auto-commit), so redo transaction implementation will solve most of failover cases transparently for user point of view.

Redo transaction approach is to save commands in transaction. When a failover occurs during a transaction, the connector can automatically reconnect and replay transaction, making failover completely transparent.

There is some limitations :

• driver will buffer up to option `transactionReplaySize` value (default 64) commands in a transaction
• huge command will temporary disable transaction buffering for current transaction.
• commands must be idempotent only (queries can be "replayable")

Configuration

(See About MariaDB java connector for all connection parameters) JDBC connection string format is:

jdbc:mariadb:[replication:|sequential:|loadbalance:]//<hostDescription>[,<hostDescription>...]/[database][?<key1>=<value1>[&<key2>=<value2>]...]

The standard option "connectTimeout" defines the socket connection timeout. By default, this option is set to 30s.
Since there are many servers, setting this option to a small amount of time make sense.
During the reconnection phase, the driver will try to connect to the hosts sequentially until the creation of an active connection. Set this option to a small value (such as 2000ms - to be set according to your environment) which will permit rejecting a faulty server quickly.

Failover and Load Balancing Modes

Each parameter corresponds to a specific use case: