Jan
08
2014
A Close Encounter with MaxScale
Posted by Ivan Zoratti on Wed 08 Jan 2014 09:08 UTC
Tags:


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 preparationIf 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