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:
[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:
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:
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 aremasterandall (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:
[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:
# 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!
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:
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
-
-
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:
router_options=slave_selection_criteria=LEAST_GLOBAL_CONNECTIONS max_slave_connections=1
Let’s test it:
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