Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Schema Sharding With MariaDB MaxScale 2.1 (Part 2)

DZone's Guide to

Schema Sharding With MariaDB MaxScale 2.1 (Part 2)

See how to SchemaRouter with ReadWriteSplit to make your database more scalable. Doing so will route reads to slave and writes to the shard’s masters.

· Database Zone ·
Free Resource

MariaDB TX, proven in production and driven by the community, is a complete database solution for any and every enterprise — a modern database for modern applications.

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 schemas (schemas 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 exist 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 (note: 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 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 configured both modules that 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 in the 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 at 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 the 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.

MariaDB AX is an open source database for modern analytics: distributed, columnar and easy to use.

Topics:
database ,sharding ,mariadb ,maxscale ,schemas ,scaling

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}