In this blog post, we’ll discuss read-write split routing in MaxScale.
The two previous posts have shown how to setup high availability (HA) with Maxscale using asynchronous replication and how we monitor replication.
Now let’s focus on the routing module performing read-write splits.
This is our current configuration:
1 2 3 4 5 6 7 | [Splitter Service] type=service router=readwritesplit servers=percona1, percona2 max_slave_replication_lag=30 user=maxscale passwd=264D375EC77998F13F4D0EC739AABAD4 |
This router module is designed to spread the read queries across multiple servers (slaves by default), and send the write queries to a single server: the master.
This module is intended to work with Asynchronous Master-Slave replication but also with Galera replication if you plan to write to a single node.
So what is routed to the Master?
- Write statements
- All statements within an open transaction, even if this transaction is read only
- Store procedure and user-defined function call.
- DDL statements
- Execution of prepared statements (EXECUTE)
- All statements using temporary tables
Example:
- percona1: master
- percona2 and percona3: slaves
Let’s connect to MaxScale with the MySQL’s interactive client:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | mysql> select @@hostname; +------------+ | @@hostname | +------------+ | percona2 | +------------+ mysql> start transaction; mysql> select @@hostname; +------------+ | @@hostname | +------------+ | percona1 | +------------+ mysql> rollback; mysql> select @@hostname; +------------+ | @@hostname | +------------+ | percona2 | +------------+ |
Now let’s try with a READ ONLY transaction:
1 2 3 4 5 6 7 | mysql> start transaction read only; mysql> select @@hostname; +------------+ | @@hostname | +------------+ | percona1 | +------------+ |
As we can see, MaxScale doesn’t support READ ONLY transactions. It considers them the same as any transaction. This means they are routed to the master as a WRITE.
We’ve already seen the max_slave_replication_lag optional parameter, but there are some others:
- max_slave_connections: defines the maximum number of slaves a router session uses, the default is to use all the ones available
- use_sql_variables_in: defines where queries’ reading session variables should be routed. Valid values are
master
andall (
the latter being the default) - weightby: defines the name of the value used to calculate the weights of the server
Now let’s play with the weightby . So in this configuration, we will target 10% of the reads to percona2, and 90% to percona3:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | [Splitter Service] type=service router=readwritesplit servers=percona1, percona2, percona3 weightby=myweight ... [percona2] type=server address=192.168.90.3 port=3306 protocol=MySQLBackend myweight=1 [percona3] type=server address=192.168.90.4 port=3306 protocol=MySQLBackend myweight=9 |
We restart MaxScale, and verify the settings of the service:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | # maxadmin -pmariadb show service "Splitter Service" Service 0x363b460 Service: Splitter Service Router: readwritesplit (0x7fe7f1e88540) State: Started Number of router sessions: 0 Current no. of router sessions: 0 Number of queries forwarded: 0 Number of queries forwarded to master: 0 Number of queries forwarded to slave: 0 Number of queries forwarded to all: 0 Master/Slave percentage: 0.00% Connection distribution based on myweight server parameter. Server Target % Connections Operations Global Router percona3 90.0% 0 0 0 percona2 10.0% 0 0 0 percona1 100.0% 0 0 0 Started: Wed Feb 24 22:39:27 2016 Root user access: Disabled Backend databases 192.168.90.4:3306 Protocol: MySQLBackend 192.168.90.3:3306 Protocol: MySQLBackend 192.168.90.2:3306 Protocol: MySQLBackend Routing weight parameter: myweight Users data: 0x36397c0 Total connections: 2 Currently connected: 2 SSL: Disabled |
The target % seems correct, let’s test it!
1 2 3 4 5 6 7 8 9 10 11 12 13 | for i in `seq 1 10`; do mysql -h 192.168.90.5 -BN -umanager -ppercona -e "select @@hostname; select sleep(10)" 2>/dev/null & done percona2 percona2 percona2 percona2 percona2 percona2 percona3 percona3 percona3 percona3 |
That doesn’t seem good! Let’s check the service again:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | Service 0x363b460 Service: Splitter Service Router: readwritesplit (0x7fe7f1e88540) State: Started Number of router sessions: 10 Current no. of router sessions: 10 Number of queries forwarded: 30 Number of queries forwarded to master: 0 Number of queries forwarded to slave: 30 Number of queries forwarded to all: 0 Master/Slave percentage: 0.00% Connection distribution based on myweight server parameter. Server Target % Connections Operations Global Router percona3 90.0% 10 10 5 percona2 10.0% 10 10 5 percona1 100.0% 10 10 0 Started: Wed Feb 24 22:39:27 2016 Root user access: Disabled Backend databases 192.168.90.4:3306 Protocol: MySQLBackend 192.168.90.3:3306 Protocol: MySQLBackend 192.168.90.2:3306 Protocol: MySQLBackend Routing weight parameter: myweight Users data: 0x36397c0 Total connections: 12 Currently connected: 12 SSL: Disabled |
Five operations for both . . . this looks like a normal load balancer, 50%-50%.
So that doesn’t work as we expected. Let’s have a look at other router options:
- slave_selection_criteria. Controls how the router chooses the slaves and how it load balances the sessions. There are some parameter options:
- LEAST_GLOBAL_CONNECTIONS. Slave with least connections from MaxScale, not on the server itself
- LEAST_ROUTER_CONNECTIONS. Slave with least connections from this service
- LEAST_BEHIND_MASTER. Slave with smallest replication lag
- LEAST_CURRENT_OPRTATIONS. Slave with least active operations (this is the default)
- master_accept_reads. Uses the master for reads
- slave_selection_criteria. Controls how the router chooses the slaves and how it load balances the sessions. There are some parameter options:
The are some others; please check the online manual for:
- max_sescmd_history
- disable_sescmd_history
That explains the behavior we just observed. But what if we want to use the weight setting, and not spread the reads equivalently on the slaves?
I found the answer on IRC in the #maxscale freenode. Markus Makela (markusjm) explained to me that the default configuration in 1.3.0 is to use all the slaves, and load balance the actual statements. So to achieve what we want to do, we need to use these options in the service section:
1 2 | router_options=slave_selection_criteria=LEAST_GLOBAL_CONNECTIONS max_slave_connections=1 |
Let’s test it:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | for i in `seq 1 10`; do mysql -h 192.168.90.5 -BN -umanager -ppercona -e "select @@hostname; select sleep(10)" 2>/dev/null & done percona3 percona3 percona3 percona3 percona3 percona2 percona3 percona3 percona3 percona3 Service 0x1d88560 Service: Splitter Service Router: readwritesplit (0x7f9c018c3540) State: Started Number of router sessions: 10 Current no. of router sessions: 10 Number of queries forwarded: 30 Number of queries forwarded to master: 0 Number of queries forwarded to slave: 30 Number of queries forwarded to all: 0 Master/Slave percentage: 0.00% Connection distribution based on myweight server parameter. Server Target % Connections Operations Global Router percona3 90.0% 9 9 9 percona2 10.0% 1 1 1 percona1 100.0% 10 10 0 Started: Wed Feb 24 22:58:21 2016 Root user access: Disabled Backend databases 192.168.90.4:3306 Protocol: MySQLBackend 192.168.90.3:3306 Protocol: MySQLBackend 192.168.90.2:3306 Protocol: MySQLBackend Routing weight parameter: myweight Users data: 0x1d8a480 Total connections: 12 Currently connected: 12 SSL: Disabled |
Yes! It worked as expected!
max_slave_connections sets the maximum number of slaves a router session uses at any moment. The default is to use all available slaves. When we set it to 1, we get one master and one slave connection per client, and the connections are balanced according to the server weights. The new mechanism uses statements instead of connections for load balancing (see MXS-588).
Finally, this routing module also support routing hints
. I’ll cover them in my next MaxScale post.
More information: https://github.com/mariadb-corporation/MaxScale/blob/develop/Documentation/Routers/ReadWriteSplit.md
For an HA solution this works good. But considering the performance during Benchmark this is similar to Haproxy throughput which was upsetting. VIP via Pacemaker/Heartbeat still better in terms of performance since it doesn’t have middle hop like haproxy/maxscale between application and database.
thanks, your post helps me