August 11, 2014

Scale Out Reads With MaxScale 1.0

Let's assume you want to start an automatically expanding and shrinking MySQL replication cluster with up-to seven database servers. This blog shows how to setup up and start MaxScale to work with a master and a single slave and, when needed, how it adapts to the changing cluster configurations. While the set up here is simple similar behavior can be applied in bigger and more complex scenarios.

Configuration

We make two important assumptions here: MaxScale doesn't promote master and slaves, someone else does. Same goes with master failover, replication setup, user management, GRANTs etc. (some of the necessary steps are covered in one of the earlier blogs https://mariadb.com/blog/installing-newest-maxscale-scratch-builders-edi...).
Secondly, all server names, ip addresses and listening ports of database nodes must be known and be written in the MaxScale.cnf configuration file prior starting MaxScale. Servers don't need to be present, running nor have specific status in the beginning but server information must be available for MaxScale when it starts.

Below is a minimal example of such a configuration:

[maxscale]
threads=8

[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3,server4,server5,server6,server7
user=maxuser
passwd=maxpwd

[RW Split Router]
type=service
router=readwritesplit
servers=server1,server2,server3,server4,server5,server6,server7
max_slave_connections=100%
user=maxuser
passwd=maxpwd

[Debug Interface]
type=service
router=debugcli

[RW Split Listener]
type=listener
service=RW Split Router
protocol=MySQLClient
port=4006

[Debug Listener]
type=listener
service=Debug Interface
protocol=telnetd
port=4442

[server1]
type=server
address=192.168.100.1
port=3000
protocol=MySQLBackend

[server2]
type=server
address=192.168.100.2
port=3000
protocol=MySQLBackend
...

[server7]
type=server
address=192.168.100.7
port=3000
protocol=MySQLBackend

Start MaxScale With a Master and a Slave

Once configuration for MaxScale is written and saved, start maxscale in bin/ directory by executing ./maxscale -d -c ../.

If none of the servers is running or is not in either Master nor Slave role, you will see following messages appearing in the error log:

2014 08/05 23:33:13   Error : Unable to get user data from backend database for service RW Split Router. Missing server information.
2014 08/05 23:33:16   Error : Monitor was unable to connect to server 192.168.100.1:3000 : "Can't connect to MySQL server on '192.168.100.1' (113)"
2014 08/05 23:33:17   Error : Monitor was unable to connect to server 192.168.100.2:3000 : "Can't connect to MySQL server on '192.168.100.2' (113)"
2014 08/05 23:33:18   Error : Monitor was unable to connect to server 192.168.100.3:3000 : "Can't connect to MySQL server on '192.168.100.3' (113)"
...
2014 08/05 23:33:19   Error : Monitor was unable to connect to server 192.168.100.7:3000 : "Can't connect to MySQL server on '192.168.100.7' (111)"

This only means that MaxScale can't read authentication information from backend servers for any of the configured services nor it fails to query server statuses from the servers. The message log indicates the same fact:

2014 08/05 23:33:17   Backend server 192.168.100.1:3000 state : NO STATUS
2014 08/05 23:33:18   Backend server 192.168.100.2:3000 state : NO STATUS
...
2014 08/05 23:33:19   Backend server 192.168.100.7:3000 state : NO STATUS

Next, start the master and one slave, say, servers "server1", and "server2", and start MySQL replication between them. You may see something like this in the message log:

2014 08/05 23:36:49   Backend server 192.168.100.5:3000 state : NO STATUS
2014 08/05 23:36:49   Backend server 192.168.100.6:3000 state : NO STATUS
2014 08/05 23:36:49   Backend server 192.168.100.7:3000 state : NO STATUS
2014 08/05 23:36:59   Backend server 192.168.100.1:3000 state : RUNNI NG (only)
2014 08/05 23:36:59   Backend server 192.168.100.2:3000 state : RUNNING (only)
2014 08/05 23:37:12   Backend server 192.168.100.3:3000 state : NO STATUS
2014 08/05 23:37:13   Backend server 192.168.100.4:3000 state : NO STATUS

Note that MaxScale currently requires at least a master and one slave before it accepts any server as master. Since the status differs from MASTER and SLAVE, check server statuses by accessing MaxScale with the the debug interface

telnet localhost 4442

and by executing:

show servers

You should see server1 and server2 as Master, Running and Slave, Running. Before quitting debug client, enable the trace log by executing:

log enable trace

Now start a read/write router session by executing:

mysql -c --host=127.0.0.1 -P 4006 -u maxuser -pmaxpwd

Open the trace log with:

tail -f -n500 ../log/skygw_trace1.log

You should see the following message there:

2014 08/05 23:43:06   Note : Couldn't connect to maximum number of slaves. Connected successfully to 1 slaves of 1 of them.
2014 08/05 23:43:06   Query : "select @@version_comment limit 1"
2014 08/05 23:43:06   QUERY_TYPE_READ
2014 08/05 23:43:06   Selected RUNNING SLAVE in         192.168.100.2:3000
2014 08/05 23:43:06   Selected RUNNING MASTER in        192.168.100.1:3000


The cluster is up and running and MaxScale with its services on top of it.

Scaling Out

When it becomes necessary to balance the load with more slaves, start two new slaves, say, server3 and server4, and start replication between them and the master. As soon as MaxScale's monitor notices changed statuses of them the message log should include the following lines:

2014 08/05 23:56:00   Backend server 192.168.100.3:3000 state : RUNNING (only)
2014 08/05 23:56:11   Backend server 192.168.100.4:3000 state : RUNNING (only)

You can check the status in the similar way than earlier, by using debug client. Then connect a new client with MaxScale. If you look at the trace log now you should see the following:

2014 08/05 23:56:15   Note : Couldn't connect to maximum number of slaves. Connected successfully to 3 slaves of 3 of them.
2014 08/05 23:56:15   Query : "select @@version_comment limit 1"
2014 08/05 23:56:15   QUERY_TYPE_READ
2014 08/05 23:56:15   Selected RUNNING SLAVE in         192.168.100.4:3000
2014 08/05 23:56:15   Selected RUNNING SLAVE in         192.168.100.3:3000
2014 08/05 23:56:15   Selected RUNNING SLAVE in         192.168.100.2:3000
2014 08/05 23:56:15   Selected RUNNING MASTER in        192.168.100.1:3000

MySQL Replication Cluster with Three Slaves

Now read load is divided between servers 2, 3 and 4.

Scaling In

It is also possible to squeeze the cluster, that is, limit the number of slaves the router session uses (other than using max_slave_connections=1). When server(s) are needed to be taken down, shut down, for example, server3. MaxScale's monitor will detect the changed status of the server and the existing session will re-select new slave(s) on-the-fly from the existing servers. As a consequence, the trace log includes the following messages:

2014 08/06 00:02:01   Servers and router connection counts:
2014 08/06 00:02:01   192.168.100.7:3000 current operations : 0
2014 08/06 00:02:01   192.168.100.6:3000 current operations : 0
2014 08/06 00:02:01   192.168.100.5:3000 current operations : 0
2014 08/06 00:02:01   192.168.100.4:3000 current operations : 0
2014 08/06 00:02:01   192.168.100.3:3000 current operations : 0
2014 08/06 00:02:01   192.168.100.2:3000 current operations : 0
2014 08/06 00:02:01   192.168.100.1:3000 current operations : 0
2014 08/06 00:02:01   Note : Couldn't connect to maximum number of slaves. Connected successfully to 2 slaves of 2 of them.
2014 08/06 00:02:01   Selected RUNNING SLAVE in         192.168.100.2:3000
2014 08/06 00:02:01   Selected RUNNING MASTER in        192.168.100.1:3000
2014 08/06 00:02:01   Selected RUNNING SLAVE in         192.168.100.4:3000

MySQL Replication Cluster with Master and Two Slaves

If there were no active client sessions, the next client session will simply see the existing two servers and use them.

Summary

If you know what servers will potentially belong to your MySQL Replication cluster, it scales out seamlessly as the load increases or vice versa, squeezes when all resources are not needed. Servers can be added to and removed from the cluster without having to modify your application or reconfigure MaxScale.

About Vilho Raatikka

Vilho is a Senior Software Engineer working mainly on MaxScale. Vilho has worked with databases server technologies since the year 2000 for IBM, Solid Information Technology and Helsinki University as software engineer, teaching assistant and researcher.

Read all posts by Vilho Raatikka