June 26, 2017

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.

About Wagner Bianchi

Bianchi (@wagnerbianchijr) has been working with MySQL ecosystem for more than 11 years now, he is an Oracle ACE Director since 2014 and has worked on the most complex environments running Open Source solutions. Bianchi joint MariaDB RDBA Team in 2017 where he works as Principal Remote DBA.

Read all posts by Wagner Bianchi