Testing MariaDB MaxScale ReadWriteSplit
Introduction
MaxScale provides a functionality that enables UPDATE INSERT DELETE queries to be sent to the Master node and SELECT queries to be sent to Slave nodes by using ReadWriteSplit router.
According to MaxScale documentation:
The readwritesplit router is designed to increase the read-only processing capability of a cluster while maintaining consistency.
Read queries, which do not modify data, are spread across multiple nodes while all write queries will be sent to a single node.
Let’s test this functionality.
Environment
For this testing we are using a 3 node Galera Cluster with MaxScale already setup on Virtual Machines Instances from Google Cloud Platform. For more information please visit MaxScale with MariaDB Galera Cluster and MariaDB Galera Cluster in VM Instances.
MaxScale Galera Cluster
Servers
Issuing maxctrl command we can review we have one Master server patomariagm:3306 and the servers patomariag1:3306 and patomariag2:3306 are the Slave nodes:
pato@patomaxgalera~$ maxctrl list servers
+----------------------------------------------------------------------------------+
¦ Server ¦ Address ¦ Port ¦ Connections ¦ State ¦ GTID ¦
+------------+-------------+------+-------------+-------------------------+--------¦
¦ Pato-Node0 ¦ patomariagm ¦ 3306 ¦ 0 ¦ Master, Synced, Running ¦ 1-1-12 ¦
+------------+-------------+------+-------------+-------------------------+--------¦
¦ Pato-Node1 ¦ patomariag1 ¦ 3306 ¦ 0 ¦ Slave, Synced, Running ¦ 1-1-12 ¦
+------------+-------------+------+-------------+-------------------------+--------¦
¦ Pato-Node2 ¦ patomariag2 ¦ 3306 ¦ 0 ¦ Slave, Synced, Running ¦ 1-1-12 ¦
+----------------------------------------------------------------------------------+
MaxScale Configuration
Our MaxScale configuration includes the following service which uses this functionality defined as router=readwritesplit that will split the database SIUD activity between the nodes Pato-Node0 (Master) and Pato-Node1 (Slave):
[RW-Service]
type=service
router=readwritesplit
servers=Pato-Node0,Pato-Node1
user=maxpato
password=.....
[RW-Listener]
type=listener
service=RW-Service
protocol=MariaDBClient
port=4006
Services
Issuing maxctrl command we can see listed the RW-Service for servers Pato-Node0, Pato-Node1:
pato@patomaxgalera~$ maxctrl list services
+----------------------------------------------------------------------------------------+
¦ Service ¦ Router ¦ Connections ¦ Total Connections ¦ Servers ¦
+------------+----------------+-------------+-------------------+------------------------¦
¦ CLI ¦ cli ¦ 0 ¦ 2 ¦ ¦
+------------+----------------+-------------+-------------------+------------------------¦
¦ RW-Service ¦ readwritesplit ¦ 0 ¦ 0 ¦ Pato-Node0, Pato-Node1 ¦
+------------+----------------+-------------+-------------------+------------------------¦
¦ RO-Service ¦ readconnroute ¦ 0 ¦ 0 ¦ Pato-Node2 ¦
+----------------------------------------------------------------------------------------+
Testing Read Write Split Functionality
Preparation
Enable user statistics in both nodes so we can check user activity:
MariaDB [mysql]> set global userstat = 1 ;
Query OK, 0 rows affected (0.000 sec)
Connect to Database using MaxScale
We connect to MaxScale server listening in port 4006 for RW-Service:
$ mysql -u remoto -p -h patomaxgalera -P 4006
and then check we got connected to Pato-Node1 patomariag1 Slave server:
MariaDB [(none)]> select @@hostname ;
+-------------+
| @@hostname |
+-------------+
| patomariag1 |
+-------------+
1 row in set (0.000 sec)
Then do an INSERT and SELECT:
MariaDB [(none)]> use taller
Database changed
MariaDB [taller]> insert into r1 (c2,d3) values (@@hostname,now());
Query OK, 1 row affected (0.398 sec)
MariaDB [taller]> commit;
Query OK, 0 rows affected (0.000 sec)
MariaDB [taller]> select * from r1;
...
Validate Read Performed in Slave
Reviewing activity in server patomariag1 (Pato-Node1) we see the user remoto issues one SELECT command and got 15 rows:
pato@patomariag1~$ mysql
MariaDB [(none)]> select USER, ROWS_READ, ROWS_INSERTED, SELECT_COMMANDS, UPDATE_COMMANDS
-> from information_schema.user_statistics where user = 'remoto';
+--------+-----------+---------------+-----------------+-----------------+
| USER | ROWS_READ | ROWS_INSERTED | SELECT_COMMANDS | UPDATE_COMMANDS |
+--------+-----------+---------------+-----------------+-----------------+
| remoto | 15 | 0 | 1 | 0 |
+--------+-----------+---------------+-----------------+-----------------+
1 row in set (0.001 sec)
we can see there was no INSERT or UPDATE activity registered in this Slave node.
Validate Write Performed in Master
So checking the activity in server patomariagm (Pato-Node0) we see the user remoto issues one INSERT and one UPDATE:
pato@patomariagm~$ mysql
MariaDB [(none)]> select USER, ROWS_READ, ROWS_INSERTED, SELECT_COMMANDS, UPDATE_COMMANDS
-> from information_schema.user_statistics where user = 'remoto';
+--------+-----------+---------------+-----------------+-----------------+
| USER | ROWS_READ | ROWS_INSERTED | SELECT_COMMANDS | UPDATE_COMMANDS |
+--------+-----------+---------------+-----------------+-----------------+
| remoto | 0 | 1 | 0 | 1 |
+--------+-----------+---------------+-----------------+-----------------+
1 row in set (0.000 sec)
we can see there was no SELECT activity registered in the Master node.
The Read Write Split functionality is working!
Conclusion
As we can see the ReadWriteSplit MaxScale functionality in fact splits the queries sent to the Galera Cluster into write and read statements and send them accordingly to the Master or to any of the Slaves nodes.
For more information about ReadWriteSplit go to official MaxScale documentation.