How to Deploy and Configure MaxScale for SQL Load Balancing with Read-Write Split

There are two models of load balancing: transport and application layer. HAProxy is a great TCP load balancer, but it’s lack of SQL awareness effectively limits its ability to address certain scaling issues in distributed database environments. In the open source world, there’s been a few SQL-aware load balancers, namely MySQL Proxy, ProxySQL and MaxScale, but they all seemed to be in beta status and unfit for production use. So we were pretty excited when the MariaDB team released a GA version of MaxScale earlier this year. In this blog, we’ll have a look at MaxScale and see how it compares with HAProxy.

Deployment
Installation is easy, at least on the latest LTS version of Ubuntu (Trusty, 14.04) which we used for our tests.
Add a public key:

$ apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 8167EE24

Add a MaxScale repository to one of *.list files for apt-get:

deb [arch=amd64] http://downloads.mariadb.com/software/MaxScale/maxscale/DEB trusty main

Run:

$ apt-get update && apt-get install maxscale

and you can enjoy your new software - proxy is installed into the /usr/local/skysql/maxscale directory.

Configuration
Once installed, we need to configure it. Along with installation comes an example configuration file, located in: /usr/local/skysql/maxscale/etc/MaxScale_template.cnf. It gives a nice introduction to the available options, and helps to setup the environment.
MaxScale uses a pluggable architecture with different plugins providing different features. In this post, we will concentrate on the routing part, and for now, leave out other interesting possibilities like query rewriting. MaxScale uses different types of services; monitors, services, listeners and filters.
For our tests we defined two types of routing services:

• ‘router=readwritesplit’, which provides read/write (RW) splitting,
• ‘router=readconnroute’, which provides round-robin-like (RR) kind of access.

Each service was accompanied by a listener, port 3307 for RW split and 3308 for RR service. With RR service, we relied on MaxScale’s monitoring of Galera nodes to route connections only to the nodes in a ‘Synced’ state.

[maxscale]

threads=4

 

[Galera Monitor]

type=monitor

module=galeramon

servers=server1,server2,server3

user=maxmon

passwd=maxpwd

monitor_interval=10000

disable_master_failback=1

 

[qla]

type=filter

module=qlafilter

options=/tmp/QueryLog

 

[fetch]

type=filter

module=regexfilter

match=fetch

replace=select

 

[RW]

type=service

router=readwritesplit

servers=server1,server2,server3

user=root

passwd=secretpass

max_slave_connections=100%

router_options=slave_selection_criteria=LEAST_CURRENT_OPERATIONS

 

[RR]

type=service

router=readconnroute

router_options=synced

servers=server1,server2,server3

user=root

passwd=secretpass

 

[Debug Interface]

type=service

router=debugcli

 

[CLI]

type=service

router=cli

 

[RWlistener]

type=listener

service=RW

protocol=MySQLClient

address=10.69.179.54

port=3307

 

[RRlistener]

type=listener

service=RR

protocol=MySQLClient

address=10.69.179.54

port=3308

 

[Debug Listener]

type=listener

service=Debug Interface

protocol=telnetd

address=127.0.0.1

port=4442

 

[CLI Listener]

type=listener

service=CLI

protocol=maxscaled

address=127.0.0.1

port=6603

 

 

[server1]

type=server

address=10.138.103.93

port=3306

protocol=MySQLBackend

 

[server2]

type=server

address=10.139.81.25

port=3306

protocol=MySQLBackend

 

[server3]

type=server

address=10.81.192.219

port=3306

protocol=MySQLBackend

There are couple of interesting bits in the configuration file. As you can see, we had to define user/password pairs several times. Those users are used to check the health of the MySQL nodes and to get access to the list of users defined in the system. For the sake of simplicity we used plain text passwords but it is possible to use hashed passwords for better security.
Finally, since we wanted to compare performance of MaxScale vs HAProxy, we used HAProxy installed from within ClusterControl in a default setup – configured similarly to MaxScale’s RR service.

How does MaxScale work with Galera Cluster?
So, let’s talk about how MaxScale sees the Galera Cluster. MaxScale provides an admin CLI which gives you access to some internal statistics. After the first login (user admin, password skysql), you can check available options by running the ‘help’ command. One of the very useful commands is ‘show servers’, which returns a health status of the cluster. Below is the example output of that command.

$ /usr/local/skysql/maxscale/bin/maxadmin -u admin

Password:

MaxScale> show servers

Server 0x219bac0 (server1)

    Server:                10.138.103.93

    Status:                       Slave, Synced, Running

    Protocol:            MySQLBackend

    Port:                3306

    Server Version:            5.6.22-72.0-56-log

    Node Id:            2

    Master Id:            -1

    Repl Depth:            0

    Number of connections:        0

    Current no. of conns:        0

    Current no. of operations:    0

Server 0x20f7da0 (server2)

    Server:                10.139.81.25

    Status:                       Slave, Synced, Running

    Protocol:            MySQLBackend

    Port:                3306

    Server Version:            5.6.22-72.0-56-log

    Node Id:            1

    Master Id:            -1

    Repl Depth:            0

    Number of connections:        0

    Current no. of conns:        0

    Current no. of operations:    0

Server 0x20f7c90 (server3)

    Server:                10.81.192.219

    Status:                       Master, Synced, Running

    Protocol:            MySQLBackend

    Port:                3306

    Server Version:            5.6.22-72.0-56-log

    Node Id:            0

    Master Id:            -1

    Repl Depth:            0

    Number of connections:        0

    Current no. of conns:        0

    Current no. of operations:    0

We are interested in the status of the nodes right now – as we can see, we have three nodes ‘Running’, all of them are ‘Synced’. Two were elected as ‘Slave’ and one as a ‘Master’. Those states are what we can use in the configuration file. For example, in RR service we defined the following variable:

router_options=synced

It means that, at any given time, connections can be routed to any of the nodes, as long as they are in the ‘synced’ state (i.e. not serving as a donor or joining the cluster). On the other hand, the RW service was looking for ‘Slave’ and ‘Master’ states to route traffic accordingly. In case of a master failure, a new node is elected as a new master. Your application needs to reconnect though, MaxScale currently does not provide failover for currently open connections.
What’s worth noting, if you want to setup a RW split, you will need to set the max_slave_connections variable accordingly. By default MaxScale sets it to one and, as a result, only one slave is getting read connections. You can here use a fixed number (2, 5) or a percent of the slave pool (50%, 100%). As we wanted all of our slaves, no matter how many there are out there, to serve the traffic, we set this variable to 100%:
max_slave_connections=100%

Another interesting bit is the ‘master’ failover part – when MaxScale detects that a node, elected as a master, is unreachable, it promotes one of the ‘slaves’ to the ‘master’ role. Then, by default, when old ‘master’ comes back online, it is immediately promoted to its old master role. As a result, writes may switch back and forth between different nodes should the ‘master’ start to flap. You can switch this default behavior by adding ‘disable_master_failback=1’ directive to the definition of the monitor service.
In case of any connectivity issues, it’s worth checking log files (by default located in the /usr/local/skysql/maxscale/log/ directory) and the kind of MySQL users MaxScale had detected. The latter can be done from the CLI, using the ‘show dbusers <service>’ command:

MaxScale> show dbusers RW

Users table data

Hashtable: 0x2a0c900, size 52

    No. of entries:         3

    Average chain length:    0.5

    Longest chain length:    7

User names: sbtest@%, maxmon@%, maxmon@%

 

 

 

 If you added some new users on the MySQL side, you can refresh MaxScale’s database by running reload dbusers <service>:
MaxScale> reload dbusers RW
In general, the CLI gives you some nice options to use. We’ll not go over all of them in this post, but we’d still like to mention some features:

• configuration (at least partially) can be changed on fly and reloaded (reload config command)
• server state can be set from the CLI, which enables a DBA to move writes to a different node in the cluster (set server … command)
• services can be disabled/enabled/restarted from the CLI

While in the CLI, help is easily reachable through the following commands:

help

help <command> (for example help show)

 

Stay tuned for part two of this post where we will cover the performance comparison between MaxScale’s different router services and HAProxy.