MariaDB MaxScale Read and Write Splitting
Table of Contents
(the image is supposed to be like that ;P )
Maxscale is a new open source product from MariaDB. It’s a MySQL/MariaDB proxy and loadbalancer, and, what’s most interesting to me, is that it can send write queries to one particular node of a cluster and reads to other nodes, thereby avoiding some nastiness in terms of writing to multiple nodes. There are some caveats to writing to multiple masters and not every application can.
MariaDB MaxScale is an open-source, database-centric proxy that works with MariaDB Enterprise, MariaDB Enterprise Cluster, MariaDB 5.5, MariaDB 10 and Oracle MySQL®. It’s pluggable architecture is designed to increase flexibility and aid customization.
Read Write Split
It’s fairly common to want to try to split reads and writes up using some kind of proxy system. I’m not up to date on the history of the requirement, but it has been around for a long time. There are, I believe, a few systems that try to do the RW split, but I’m not sure anything has really succeeded.
As an OpenStack operator I’m keenly aware that I need a highly available MySQL/MariaDB Galera cluster, but also that I can only, as far as I know at this time, write to one of the cluster nodes at a time. If I write to all the nodes using a master/master strategy I’ll run into issues. This is better laid out by this Percona post. Some parts of OpenStack use a “SELECT … FOR UPDATE”:
The SELECT … FOR UPDATE construct reads the given records in InnoDB, and locks the rows that are read from the index the query used, not only the rows that it returns. Given how write set replication works, the row locks of SELECT … FOR UPDATE are not replicated. – From the above Percona post
But, using some kind of load balancing system, eg. haproxy, so that I only write to one cluster node also means that I only read from one cluster node as well. That’s where MaxScale’s ability to split read and writes comes into play.
I should note there is a good post on SeveralNine’s site that has some good information in it on MaxScale.
Install MaxScale
I created an account on MariaDB’s site. MaxScale is, I believe, GPL2. But in order to grab the deb file from MariaDB you need to login and get your own unique repository. Once I did that I configured the repository and now MaxScale is available to install via apt. You could always download the source and compile on your own if you would like.
ubuntu@maxscale-1:~$ apt-cache policy maxscale
maxscale:
Installed: 1.2.0
Candidate: 1.2.0
Version table:
*** 1.2.0 0
1000 http://downloads.mariadb.com/enterprise//mariadb-maxscale/latest/ubuntu/ trusty/main amd64 Packages
100 /var/lib/dpkg/status
</code>
</pre>
## Configuration
Surprisingly MaxScale is pretty straight forward to configure for read/write splitting.
This is what my config file looks like (just in my testing phase, so this is not in production at all):
[maxscale]
threads=4
[Splitter Service]
type=service
router=readwritesplit
servers=dbserv1,dbserv2,dbserv3
user=maxscale
passwd=7313125C85ABDFB93A4CE397FC2B198D
max_slave_connections=100%
router_options=slave_selection_criteria=LEAST_CURRENT_OPERATIONS
[Splitter Listener]
type=listener
service=Splitter Service
protocol=MySQLClient
port=3306
socket=/tmp/ClusterMaster
[dbserv1]
type=server
address=192.168.44.32
port=3306
protocol=MySQLBackend
[dbserv2]
type=server
address=192.168.44.33
port=3306
protocol=MySQLBackend
[dbserv3]
type=server
address=192.168.44.34
port=3306
protocol=MySQLBackend
[Galera Monitor]
type=monitor
module=galeramon
diable_master_failback=1
servers=dbserv1, dbserv2, dbserv3
user=maxscale
passwd=7313125C85ABDFB93A4CE397FC2B198D
[CLI]
type=service
router=cli
[CLI Listener]
type=listener
service=CLI
protocol=maxscaled
address=localhost
port=6603
As you can see each of the three nodes in my cluster is listed in the configuration file: dbserv1, dbserv2, and dbserv3.
I like the config file. While I hand-rolled this one, the format certainly lends itself to automation.
Now maxscale can be started.
## maxpasswd and maxkeys
I should mention that the "password" in the configuration file above was creating using a combination of maxpasswd and maxkeys. I believe you can just enter the plaintext password into the config file if you want to avoid that extra step.
## Start maxscale
If you used the deb to install, then it comes with the init scripts. Just use "service maxscale start" and it should start up.
ubuntu@maxscale-1:/etc$ sudo service maxscale status
* Checking MaxScale
* maxscale is running
## maxadmin
MaxScale has a command line interface to MaxScale.
ubuntu@maxscale-1:/etc$ maxadmin -pmariadb list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
dbserv1 | 192.168.44.32 | 3306 | 5 | Slave, Synced, Running
dbserv2 | 192.168.44.33 | 3306 | 83 | Master, Synced, Running
dbserv3 | 192.168.44.34 | 3306 | 89 | Slave, Synced, Running
-------------------+-----------------+-------+-------------+--------------------
As we can see above, MaxScale has made dbserv2 "the master" which is a MaxScale only construction (ie. has nothing really to do with the cluster itself, rather it'll send writes to that node and reads to the other, assuming it's setup in a RW split configuration).
We can list the services configured.
ubuntu@maxscale-1:/etc$ maxadmin -pmariadb list services
Services.
--------------------------+----------------------+--------+---------------
Service Name | Router Module | #Users | Total Sessions
--------------------------+----------------------+--------+---------------
Splitter Service | readwritesplit | 2 | 54357
CLI | cli | 2 | 68
--------------------------+----------------------+--------+---------------
We can also check stats on the splitter service.
ubuntu@maxscale-1:/etc$ maxadmin -pmariadb show service "Splitter Service"
Service 0x35bc5e0
Service: Splitter Service
Router: readwritesplit (0x7f0ad8ace4a0)
State: Started
Number of router sessions: 54339
Current no. of router sessions: 0
Number of queries forwarded: 4052477
Number of queries forwarded to master: 2704498
Number of queries forwarded to slave: 1347979
Number of queries forwarded to all: 55274
Started: Sun Sep 27 21:54:32 2015
Root user access: Disabled
Backend databases
192.168.44.34:3306 Protocol: MySQLBackend
192.168.44.33:3306 Protocol: MySQLBackend
192.168.44.32:3306 Protocol: MySQLBackend
Users data: 0x35168d0
Total connections: 54357
Currently connected: 2
SSL: Disabled
While running a test we can check sessions too.
ubuntu@maxscale-1:/etc$ maxadmin -pmariadb list sessions
Sessions.
-----------------+-----------------+----------------+--------------------------
Session | Client | Service | State
-----------------+-----------------+----------------+--------------------------
0x38819f0 | 127.0.0.1 | CLI | Session ready for routing
0x7f0ab4044620 | 192.168.44.35 | Splitter Service | Session ready for routing
0x7f0ab4063280 | 192.168.44.35 | Splitter Service | Session ready for routing
0x7f0ab4001800 | 192.168.44.35 | Splitter Service | Session ready for routing
0x7f0ab403a630 | 192.168.44.35 | Splitter Service | Session ready for routing
0x7f0ab4029110 | 192.168.44.35 | Splitter Service | Session ready for routing
0x7f0ab00c60d0 | 192.168.44.35 | Splitter Service | Session ready for routing
0x7f0ab00c73a0 | 192.168.44.35 | Splitter Service | Session ready for routing
0x7f0ab4017710 | 192.168.44.35 | Splitter Service | Session ready for routing
0x7f0ab4090310 | 192.168.44.35 | Splitter Service | Session ready for routing
0x7f0aa80113e0 | 192.168.44.35 | Splitter Service | Session ready for routing
0x7f0aa8034430 | 192.168.44.35 | Splitter Service | Session ready for routing
0x7f0aa8032e30 | 192.168.44.35 | Splitter Service | Session ready for routing
0x7f0aa8032180 | 192.168.44.35 | Splitter Service | Session ready for routing
0x7f0ab405e270 | 192.168.44.35 | Splitter Service | Session ready for routing
0x7f0aa8022f20 | 192.168.44.35 | Splitter Service | Session ready for routing
0x35dfa90 | 192.168.44.35 | Splitter Service | Session ready for routing
0x7f0aa8022790 | 192.168.44.35 | Splitter Service | Session ready for routing
0x35c9070 | 192.168.44.35 | Splitter Service | Session ready for routing
0x7f0ab4000de0 | 192.168.44.35 | Splitter Service | Session ready for routing
0x35c95b0 | 192.168.44.35 | Splitter Service | Session ready for routing
0x35b8b90 | 192.168.44.35 | Splitter Service | Session ready for routing
0x35c7b30 | | CLI | Listener Session
0x35b8960 | | Splitter Service | Listener Session
0x35b98f0 | | Splitter Service | Listener Session
-----------------+-----------------+----------------+--------------------------
## Stop a cluster node
If I stop MariaDB on one of the cluster nodes, MaxScale knows:
ubuntu@maxscale-1:/etc$ maxadmin -pmariadb list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
dbserv1 | 192.168.44.32 | 3306 | 5 | Slave, Synced, Running
dbserv2 | 192.168.44.33 | 3306 | 83 | Master, Synced, Running
dbserv3 | 192.168.44.34 | 3306 | 108 | Down
-------------------+-----------------+-------+-------------+--------------------
MaxScale will only bring the node back into rotation once it's synced up.
## Test it out
First, I tried using sysbench. However I found that sysbench seems to work in such a way that it will only, even though maxscale, connect to one node of the cluster at time. I'll have to look into this. I was even using the "--oltp-skip-trx=on" option.
In the end I just used a mysqlslap test that is running a simple select from the fake employees database I installed. The 192.168.77.6 IP address is that of the MaxScale node.
ubuntu@mysql-client-1:~$ cat mysqlslap.sh
#!/bin/bash
mysqlslap \
--user=sysbench \
--password=syb3nch \
--host=192.168.77.6 \
--concurrency=20 \
--number-of-queries=1000 \
--create-schema=employees \
--query="/home/ubuntu/select.sql" \
--delimiter=";" \
--verbose \
--iterations=2 \
--debug-info
ubuntu@mysql-client-1:~$ cat select.sql
SELECT * FROM employees;
Here's innotop output on one of the nodes:
root@dbsrv1:/home/ubuntu# innotop --count 1 --nonint
cmd mysql_thread_id state user hostname db time info
Query 132 Sending data sysbench 192.168.77.6 employees 00:03 SELECT * FROM employees
Query 135 Sending data sysbench 192.168.77.6 employees 00:03 SELECT * FROM employees
Query 139 Sending data sysbench 192.168.77.6 employees 00:03 SELECT * FROM employees
Query 141 Sending data sysbench 192.168.77.6 employees 00:03 SELECT * FROM employees
Query 130 Writing to net sysbench 192.168.77.6 employees 00:02 SELECT * FROM employees
Query 136 Sending data sysbench 192.168.77.6 employees 00:02 SELECT * FROM employees
Query 129 Sending data sysbench 192.168.77.6 employees 00:01 SELECT * FROM employees
Query 131 Sending data sysbench 192.168.77.6 employees 00:01 SELECT * FROM employees
Query 133 Sending data sysbench 192.168.77.6 employees 00:01 SELECT * FROM employees
Query 137 Sending data sysbench 192.168.77.6 employees 00:01 SELECT * FROM employees
Query 138 Sending data sysbench 192.168.77.6 employees 00:01 SELECT * FROM employees
Query 134 Sending data sysbench 192.168.77.6 employees 00:00 SELECT * FROM employees
Query 140 Sending data sysbench 192.168.77.6 employees 00:00 SELECT * FROM employees
And on the "master" node, which shouldn't be getting any reads:
root@dbsrv2:/home/ubuntu# innotop --nonint --count 1
cmd mysql_thread_id state user hostname db time info
These are the results of my overly simplistic test using the MaxScale proxy:
ubuntu@mysql-client-1:~$ ./mysqlslap.sh
Benchmark
Average number of seconds to run all queries: 112.675 seconds
Minimum number of seconds to run all queries: 111.592 seconds
Maximum number of seconds to run all queries: 113.759 seconds
Number of clients running queries: 20
Average number of queries per client: 50
User time 137.57, System time 69.91
Maximum resident set size 717064, Integral resident set size 0
Non-physical pagefaults 2160414, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 610086, Involuntary context switches 277132
And then without it...going directly to a single node:
ubuntu@mysql-client-1:~$ ./mysqlslap.sh
Benchmark
Average number of seconds to run all queries: 194.296 seconds
Minimum number of seconds to run all queries: 193.582 seconds
Maximum number of seconds to run all queries: 195.011 seconds
Number of clients running queries: 20
Average number of queries per client: 50
User time 144.00, System time 71.97
Maximum resident set size 746008, Integral resident set size 0
Non-physical pagefaults 1948959, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 1781510, Involuntary context switches 163916
## Basic introduction
So that was my initial exploration of MaxScale. Seems promising. The biggest problem is that I don't know a whole heck of a lot about databases. Despite being a sysadmin for quite a while, I've not had to do much with databases, be it performance or otherwise, so the limitations here seem to be on my end.
The other issue is that MaxScale is relatively new. I'd be willing to try it out in a staging environment for sure. Using it might require a support contract for me to feel entirely comfortable, but then again if I had issues with it I could just pull it from use and go straight to on node. I don't say that lightly either because I don't have much in the way of support contracts right now. ;)
Hopefully in the future I can do more testing with MaxScale as it seems extremely useful, especially in terms of being able to stop servers and split read/write.
I also have some more work to do with sysbench. It seems like it's the best tool, that I know of, to test database performance with, but it is not working well with MaxScale.