[MariaDB]: 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 theMaxScale.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
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 usingmax_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
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.