In this post, I ‘ m going to cover my recent encounter with MaxScale. We had to help one of our client to load balance and split read / writes on their 3 node Percona XtraDB Cluster (PXC).

List of available options were

1. HaProxy
2. MaxScale
3. ProxySQL

Implementation of HaProxy is ruled out as It’s doesn’t have SQL awareness, Also PXC don’t scale well on write sharing.

“In the past, Read Write split has to done on the application side using API’s. Now, we can able to split Reads and Writes without touching application through Intelligent Load Balancers”

Finally, We have chosen MaxScale over ProxySQL as ProxySQL is still in Beta and not as feature rich as MaxScale & Galera Support was not ready completely.

Key Reason To Choose MaxScale:

1. SQL aware load balancer – Can able to split the reads / writes
2. Galera state awareness – Can able to direct hits only to the nodes in ‘Synced’ state in PXC
3. Modular implementation – Easier to Implment, Use and Manage
4. Monitoring console – We can login MaxScale console to verify the states and routing statistics
5. Actively maintained by MariaDB Corp – It has GA release from MariaDB and You can expect prompt fixes and upgrades.

Implementation:

1. Pre Requisite

Create a MySQL user with the following privileges on db servers to be able to connect from MaxScale server, To check states and availability.

 
GRANT SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'maxscale'@'%' identified by 'MaxScale';
 GRANT SELECT ON mysql.user TO 'maxscale'@'%';
 GRANT SELECT ON mysql.db TO 'maxscale'@'%';
 GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%';
	

2. Download and Install MaxScale

MariaDB MaxScale’s packages are available at https://downloads.mariadb.com/files/MaxScale

Here I ‘ m downloading and installing the MaxScale’s latest version 1.4.3.

 
# wget https://downloads.mariadb.com/files/MaxScale/1.4.3/centos/6/x86_64/maxscale-1.4.3-1.centos.6.x86_64.rpm

# yum localinstall maxscale-1.4.3-1.centos.6.x86_64.rpm
	

3. Setup MaxScale Config

MaxScale looks for configuration file at /etc/maxscale.cnf

MaxScale has modular implementation and you need understand the following terms to setup MaxScale config.

[i] Monitor – MaxScale will analyse the given servers internally at regular intervals based on the given monitor modules. Basic modules are explained below.

“mysqlmon” – Check native replication health and server states (Master or Slave).

“galeramon” – Galera cluster health and node states are monitored with module.

We have used “galeramon” to monitor our PXC cluster. Sample config is listed below.

 
[Galera Monitor]
 type=monitor
 module=galeramon
 servers=node1,node2,node3
 user=maxscale
 passwd=MaxScale
	

[ii] Balance Load – MaxScale route connections based on router algorithm specified. Basic router types are explained below.

“readwritesplit” – To split the incoming writes to a single master and reads to the any number of given slaves.

“readconnroute” – It will split the incoming connections with round robin mechanism.

“cli” – MaxScale monitoring console.

We have used “readwritesplit” to split the reads / writes. Sample config is listed below.

 
[RW]
 type=service
 router=readwritesplit
 servers=node1,node2,node3
 user=maxscale
 passwd=MaxScale
	

Config for monitoring console is listed

 
[Console]
 type=service
 router=cli
	

[iii] Listen – MaxScale needs a listener to accept incoming connections for each service defined

 
[RW listener]
 type=listener
 service=RW
 protocol=MySQLClient
 port=3306

[Console Listener]
 type=listener
 service=Console
 protocol=maxscaled
 port=6603
	

[iv] Define Nodes – We have to define the node ip, port for the servers.

 
[node1]
 type=server
 address=10.0.1.10
 port=3310
 protocol=MySQLBackend
	

Similarly define the server details for node2 and node3

Now put the snippets altogether on /etc/maxscale.cnf

4. Start MaxScale

MaxScale config is compelete on the previous step. Now, We can start MaxScale.

 
#service maxscale start
 Starting MaxScale: maxscale (pid 4107) is running...       [  OK  ]
	

Check MaxScale log “/var/log/maxscale/maxscale1.log” In case of errors in Start.

5. Verify Implementation

 
# maxadmin -pmariadb "list servers"

Servers.

-------------------+-----------------+-------+-------------+--------------------

Server             | Address         | Port  | Connections | Status

-------------------+-----------------+-------+-------------+--------------------

node1              | 10.0.1.10    |  3310 |           0 | Slave, Synced, Running

node2              | 10.0.1.20    |  3310 |           0 | Slave, Synced, Running

node3              | 10.0.1.30    |  3310 |           0 | Master, Synced, Running

-------------------+-----------------+-------+-------------+--------------------
	

This completes installation of MaxScale. Operations and Administration will be followed up on later part of the blog post.