Mar
29
2016
--

Read-write split routing in MaxScale

Read-write split routing performance in MaxScale

Read-write split routing in MaxScaleIn 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 are master and all (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

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com