Maxscale

MariaDB Maxscale

Introduction

Since long HAproxy is the standard loadbalancing solution for people who want a simple userspace daemon for MySQL loadbalancing. However with the availability of Maxscale we have a tool which has been specifically designed to loadbalance for MySQL instances (Galera clusters and/or Master-Slave systems) and offers some attractive features. Performance-wise we have not finished out testing, but first experiments indicate that we do not observe performance regressions from HAproxy to Maxscale.

Maxscale comes with built-in MySQL server node status monitoring, thus no external health check service is required, which simplifies the setup significantly.

Links:

• MaxScale documentation on GitHub: https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Documentation-Contents.md
• Complete list of configuration options: https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Getting-Started/Configuration-Guide.md

Installation

Follow instructions on https://mariadb.com/kb/en/mariadb-enterprise/maxscale-21-installation-guide/ to install the software on your system.

Configuration

Maxscale requires a user for monitoring et al.

CREATE USER 'maxscale'@'loadbalancer.fqdn' IDENTIFIED BY 'sitpavOfEyt5';
GRANT SELECT on mysql.user TO 'maxscale'@'loadbalancer.fqdn';
GRANT SELECT ON mysql.db TO 'maxscale'@'loadbalancer.fqdn';
GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'loadbalancer.fqdn';
GRANT SHOW DATABASES ON *.* TO 'maxscale'@'loadbalancer.fqdn';
GRANT REPLICATION CLIENT on *.* TO 'maxscale'@'loadbalancer.fqdn';
FLUSH PRIVILEGES;

The config file we use is given below. Since OX App Suite has its own built-in read/write separation, we don't need Maxscale's Read-Write Split feature; rather, we configure a Read Service with round-robin semantics, and a Write Service with a persistent active-passive behavior.

Do not confuse the wording Replication Monitor below with OX's builtin Replication Monitor (which we disable for fully synchronous Galera). It is just an identifier for the definition of Maxscale's built-in health check which is in this case checking Galera nodes (module=galeramon).

The Read Service and Write Service are different by their router_options -- effectively we define that for the Read Service all nodes which are synced are available, while for the for the Write Service only the node considered by Maxscale to be the master is being used.

The behavior which node is considered master can be changed by some configuration variables, most notably root_node_as_master which defines that onle the root node, i.e. the node with wsrep_local_index=0 will be used as master node. This setting makes sense if multiple distributed Maxscale instances are to be used, since it avoids the otherwise potential danger that different nodes pick different masters. However if you use only one (active) Maxscale instance per target cluster at a time (maybe with some active-passive HA setup), it might be beneficial to not use root_node_as_master, but rather disable_master_failback to reduce the number of master switches / failovers.

[maxscale]
threads=4

# Server definitions
#
# Set the address of the server to the network
# address of a MySQL server.
#

[db1]
type=server
address=db1.fqdn
port=3306
protocol=MySQLBackend

[db2]
type=server
address=db2.fqdn
port=3306
protocol=MySQLBackend

[db3]
type=server
address=db3.fqdn
port=3306
protocol=MySQLBackend

# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MySQL Monitor documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Monitors/MySQL-Monitor.md

[Replication Monitor]
type=monitor
module=galeramon
servers=db1, db2, db3
user=maxscale
passwd=sitpavOfEyt5
monitor_interval=10000

# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#

# ReadConnRoute documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadConnRoute.md

[Read Service]
type=service
router=readconnroute
servers=db1, db2, db3
user=maxscale
passwd=sitpavOfEyt5
router_options=synced

[Write Service]
type=service
router=readconnroute
servers=db1, db2, db3
user=maxscale
passwd=sitpavOfEyt5
router_options=master

# This service enables the use of the MaxAdmin interface
# MaxScale administration guide:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Reference/MaxAdmin.md

[MaxAdmin Service]
type=service
router=cli

# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#

[Read Listener]
type=listener
service=Read Service
protocol=MySQLClient
port=4306

[Write Listener]
type=listener
service=Write Service
protocol=MySQLClient
port=4307

[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default

Operations

Maxscale offers a command line tool maxadmin offering an impressive set of features for operations.

• https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-21-mariadb-maxscale-administration-tutorial/
• https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-21-maxadmin-admin-interface/