Schema Sharding with MariaDB MaxScale 2.1 – Part 2

In this second installment of schema sharing with MariaDB MaxScale to combine SchemaRouter and ReadWriteSplit MaxScale routers, we’ll go through the details of implementing it in order to shard databases among many pairs of master/slave servers.
Before we move forward, I would like to highlight that you need to remove any duplicate database schema or a schema that is present on both shards. Schemas should exist in just one of the shards as when the SchemaRouter plugin starts, it’s going to read all the database schemas that exists on all the shards to get to know where the query will be routed. Take care to use mysql_secure_instalation and remove test database schema to avoid the below error when sending queries to MaxScale that will resolve them with the backend database servers (this rule to avoid having duplicate databases among shards does not apply for mysql system database.):
2017-03-06 16:12:23 error : [schemarouter] Database 'test' found on servers 'Shard-A' and 'Shard-B' for user appuser@127.0.0.1. 2017-03-06 16:12:23 error : [schemarouter] Duplicate databases found, closing session.
We can yet execute simple tests for SchemaRouter and for ReadWriteSplit, as below:
#: schemarouter #: existing databases on shards [root@maxscale log]# mysql -u appuser -p123456 -h 127.0.0.1 -P 3306 -e "show databases;" +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | shard_a | <—— shard_a, box01 | shard_b | <—— shard_b, box03 | test | +--------------------+ #: creating tables on both existing shards/schemas [root@maxscale log]# mysql -u appuser -p123456 -h 127.0.0.1 -P 3306 -e "create table shard_a.t1(i int, primary key(i));" [root@maxscale log]# mysql -u appuser -p123456 -h 127.0.0.1 -P 3306 -e "create table shard_b.t1(i int, primary key(i));" [root@maxscale log]# mysql -u appuser -p123456 -h 127.0.0.1 -P 3306 -e "show tables from shard_a;" +-------------------+ | Tables_in_shard_a | +-------------------+ | t1 | +-------------------+ [root@maxscale log]# mysql -u appuser -p123456 -h 127.0.0.1 -P 3306 -e "show tables from shard_b;" +-------------------+ | Tables_in_shard_b | +-------------------+ | t1 | +-------------------+ #: testing the readwritesplit #: let’s write to master node of shard_a and get the hostname to check if it’s writing to the right node [root@maxscale log]# mysql -u appuser -p123456 -h 127.0.0.1 -P 3306 -e "insert into shard_a.t1 set i=1,server=(select @@hostname);” #: let’s check if it’s reading form the right node [root@maxscale log]# mysql -u appuser -p123456 -h 127.0.0.1 -P 3306 -e "select @@hostname,i,server from shard_a.t1;" +------------+---+--------+ | @@hostname | i | server | +------------+---+--------+ | box02 | 1 | box01 | +------------+---+--------+ #: let’s do the same for shard_b [root@maxscale log]# mysql -u appuser -p123456 -h 127.0.0.1 -P 3306 -e "insert into shard_b.t1 set i=1,server=(select @@hostname);" [root@maxscale log]# mysql -u appuser -p123456 -h 127.0.0.1 -P 3306 -e "select @@hostname,i,server from shard_b.t1;" +------------+---+--------+ | @@hostname | i | server | +------------+---+--------+ | box04 | 1 | box03 | +------------+---+--------+
In the above example, it’s valid to say that, you need to have server replicating in Row Based Replication, where binary log format should be configured as ROW. After these tests above, we can see some internal movement on MaxScale that makes it to increase statistic numbers/registers as you can see below:
[root@maxscale log]# maxadmin show service "Shard-A-Router" Service: Shard-A-Router Router: readwritesplit State: Started use_sql_variables_in: all slave_selection_criteria: LEAST_CURRENT_OPERATIONS master_failure_mode: fail_instantly max_slave_replication_lag: -1 retry_failed_reads: true strict_multi_stmt: true disable_sescmd_history: true max_sescmd_history: 0 master_accept_reads: false Number of router sessions: 24 Current no. of router sessions: 1 Number of queries forwarded: 47 Number of queries forwarded to master: 19 (40.43%) Number of queries forwarded to slave: 28 (59.57%) Number of queries forwarded to all: 46 (97.87%) Started: Mon Mar 6 17:03:19 2017 Root user access: Disabled Backend databases: 192.168.50.11:3306 Protocol: MySQLBackend Name: server1 192.168.50.12:3306 Protocol: MySQLBackend Name: server2 Total connections: 25 Currently connected: 1 [root@maxscale log]# maxadmin show service "Shard-B-Router" Service: Shard-B-Router Router: readwritesplit State: Started use_sql_variables_in: all slave_selection_criteria: LEAST_CURRENT_OPERATIONS master_failure_mode: fail_instantly max_slave_replication_lag: -1 retry_failed_reads: true strict_multi_stmt: true disable_sescmd_history: true max_sescmd_history: 0 master_accept_reads: false Number of router sessions: 24 Current no. of router sessions: 1 Number of queries forwarded: 25 Number of queries forwarded to master: 18 (72.00%) Number of queries forwarded to slave: 7 (28.00%) Number of queries forwarded to all: 46 (184.00%) Started: Mon Mar 6 17:03:19 2017 Root user access: Disabled Backend databases: 192.168.50.13:3306 Protocol: MySQLBackend Name: server3 192.168.50.14:3306 Protocol: MySQLBackend Name: server4 Total connections: 25 Currently connected: 1 [root@maxscale log]# maxadmin show service "Shard-Service" Invalid argument: Shard-Service [root@maxscale log]# maxadmin show service "Sharding-Service" Service: Sharding-Service Router: schemarouter State: Started Session Commands Total number of queries: 60 Percentage of session commands: 36.67 Longest chain of stored session commands: 0 Session command history limit exceeded: 0 times Session command history: enabled Session command history limit: unlimited Session Time Statistics Longest session: 0.00 seconds Shortest session: 0.00 seconds Average session length: 0.00 seconds Shard map cache hits: 9 Shard map cache misses: 13 Started: Mon Mar 6 17:03:25 2017 Root user access: Disabled Backend databases: 127.0.0.1:4006 Protocol: MySQLBackend Name: Shard-A 127.0.0.1:4007 Protocol: MySQLBackend Name: Shard-B Total connections: 23 Currently connected: 1
So, at this point as we have all well configured and the both modules we need to combine, the last part of this blog will be to run a sysbench individually for each of the shards, a and b, respectively hitting the ports 4006 and 4007 (as you can see on MaxScale configuration file abstraction) to have better number being reported. The sysbench I run are below:
[root@maxscale log]# sysbench --max-requests=0 --mysql-user=appuser --mysql-password=XXXXXX --mysql-db=shard_a --oltp-table-size=1000000 --oltp-read-only=off --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=off --db-ps-mode=disable --max-time=60 --oltp-reconnect-mode=transaction --mysql-host=127.0.0.1 --num-threads=16 --mysql-port=4006 --db-driver=mysql --test=oltp run sysbench 0.4.12: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 16 Doing OLTP test. Running mixed OLTP test Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases) Using "BEGIN" for starting transactions Using auto_inc on the id column Threads started! Time limit exceeded, exiting... (last message repeated 15 times) Done. OLTP test statistics: queries performed: read: 10886 write: 54430 other: 32658 total: 97974 transactions: 10886 (181.24 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 65316 (1087.45 per sec.) other operations: 32658 (543.73 per sec.) Test execution summary: total time: 60.0633s total number of events: 10886 total time taken by event execution: 960.5471 per-request statistics: min: 18.25ms avg: 88.24ms max: 692.52ms approx. 95 percentile: 158.38ms Threads fairness: events (avg/stddev): 680.3750/4.04 execution time (avg/stddev): 60.0342/0.02 [root@maxscale log]# sysbench --max-requests=0 --mysql-user=appuser --mysql-password=XXXXXX --mysql-db=shard_b --oltp-table-size=1000000 --oltp-read-only=off --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=off --db-ps-mode=disable --max-time=60 --oltp-reconnect-mode=transaction --mysql-host=127.0.0.1 --num-threads=16 --mysql-port=4007 --db-driver=mysql --test=oltp run sysbench 0.4.12: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 16 Doing OLTP test. Running mixed OLTP test Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases) Using "BEGIN" for starting transactions Using auto_inc on the id column Threads started! Time limit exceeded, exiting... (last message repeated 15 times) Done. OLTP test statistics: queries performed: read: 12638 write: 63190 other: 37914 total: 113742 transactions: 12638 (210.50 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 75828 (1262.99 per sec.) other operations: 37914 (631.49 per sec.) Test execution summary: total time: 60.0386s total number of events: 12638 total time taken by event execution: 960.2365 per-request statistics: min: 18.38ms avg: 75.98ms max: 316.19ms approx. 95 percentile: 120.72ms Threads fairness: events (avg/stddev): 789.8750/6.69 execution time (avg/stddev): 60.0148/0.01
And now we can have a look on the Maxadmin statistics for MaxScale:
[root@maxscale log]# maxadmin list services Services. --------------------------+-------------------+--------+----------------+------------------- Service Name | Router Module | #Users | Total Sessions | Backend databases --------------------------+-------------------+--------+----------------+------------------- Shard-A-Router | readwritesplit | 1 | 1 | server1, server2 Shard-B-Router | readwritesplit | 1 | 1 | server3, server4 Sharding-Service | schemarouter | 1 | 1 | Shard-A, Shard-B CLI | cli | 1 | 1 | MaxAdmin | cli | 3 | 4 | --------------------------+-------------------+--------+----------------+------------------- [root@maxscale log]# maxadmin show service "Sharding-Service" Service: Sharding-Service Router: schemarouter State: Started Session Commands Total number of queries: 66 Percentage of session commands: 36.36 Longest chain of stored session commands: 0 Session command history limit exceeded: 0 times Session command history: enabled Session command history limit: unlimited Session Time Statistics Longest session: 0.00 seconds Shortest session: 0.00 seconds Average session length: 0.00 seconds Shard map cache hits: 10 Shard map cache misses: 14 Started: Mon Mar 6 17:03:25 2017 Root user access: Disabled Backend databases: 127.0.0.1:4006 Protocol: MySQLBackend Name: Shard-A 127.0.0.1:4007 Protocol: MySQLBackend Name: Shard-B Total connections: 25 Currently connected: 1 [root@maxscale log]# maxadmin show service "Shard-A-Router" Service: Shard-A-Router Router: readwritesplit State: Started use_sql_variables_in: all slave_selection_criteria: LEAST_CURRENT_OPERATIONS master_failure_mode: fail_instantly max_slave_replication_lag: -1 retry_failed_reads: true strict_multi_stmt: true disable_sescmd_history: true max_sescmd_history: 0 master_accept_reads: false Number of router sessions: 10933 Current no. of router sessions: 1 Number of queries forwarded: 87248 Number of queries forwarded to master: 87213 (99.96%) Number of queries forwarded to slave: 35 (0.04%) Number of queries forwarded to all: 10957 (12.56%) Started: Mon Mar 6 17:03:19 2017 Root user access: Disabled Backend databases: 192.168.50.11:3306 Protocol: MySQLBackend Name: server1 192.168.50.12:3306 Protocol: MySQLBackend Name: server2 Total connections: 10934 Currently connected: 1 [root@maxscale log]# maxadmin show service "Shard-B-Router" Service: Shard-B-Router Router: readwritesplit State: Started use_sql_variables_in: all slave_selection_criteria: LEAST_CURRENT_OPERATIONS master_failure_mode: fail_instantly max_slave_replication_lag: -1 retry_failed_reads: true strict_multi_stmt: true disable_sescmd_history: true max_sescmd_history: 0 master_accept_reads: false Number of router sessions: 42504 Current no. of router sessions: 1 Number of queries forwarded: 190647 Number of queries forwarded to master: 190637 (99.99%) Number of queries forwarded to slave: 10 (0.01%) Number of queries forwarded to all: 42528 (22.31%) Started: Mon Mar 6 17:03:19 2017 Root user access: Disabled Backend databases: 192.168.50.13:3306 Protocol: MySQLBackend Name: server3 192.168.50.14:3306 Protocol: MySQLBackend Name: server4 Total connections: 42505 Currently connected: 1
Conclusion
We saw that MariaDB MaxScale can scale very well the database servers you have on your shards as MaxScale itself will route the traffic based on the database/schema names all over the database nodes, it’s done by the schemarouter, one of the modules or routers supported by MariaDB MaxScale 2.1.3. In this blog post, I combined SchemaRouter with ReadWriteSplit in order to make it more scalable as it’s going to route reads to slave and writes to shard’s masters. It’s good to say that, if you need to have more than one slave per shard, it’s a matter of adding another slave and MariaDB MaxScale will start routing queries to it. I would like to thank the MariaDB Engineering team for the support and for checking/adding features to make this combination of routers available on the new MariaDB MaxScale 2.1.3 version.
Post a Comment
Log into your MariaDB ID account to post a comment.