A Close Encounter with MaxScale

MaxScale is the new proxy server from the SkySQL/MariaDB team. It provides Connection Load Balancing (CLB) and Statement Load Balancing (SLB) out of the box. This post is a [relatively] quick “how to” install, configure and test SLB with the read/write splitting module.

Step 1 – Server preparation

If you do not have many HW resources, you may run everything on a single Linux instance, but the best way to test MaxScale is to use at least 4 servers: one for MaxScale and for the client apps, one as Master and two as slaves – so, 4 in total. In this post I am going a bit further, I will use 5 servers:
Max 0 – For client apps (192.168.56.20)
Max 1 – The master server (192.168.56.21)
Max 2 – The first slave (192.168.56.22)
Max 3 – The second slave (192.168.56.23)
Max 4 – The third slave (192.168.56.24)
Max 6 – The MaxScale server (192.168.56.26)

In order to do proper tests (i.e. you may want to test performance and read scalability), bare metal servers are recommended, but just for a taste of how you can use MaxScale, 5 VMs or instances on your favourite cloud provider will suffice.
Because of my nomadic behaviour, I have prepared everything on my laptop using VirtualBox. I have created one VM with CentOS 6.5, using 512MB RAM and I have now 5 copies of the same machine. The IP addresses assigned in this test are listed above within brackets.

If you use VMs, make sure they are in the same network and they can connect to each other (in VirtualBox, if you use a host-only network, you should set Allow All in the Promiscous Mode setting. If you use a host-only adapter, you should also have a NAT or a Bridge adapter to connect to the Internet and download the necessary packages.

Adapter 1 is used to connect to the internet and download the packages needed for the test:

Adapter 2 is used to connect to the other VMs with a fixed IP address:

Step 2 – Installation

First of all, you must install your favourite version of MySQL/MariaDB/Percona on your servers. In this test, I used MariaDB 10.0.7, downloaded from a yum repo. You can refer to https://downloads.mariadb.org/mariadb/repositories to find out the right repository and download the package, or simply go to this page for the other downloads.
In this test, the repo file is:
[root@Sky0 ~]# cat /etc/yum.repos.d/MariaDB.repo
# MariaDB 10.0 CentOS repository list - created 2013-12-31 11:11 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
The software installed on the 5 VMs is listed here:

On Max 0:

  • MariaDB Client
[root@Max0 ~]# yum install MariaDB-client

On Max 1 to 4:

  • MariaDB Client
  • MariaDB Server
[root@Max1 ~]# yum install MariaDB-server MariaDB-client

On Max 6:

  • MaxScale
[root@Max6 ~]# curl https://downloads.skysql.com/files/SkySQL/MaxScale/maxscale.preview.0.4.tar.gz > maxscale.preview.0.4.tar.gz
[root@Max6 ~]# cd /usr/local/
[root@Max6 local]# mkdir skysql
[root@Max6 local]# cd skysql
[root@Max6 skysql]# tar xzvf ~/maxscale.preview.0.4.tar.gz
maxscale/
maxscale/Documentation
/maxscale/Documentation/MaxScale Configuration And Usage Scenarios.pdf
...
maxscale/SETUP
[root@Max6 skysql]#
If you prefer to compile your own version of MaxScale, you can get the source code from GitHub.

Step 3 – Configuration

Max 1 to 4 must run MariaDB Replication, with Max 1 as master and Max 2, 3 & 4 as slaves.
The configuration file should have the server-id and the binary log setup.
[root@Max6 ~]# tail -5 /etc/my.cnf.d/server.cnf 
[mariadb-10.0]
server-id=1   <<<— and 2, 3 & 4 for Max 2, Max 3 and Max 4
log-bin
Start the four servers in the usual way:
[root@Max6 ~]# /etc/init.d/mysql start
Some DB users are needed for Replication and for MaxScale. In this case we have:
  • repluser – for MariaDB Replication
  • maxuser – generic user for MaxScale
  • maxmon – user for the MaxScale Monitoring module
In this test we do not care much about security, we simply create the users as:
 MariaDB [test]> create user repluser identified by 'maxpwd';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [test]> grant replication slave on *.* to repluser@'%';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [test]> create user maxuser identified by 'maxpwd';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [test]> grant all on *.* to maxuser@'%';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [test]> create user maxmon identified by 'maxpwd';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [test]> grant replication client on *.* to maxmon@'%';
Query OK, 0 rows affected (0.00 sec)
Now you can set Replication between Max 1 and the other three servers.
On Max 2, 3 and 4 you can execute these two commands:
MariaDB [(none)]> change master to master_host='192.168.56.21',
                  master_port=3306,
                  master_user='repulser',
                  master_password='repluser',
                  master_use_gtid=slave_pos;
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [(none)]> start slave;
 
Query OK, 0 rows affected (0.01 sec)
On Max 6, the most important step is to create the MaxScale configuration file. The file is located by default in $MAXSCALE_HOME/etc, in our test is /usr/local/skysql/maxscale/MaxScale/etc and the file is MaxScale.cnf.
The file has various sections.
MaxScale Section
This is a generic section. for the moment we simply tell MaxScale to use a single thread (for users)
[maxscale] 
threads=1
Monitor Section
This section is used to set the monitoring module. The servers to monitor are named here and they will be configured in other sections.
[MariaDB10 Monitor]
type=monitor
module=mysqlmon
servers=max1,max2,max3,max4
user=maxmon
passwd=maxpwd
SLB Section
This section is used to set the Statement Load Balancer. We will use the readwritesplit module available with MaxScale.
[RW Split Router]
type=service
router=readwritesplit
servers=max1,max2,max3,max4
user=maxuser
passwd=maxpwd
HTTP Section
This section is used to provide a RESTful API and it is still experimental.
[HTTPD Router]
type=service
router=testroute
servers=max1,max2,max3,max4
Debug Section
We will use this section to administer and debug MaxScale. MaxScale is still at early stage and at the moment the debug module is also used as administration module.
[Debug Interface]
type=service
router=debugcli
Listener Sections
We set three listeners, for the SLB module, for the Debug module and for the HTTP module.
[RW Split Listener]
type=listener
service=RW Split Router
protocol=MySQLClient
port=4006
 
[Debug Listener]
type=listener
service=Debug Interface
protocol=telnetd
port=4442
 
[HTTPD Listener]
type=listener
service=HTTPD Router
protocol=HTTPD
port=6444
Servers Sections
Finally, we set the 4 servers:
[max1]
type=server
address=192.168.56.21
port=3306
protocol=MySQLBackend
 
[max2]
type=server
address=192.168.56.22
port=3306
protocol=MySQLBackend
 
[max3]
type=server
address=192.168.56.23
port=3306
protocol=MySQLBackend
 
[max4]
type=server
address=192.168.56.24
port=3306
 
protocol=MySQLBackend
Putting all together, the file looks like this:
#
# Number of server threads
# Valid options are:
#      threads=
[maxscale]
threads=1

#
# Define a monitor that can be used to determine the state and role of
# the servers.
#
# Valid options are:
#
#      module=
#      servers=,,...
#      user =
#                          slave client privileges>
#      passwd=
[MariaDB10 Monitor]
type=monitor
module=mysqlmon
servers=max1,max2,max3,max4
user=maxmon
passwd=maxpwd

#
# A series of service definition
#
# Valid options are:
#
#      router=
#      servers=,,...
#      user=
#      passwd=
#
# Valid router modules currently are:
#      readwritesplit, readconnroute and debugcli
[RW Split Router]
type=service
router=readwritesplit
servers=max1,max2,max3,max4
user=maxuser
passwd=maxpwd

[HTTPD Router]
type=service
router=testroute
servers=max1,max2,max3,max4

[Debug Interface]
type=service
router=debugcli
 
#
# Listener definitions for the services
#
# Valid options are:
#
#      service=
#      protocol=
#      port=
[RW Split Listener]
type=listener
service=RW Split Router
protocol=MySQLClient
port=4006
 
[Debug Listener]
type=listener
service=Debug Interface
protocol=telnetd
port=4442
 
[HTTPD Listener]
type=listener
service=HTTPD Router
protocol=HTTPD
port=6444
 
# Definition of the servers
[max1]
type=server
address=192.168.56.21
port=3306
protocol=MySQLBackend
 
[max2]
type=server
address=192.168.56.22
port=3306
protocol=MySQLBackend
 
[max3]
type=server
address=192.168.56.23
port=3306
protocol=MySQLBackend
 
[max4]
type=server
address=192.168.56.24
port=3306
protocol=MySQLBackend

Step 4 – Running MaxScale

You can start MaxScale in many ways, but I would recommend to create a script like this:
[root@Max6 ~]# cat bin/maxscale
MAXSCALE_HOME=/usr/local/skysql/maxscale/MaxScale
LD_LIBRARY_PATH=/usr/local/skysql/maxscale/lib
/usr/local/skysql/maxscale/bin/maxscale
For the administration commands, the script would simply be:
[root@Max6 ~]# cat bin/maxscale_admin
telnet localhost 4442
Now we are ready to test MaxScale.
[root@Max6 ~]# maxscale
 
SkySQL MaxScale     Sun Jan  5 21:10:33 2014
------------------------------------------------------
Info :  MaxScale will be run in a daemon process.
        See the log from the following log files:
Error log     :     /usr/local/skysql/maxscale/MaxScale/log/skygw_err1.log
Message log   :     /usr/local/skysql/maxscale/MaxScale/log/skygw_msg1.log
Trace log     :     /usr/local/skysql/maxscale/MaxScale/log/skygw_trace1.log
Debug log     :     /usr/local/skysql/maxscale/MaxScale/log/skygw_debug1.log 

Listening MySQL connections at 0.0.0.0:4006
Listening http connections at 0.0.0.0:6444 
Listening telnet connections at 0.0.0.0:4442
You can execute this command on Max 0:
[root@Max0 ~]# mysql -u maxuser -h192.168.56.26 -P4006 -pmaxpwd
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1608
Server version: 5.5.22-SKYSQL-0.1.0 MariaDB Server
 
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MySQL [(none)]> \s
--------------
mysql  Ver 15.1 Distrib 10.0.7-MariaDB, for Linux (x86_64) using readline 5.1
 
Connection id:         1608
Current database:
Current user:          maxuser@skycluster6
SSL:                   Not in use
Current pager:         stdout
Using outfile:         ''
Using delimiter:       ;
Server:                MySQL
Server version:        5.5.22-SKYSQL-0.1.0
MariaDB Server
Protocol version:      10
Connection:            192.168.56.26 via TCP/IP
Server characterset:   latin1
Db     characterset:   latin1
Client characterset:   latin1
Conn.  characterset:   latin1
TCP port:              4006
Uptime:                2 hours 55 min 2 sec
 
Threads: 6  Questions: 150  Slow queries: 0  Opens: 1  Flush tables: 1  Open tables: 63  Queries per second avg: 0.014
--------------
 
MySQL [(none)]>
The server and server versions are old names that do not provide any meaning and they will fixed in the next release – but they refer to MaxScale.
In order to check if MaxScale is working, check the process list on each MariaDB Server:
Max 1:
MariaDB [test]> show processlist;
+----+---------+-------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
| Id | User    | Host              | db   | Command     | Time | State                                                                 | Info             | Progress |
+----+---------+-------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
|  3 | root    | localhost         | test | Query       |    0 | init                                                                  | show processlist |    0.000 |
|  4 | root    | skycluster2:34549 | NULL | Binlog Dump | 3066 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |    0.000 |
|  5 | root    | skycluster4:59562 | NULL | Binlog Dump | 2941 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |    0.000 |
|  6 | root    | skycluster3:48031 | NULL | Binlog Dump | 2936 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |    0.000 |
| 11 | maxmon  | skycluster6:32784 | NULL | Sleep       |    7 |                                                                       | NULL             |    0.000 |
| 13 | maxuser | skycluster6:32791 | NULL | Sleep       |   45 |                                                                       | NULL             |    0.000 |
+----+---------+-------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+
6 rows in set (0.00 sec)
Max 2:
MariaDB [test]> show processlist;
+----+-------------+-------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User        | Host              | db   | Command | Time | State                                                                       | Info             | Progress |
+----+-------------+-------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
|  4 | system user |                   | NULL | Connect | 3143 | Waiting for master to send event                                            | NULL             |    0.000 |
|  5 | system user |                   | NULL | Connect | -952 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 |
|  6 | root        | localhost         | test | Query   |    0 | init                                                                        | show processlist |    0.000 |
|  9 | maxmon      | skycluster6:54821 | NULL | Sleep   |    5 |                                                                             | NULL             |    0.000 |
+----+-------------+-------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
4 rows in set (0.00 sec)
Max 3:
MariaDB [(none)]> show processlist;
+----+-------------+-------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User        | Host              | db   | Command | Time | State                                                                       | Info             | Progress |
+----+-------------+-------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
|  3 | root        | localhost         | NULL | Query   |    0 | init                                                                        | show processlist |    0.000 |
|  4 | system user |                   | NULL | Connect | 3058 | Waiting for master to send event                                            | NULL             |    0.000 |
|  5 | system user |                   | NULL | Connect | -905 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 |
|  8 | maxmon      | skycluster6:57298 | NULL | Sleep   |    9 |                                                                             | NULL             |    0.000 |
|  9 | maxuser     | skycluster6:57302 | NULL | Sleep   |  167 |                                                                             | NULL             |    0.000 |
+----+-------------+-------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
5 rows in set (0.00 sec)
Max 4:
MariaDB [test]> show processlist;
+----+-------------+-------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User        | Host              | db   | Command | Time | State                                                                       | Info             | Progress |
+----+-------------+-------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
|  3 | root        | localhost         | test | Query   |    0 | init                                                                        | show processlist |    0.000 |
|  4 | system user |                   | NULL | Connect | 3112 | Waiting for master to send event                                            | NULL             |    0.000 |
|  5 | system user |                   | NULL | Connect |  655 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 |
| 13 | maxmon      | skycluster6:32771 | NULL | Sleep   |    8 |                                                                             | NULL             |    0.000 |
+----+-------------+-------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
4 rows in set (0.00 sec)
As you can see from the process list, the connection from Max 0 has been set to Max 1 for R/W and to Max 3 for R/O operations.

Step 5 Testing MaxScale

A super-simple test would be to check the number of queries and connections on each server before, during and after a mysqlslap session.
First, let’s prepare a basic command like:
[root@Sky1 ~]# cat mon.sql
select * from information_schema.global_status
 where variable_name in (
 'COM_SELECT',
 'COM_INSERT',
 'THREADS_CONNECTED’ );
…and…
[root@Sky1 ~]# cat mon
watch --interval=1 'mysql < mon.sql'
So you can run ./mon on each MariaDB/MySQL server, you should see a page like this, refreshing every second:
Every 1.0s: mysql < mon.sql     Tue Jan  7 23:11:17 2014 
VARIABLE_NAME   VARIABLE_VALUE
COM_INSERT               54856
COM_SELECT               85798
THREADS_CONNECTED            2
The threads on the master server are, as you have already seen in the process list, the replication threads and the MaxScale monitor. At least one of the thread on the slave servers is the MaxScale Monitor. On these servers, you will see the value of the COM_SELECT variable stepping +2 every second because the MaxScale monitor checks the status of the database every half second.
Now, if we run mysqlslap with 128 concurrent connections from Max 0:
[root@Sky0 ~]# mysqlslap -a -umaxuser -h192.168.56.26 -P4006 -pmaxpwd --create-schema=slap1 -c128
Benchmark
     Average number of seconds to run all queries: 6.792 seconds
     Minimum number of seconds to run all queries: 6.792 seconds
     Maximum number of seconds to run all queries: 6.792 seconds
     Number of clients running queries: 128
     Average number of queries per client: 0
Running the test on my laptop, the time is pretty irrelevant. What is relevant though is the increment that you can notice with the watch command on the DB servers:
The number of connected threads on Max 1 (the master node) should go a bit beyond 128 connections;
  • The number of connected threads on Max 2, 3 and 4 (the slave nodes) should be a bit more than 43;
  • On Max 1, the number of COM_INSERT increases significantly, COM_SELECT keeps increasing at the pace of 1 every 0.5 second;
  • On Max 2, 3 and 4, both the number of COM_INSERT and COM_SELECT increases. COM_INSERT increases because the replication thread is adding writing data. The number of COM_SELECT will increase equally on all the slaves because MaxScale is balancing the read queries on all the available slaves.
More tests and in-depth to come. In the meantime, please help us by testing maxscale, provide feedback, comments, suggestions, and submit bugs.
The MaxScale project is on GitHub, here: https://github.com/skysql/MaxScale