Moving On

I have a difficult task of making this post interesting, helpful and personal at the same time. I think the main goal is to balance these aspects, and I really appreciate your comments and suggestions that I will add here.

For the busy readers who may be put off by the length of this post, here is a very short summary: I spent 4 wonderful years, first as the head of Field Services, then as a CTO, I believe it is now time for a change, so I am leaving SkySQL. I am leaving behind a great company and very good friends, but I am not disappearing completely, and I will continue supporting the work I started and the projects I created with the help of such great people.

For many, leaving a company is not easy, and it is extremely difficult if you have contributed to its creation and development since the beginning. Even more difficult is to depart from ideas and projects that you have shaped and designed, together with the people who have contributed to them and that I am sure they will continue to work on these projects with great success.

The reasons for SkySQL

In the past 4 years, I have been asked many times why we had created SkySQL and how SkySQL is different from other providers, such as Percona and Oracle.
Since the beginning, the first and most important objective for SkySQL was to provide the best products and services around MySQL. In order to achieve this objective, we had created a network of partners and we were working closely with them to support our customers in the best possible way. The value added by SkySQL to the offering was a strong team of consultants and architects who could suggest and implement MySQL solutions, and a stellar Technical Support team who could provide the best possible answers to a large variety of technical and consultative issues that customers might encounter.
Having many options to choose from was certainly good, but it introduced another issue: not all the products could work well together. Customers demanded solutions from a single vendor that could go beyond the “typical” MySQL database + backup + monitor: they wanted to have a set of products that was tested and guaranteed to work together. This was the first motivation for the first big effort at SkySQL in terms of products and tools, when we defined the SkySQL Reference Architecture.

The Reference Architecture was the result of many hours spent in meetings and solitary thinking in my home office during the Christmas holidays in 2010, when the business slowed down and I could dedicate more CPU cycles to the subject. We worked on the project for 4 months and we launched the Architecture as a concept at the MySQL/Percona Conference in 2011. We demonstrated the SkySQL Reference Architecture with a tool that users could access online, in order to automatically generate and activate a fully functional cluster of MySQL replicated servers with MONyog, MySQL Replication, a cluster software with resource agent in AWS. Severalnines had a similar approach for MySQL Cluster and later for MySQL Replication and Galera, but at that time only SkySQL had the full automation and a selection of different engines and uses, from the configuration to the MySQL prompt. Later, Percona introduced a web tool that could provide an optimised configuration file.

The evolution of the Reference Architecture was the SkySQL Data Suite (SDS). The concept was similar, but the main difference was that for the first time we added SkySQL Intellectual Property to MySQL. The suite was packaged with an administration tool that was designed and built by SkySQL. The first target was the Cloud, specifically AWS and OpenStack. The initial idea was to have SDS seamlessly deployed on bare OS, on clouds or in hybrid environments. All the tools have been designed with programmable and user interfaces, in order to satisfy different customers’ needs. An independent presentation of SDS is available here.

In 2013, the company merged with Monty Program, and we suddenly found ourselves in a position where software development was a fundamental part of our offering. We moved the focus of the Data Suite to MariaDB and we rebranded it as MariaDB Enterprise, but more importantly, we combined the value and the skills of our services team with the core team of the original development of MySQL. The merge resulted in a company with all the credentials needed to excel and innovate in the MySQL world. But the key question at this point was: is this enough to make MySQL even more successful? Is a better MariaDB (or indeed MySQL) the right answer to the data management needs in 2010s and beyond?

The evolution of MySQL and MariaDB

The answer to the previous questions is not surprisingly a “no”. Indeed, users need a better MySQL (or MariaDB). Traditionally, they demanded more performance, more availability and more scalability, and many players have contributed in their own way to the cause.
Still, there is something missing. The competition from NoSQL solutions is, to say the least, intense. It is probably true that the MySQL adoption is not declining (as some analysts say), but the adoption of NoSQL is way bigger in absolute terms. And more important, the majority of the new initiatives and startups that once were the lymph that flowed in the MySQL Community, have now moved to NoSQL.
From a purely technical (and generic) perspective, when MySQL and NoSQL are tested and measured in a fair way, MySQL can provide in many cases better performance and robustness. Scalability, on the other hand, is a big issue as it has always been – it was an issue for bigger servers in the past, it is an issue for distributed systems now. The search for a better scalability is the primary reason why we have created MaxScale.

You may have read a lot about MaxScale, or you may want to read more here and here. In simple terms, MaxScale is a highly scalable, lightweight proxy system aimed at distributing and scaling parts of a database server that do not need to reside in its core. There is a similarity to this approach in the NoSQL world and certainly in many home made solitions. The mongos / mongod binomial is a good example of what MaxScale can achieve with MySQL, but this is only half of the story. MaxScale is generic in nature, what makes it a relevant component of the IT infrastucture are its plugins. By loading different plugins you can make MaxScale a proxy for multiple client protocols, or a proxy for geographically replicated servers, or to integrate different replication technologies, and so on.

I believe that we need MaxScale for MySQL and MariaDB. Incidentally, Max is the name of Monty’s son, so we have covered all his heirs (at least so far). In designing MaxScale, I wanted to provide a link between a technology that was good for servers available in the 90s and today’s infrastructures.

A difficult choice?

One might ask, if I feel so strong about MaxScale and its fundamental role, why am I leaving it behind? The fact is, I am not. The project is in good hands, thanks to the great work and dedication from Mark Riddoch, Massimiliano Pinto and Vilho Raatikka. The concept, the ideas and the architecture are here to stay. MaxScale is shaped today as we – Mark Riddoch, Massimo Brignoli and I – wanted it, as we have designed it during long hours of work and passionate discussions.
When your kids grow up and are ready to walk alone in the world, you need to let them go. MaxScale can now walk with MySQL and MariaDB, and SkySQL will take a good care of their path together. So now, I may move on and I have time to raise other kids.

A look at the future

As for me, I am technically embracing a wider range of technologies. I will not be focused only to MySQL, but rest assured that these 10 years will always remain in my heart. I will work on IT infrastructures and systems where databases play the central and most important role, but I will look at the customers’ needs as a whole. I will carry on my duty for the MySQL User Group in London, that has now reached the reasonable size of 40-50 attendees per session, every other month. I will not move my MySQL blog, so any MySQL-related post will be available on izoratti.blogspot.com and it will be aggregated on PlanetMySQL. But I will have a collection various topics in my personal blog www.ivanzoratti.com. I will cover more databases, HPC, OpenStack and OSs. I will also have a section dedicated to an important aspect of my life, which is the study of Kung Fu in its inner and outer styles. I started learning Kung Fu almost 30 years ago, first for 12 years, then I abandoned it for another 12 years, until I realised the importance of this practice in my life. I have to thank some of my best friends for that, they really helped me a lot in good and bad times.

So, even if I will not wear a T-shirt with a seal (or a sea lion, as it is more fashionable these days), you will probably see me around at conferences and exhibitions, or perhaps you will not see me, but I will work, as I have done in these 10 years, behind the scenes to make MySQL the good and strong database that can help in creating the next Facebook or the next Twitter of this world.

All the best to all of you.

MaxScale 0.6

Kudos to the SkySQL Engineering team, who released the 3rd alpha version of MaxScale, a database proxy for MySQL, MariaDB and Percona servers, labeled MaxScale 0.6.

This version comes with two important additions:

  • A feature-complete read/write splitting module, i.e. read and write operations are now balanced in a smarter way to master or slave servers.
  • New client-based features, such as a version string that provides compatibility with the major MariaDB and MySQL connectors, the ability to connect through the root user and the use of the unix socket when MaxScale is co-located with a client application on the same server.

Binaries and source code are “hidden” here: http://downloads.skysql.com/files/SkySQL/MaxScale.

The project is on GitHub: https://github.com/skysql/MaxScale/

Other helpful links are:

We have still some work to do before we can reach a beta stage. We definitely need to catch more bugs, but we also want to extend the features of the product. We are working on different types of load balancing, on the compatibility with Galera, MySQL Cluster/NDB, MariaDB Spider and on many other aspects.

At the moment we have two routing modules:

  • The readconnroute module is a connection load balancer that accepts 2 types of connections from the client (read/write and read/only) and it balances the connections among all the masters and the slaves in a MySQL and MariaDB Cluster. This is helpful for applications that have apre-defined read/write and read/only operations. If the backend is based on a Galera Cluster, readconnroute acts as a “simple” load balancer for all the connections.
  • The readwritesplit module is a statement load balancer that accepts a connection that can send read and write operations. The module parses each statement and decides whether must be sent to a master server, a slave server or all the servers. The backend is a MariaDB or a MySQL Replication cluster.

There is definitely more to come. For example, we want to provide read/write splitting for Galera clusters in order to avoid deadlocks and to guarantee read operations without replication lag. We are also working on a version that can handle slaves with different specification and clusters that can be organised on multiple locations, i.e. where there is a mix of local and remote servers. Last but not least, we are working on benchmarks to show the clear benefits of MaxScale, where it fits and where it doesn’t. in a typical IT infrastructure.

As usual, help, suggestions and comments are more than welcome.

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