Going a step further – ShardingSphere-Proxy of Amazon Aurora’s Read and Write Capability Extension

1 Introduction

Amazon Aurora is a relational database service developed by Amazon Cloud Technology. It provides full compatibility with open source databases MySQL and PostgreSQL, and can also provide performance and availability comparable to commercial databases. In terms of performance, Aurora MySQL can support five times the throughput with the same configuration as the open source standard MySQL, and Aurora PostgreSQL can support three times the throughput improvement with the same configuration as the open source standard PostgreSQL. From the perspective of scalability, Aurora has made functional enhancements and innovations in storage and computing, horizontal and vertical. Aurora supports up to 128TB of storage capacity, and supports dynamic shrinking of storage tiers in units of 10GB. In terms of computing, Aurora provides the scalability configuration of multiple read replicas to support the expansion of up to 15 read replicas in a region, provides a multi-master architecture to support the expansion of 4 write nodes in the same region, and provides serverless serverless. The architecture instance-level scale-up in seconds, providing a global database to achieve low-latency cross-region expansion of the database.

With the growth of user data volume, Aurora has provided good scalability. Can it further process more data volume and support more concurrent access? You can consider using the method of sub-database and sub-table to support the configuration of multiple underlying Aurora clusters. Based on this, a series of blogs including this blog will be introduced accordingly, aiming to provide a reference for you to choose to use proxy or JDBC when you split databases and tables.

This blog will focus on how to use ShardingSphere-Proxy, an open source sub-database and sub-table middleware tool, to build database clusters, covering sub-database and sub-table, read-write separation, dynamic configuration, etc.

2. Introduction to ShardingSphere-Proxy

Apache ShardingSphere is an ecosystem of open source distributed database solutions. It consists of three products, JDBC, Proxy, and Sidecar (under planning), which can be deployed independently and can be used in combination with hybrid deployment.

As middleware, ShardingSphere-Proxy is positioned as a transparent database proxy. It adopts the Apache2.0 protocol and continues to iterate versions. The latest version is 5.1.0, and currently supports MySQL and PostgreSQL versions. It is transparent to the application and is compatible with clients of the MySQL/PostgreSQL protocol. MySQL command line mysql, MySQL workbench, etc. can directly access ShardingSphere-Proxy.

The lower layer of ShardingSphere-Proxy can connect to different databases, which can be homogeneous or heterogeneous. Users can specify the sub-database sub-table or read-write separation rules of the underlying database in two ways: 1) Statically specify according to the yaml configuration file 2) Specify using the enhanced DistSQL language provided by ShardingSphere. Because DistSQL supports dynamic creation of rules without restarting Proxy itself, it becomes the focus of future development of ShardingSphere-Proxy.

As a database proxy, whether it can provide a connection pool to enhance user concurrent access connection processing is an aspect that needs to be considered. ShardingSphere-Proxy supports configuring a Hikari connection pool for each database when adding a data source and initializing it. Hikari is a connection pool widely used in the industry, with little performance loss, and is used as the default connection pool by SpringBoot. The connection pool of ShardingSphere-Proxy can support users to configure the maximum number of connections, the maximum idle time, and cache related information. In addition to the Hikari connection pool, ShardingSphere-Proxy also supports the configuration of other connection pools.

Syntax compatibility with existing SQL is also a key factor for users to measure database proxies, as this involves changing application code. Taking MySQL as an example, ShardingSphere supports most of the MySQL syntaxes, but there are also a few unsupported syntaxes, such as optimize tables, resource group management, user creation, and GRANT permission management. For details, please refer to the latest documentation of ShardingSphere.

The following will share my experimental tests on several dimensions of ShardingSphereProxy connecting to Aurora: 1) sub-database sub-table 2) dynamic expansion 3) read-write separation 4) multi-table join 5) failure recovery.

3. Environment Construction

3.1 Aurora cluster construction

First, create three sets of Aurora MySQL clusters according to the Aurora Cluster Creation Guide . The model is db.r5.2xlarge. Each cluster has one write node and one read node.

3.2 ShardingSphere-Proxy Construction

Start an EC2 node in the same Availability Zone as Aurora, the model is r5.8xlarge. Then install ShardingSphere-Proxy on it.

3.2.1 Download the installation package

Download the binary installation package directly and decompress it. Download the latest version 5.1.0, which has better support for DistSQL.

 wget https://dlcdn.apache.org/shardingsphere/5.1.0/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin.tar.gz
tar -xvf apache-shardingsphere-5.1.0-shardingsphere-proxy-bin.tar.gz

The library that comes with SharingSphereProxy includes the JDBC driver for PostgreSQL, but not the driver for MySQL. Because the created cluster is MySQL, you need to copy the MySQL JDBC driver to the lib directory.

 wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar
cp mysql-connector-java-5.1.47.jar apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/lib/

3.2.2 Configure the server side of Proxy

In the root directory of ShardingSphere-Proxy, there is a configuration file directory named conf, which contains a file named server.yaml, which is used to configure ShardingSphere-Proxy itself as a proxy to provide external service information and store meta information. The following is a configuration example, which configures user permission information, specific attribute information, and meta information stored in zookeeper in cluster mode.

 rules:
  - !AUTHORITY
    users:  //访问Proxy的用户名和密码信息
      - root@%:root
      - sharding@:sharding
    provider:  //控制用户对schema的登陆权限
      type: ALL_PRIVILEGES_PERMITTED
  - !TRANSACTION  //事务类型配置，支持本地事务、XA两阶段事务、BASE柔性事务
    defaultType: XA
    providerType: Atomikos

props:  //特定属性配置
  max-connections-size-per-query: 1
  proxy-hint-enabled: true //为强制路由使用，默认值为false

mode: //元信息存放的配置，shardingsphereProxy支持三种模式：内存、单机和集群
  type: Cluster
  repository:
    type: ZooKeeper //可以设置为zookeeper、etcd等
    props:
      namespace: shardingproxy
      server-lists: localhost:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500
  overwrite: false

3.3 Start Proxy

The bins directly under the root directory of ShardingSphereProxy correspond to start and stop scripts. Runtime logs are in the directory logs. start proxy

bin/start.sh
bin/stop.sh

3.4 Verifying the connection

If there is no special configuration, ShardingSphereProxy uses port 3307 by default. Log in to the proxy using the username and password configured in 3.2.2. Run the mysql command line tool on EC2 to connect, and the connection is successful. Note that there isn't any database here, as we didn't pre-configure the data source with a YAML configuration file.

 [ec2-user@ip-111-22-3-123 bin]$ mysql -h 127.0.0.1 -uroot --port 3307 -proot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.22-ShardingSphere-Proxy 5.1.0 

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
Empty set (0.01 sec)

4. Functional testing

4.1 DistSQL creates sharding rules and data sharding test

This section verifies the basic sub-database sub-table capability of ShardingSphere. ShardingSphereProxy supports two ways to create sharding rules and read-write separation rules, YAML and DistSQL. DistSQL extends the SQL syntax and can support online creation of data sources, creation and modification of table creation rules, and is more flexible. This article only introduces the use cases of DistSQL.

4.1.1 Create database

Connect to ShardingSphere-Proxy to create a database as a logical distributed database.

 MySQL [(none)]> create database distsql_sharding_db;
Query OK, 0 rows affected (0.90 sec)

Create databases on individual Aurora clusters to connect as database sources. Among them, rshard1, rshard2, rshard3 are the aliases I defined to connect to the Aurora database.

 alias rshard1=’mysql -h $dbname -u$username -p$password’

 [ec2-user@ ip-111-22-3-123 bin]$ rshard1 -e "create database dist_ds";
[ec2-user@ ip-111-22-3-123 bin]$ rshard2 -e "create database dist_ds;"
[ec2-user@ ip-111-22-3-123 bin]$ rshard3 -e "create database dist_ds;"

4.1.2 Create a data source

Run the following DistSQL statement in ShadingSphereProxy to create 3 data sources pointing to 3 different Aurora clusters

 MySQL [distsql_sharding_db]> add resource ds_0(url="jdbc:mysql://aurora-2-07-7-shard1.cluster-12345678.us-east-1.rds.amazonaws.com:3306/dist_ds?serverTimezone=UTC&useSSL=false",user=admin,password=12345678);
Query OK, 0 rows affected (0.03 sec)

MySQL [distsql_sharding_db]> add resource ds_1(url="jdbc:mysql://aurora-2-07-7-shard2.cluster-12345678.us-east-1.rds.amazonaws.com:3306/dist_ds?serverTimezone=UTC&useSSL=false",user=admin,password=12345678);
Query OK, 0 rows affected (0.06 sec)

MySQL [distsql_sharding_db]> add resource ds_2(url="jdbc:mysql://aurora-2-07-7-shard3.cluster-12345678.us-east-1.rds.amazonaws.com:3306/dist_ds?serverTimezone=UTC&useSSL=false",user=admin,password=12345678);
Query OK, 0 rows affected (0.05 sec)

4.1.3 Create sharding rules

The sharding rule of the t_order table is specified here. Note that the table name of the sharding rule is the same as the table name of the table to be created later. The specific rules are: hash the table according to the order_id for the three underlying data sources (Aurora cluster) and divide it into 6 parts. In addition, the strategy of automatic value generation is adopted for order_id, and the strategy is snowflake algorithm. ShardingSphere supports two distributed primary key generation strategies: UUID and Snowflake algorithm SNOWFLAKE. The primary key generated by using the snowflake algorithm has 4 parts in binary representation. The table from high to low is: 1bit sign bit, 41bit timestamp bit, 10bit worker process bit and 12bit serial number bit. Run the following DistSQL statement in ShardingSphereProxy to create sharding rules:

 MySQL [distsql_sharding_db]> CREATE SHARDING TABLE RULE t_order(
→ RESOURCES(ds_0,ds_1, ds_2),
→ SHARDING_COLUMN=order_id,
→ TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=6)),
→ KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME=snowflake))
→ );
Query OK, 0 rows affected (0.02 sec)

4.1.4 Create table

The table creation statement is the same as the ordinary MySQL table creation statement. Run the following statement in ShardingSphereProxy to create a table:

 MySQL [distsql_sharding_db]> CREATE TABLE `t_order` ( `order_id` bigint NOT NULL, `user_id` int NOT NULL, `status` varchar(45) DEFAULT NULL, PRIMARY KEY (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    -> ;
Query OK, 0 rows affected (0.22 sec)

Check the status of the table on ShardingSphereProxy.

 MySQL [distsql_sharding_db]> show tables;
+-------------------------------+------------+
| Tables_in_distsql_sharding_db | Table_type |
+-------------------------------+------------+
| t_order                       | BASE TABLE |
+-------------------------------+------------+
1 row in set (0.00 sec)

Connect to each of the three Aurora clusters to see if the table is automatically created. You can see that two tables are created on each underlying database cluster, for a total of 6 tables. And the table names are sorted numerically by "t_oder_"

 [ec2-user@ ip-111-22-3-123 bin]$ rshard1 -Ddist_ds -e "show tables;"
+-------------------+
| Tables_in_dist_ds |
+-------------------+
| t_order_0         |
| t_order_3         |
+-------------------+
[ec2-user@ ip-111-22-3-123 bin ]$ rshard2 -Ddist_ds -e "show tables;"
+-------------------+
| Tables_in_dist_ds |
+-------------------+
| t_order_1         |
| t_order_4         |
+-------------------+
[ec2-user@ ip-111-22-3-123 bin]$ rshard3 -Ddist_ds -e "show tables;"
+-------------------+
| Tables_in_dist_ds |
+-------------------+
| t_order_2         |
| t_order_5         |
+-------------------+

4.1.5 Inserting and finding data

Insert and search data in ShardingSphere-Proxy, the data can be inserted and searched successfully. Run in ShardingSphere-Proxy:

 MySQL [distsql_sharding_db]> insert into t_order(user_id, status) values (1, 'ok');
insert into t_order(user_id, status) values (2, 'abc');
Query OK, 1 row affected (0.01 sec)

MySQL [distsql_sharding_db]> insert into t_order(user_id, status) values (2, 'abc');
insert into t_order(user_id, status) values (3, 'abc');
Query OK, 1 row affected (0.00 sec)

MySQL [distsql_sharding_db]> insert into t_order(user_id, status) values (3, 'abc');
insert into t_order(user_id, status) values (4, 'abc');
Query OK, 1 row affected (0.01 sec)

MySQL [distsql_sharding_db]> insert into t_order(user_id, status) values (4, 'abc');
insert into t_order(user_id, status) values (5, 'abc');
Query OK, 1 row affected (0.00 sec)

MySQL [distsql_sharding_db]> insert into t_order(user_id, status) values (5, 'abc');
insert into t_order(user_id, status) values (6, 'abc');
Query OK, 1 row affected (0.01 sec)

MySQL [distsql_sharding_db]> insert into t_order(user_id, status) values (6, 'abc');
insert into t_order(user_id, status) values (7, 'abc');
Query OK, 1 row affected (0.00 sec)

MySQL [distsql_sharding_db]> insert into t_order(user_id, status) values (7, 'abc');
insert into t_order(user_id, status) values (8, 'abc');
Query OK, 1 row affected (0.01 sec)

MySQL [distsql_sharding_db]> insert into t_order(user_id, status) values (8, 'abc');
Query OK, 1 row affected (0.00 sec)

MySQL [distsql_sharding_db]> insert into t_order(user_id, status) values (9, 'abc');
Query OK, 1 row affected (0.00 sec)

MySQL [distsql_sharding_db]> select * from t_order;
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 708700161915748353 |       2 | abc    |
| 708700161995440128 |       5 | abc    |
| 708700169725542400 |       9 | abc    |
| 708700161877999616 |       1 | ok     |
| 708700161936719872 |       3 | abc    |
| 708700162041577472 |       7 | abc    |
| 708700161970274305 |       4 | abc    |
| 708700162016411649 |       6 | abc    |
| 708700162058354689 |       8 | abc    |
+--------------------+---------+--------+
9 rows in set (0.01 sec)

Go to each Aurora cluster to find the data inserted in the sub-table, and you can see that the 9 records inserted in the proxy are scattered into the bottom 6 tables. Because the order_id is generated by the snowflake algorithm and the amount of data is relatively small, the data here is not uniform.

 [ec2-user@ip-111-22-3-123 bin]$ rshard1 -Ddist_ds -e "select * from t_order_0;"
[ec2-user@ip-111-22-3-123 bin]$ rshard1 -Ddist_ds -e "select * from t_order_3;"
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 708700161915748353 |       2 | abc    |
+--------------------+---------+--------+
[ec2-user@ip-111-22-3-123 bin]$ rshard2 -Ddist_ds -e "select * from t_order_1;"
[ec2-user@ip-111-22-3-123 bin]$ rshard2 -Ddist_ds -e "select * from t_order_4;"
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 708700161995440128 |       5 | abc    |
| 708700169725542400 |       9 | abc    |
+--------------------+---------+--------+
[ec2-user@111-22-3-123 bin]$ rshard3 -Ddist_ds -e "select * from t_order_2;"
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 708700161877999616 |       1 | ok     |
| 708700161936719872 |       3 | abc    |
| 708700162041577472 |       7 | abc    |
+--------------------+---------+--------+
[ec2-user@ip-111-22-3-123 bin]$ rshard3 -Ddist_ds -e "select * from t_order_5;"
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 708700161970274305 |       4 | abc    |
| 708700162016411649 |       6 | abc    |
| 708700162058354689 |       8 | abc    |
+--------------------+---------+--------+</code></pre></div>

The above experiments have verified that ShardingSphere-Proxy has the ability to create logical libraries, connect data sources, create sharding rules, automatically create sub-tables on the underlying database when creating logical tables, and be able to execute query distribution and aggregation capabilities.

4.2 Dynamic scaling verification (online scaling of shards)

This section verifies whether ShardingSphere-Proxy has the ability to dynamically change the sharding rules of the table.

ShardingSphere-Proxy provides the ability to change sharding rules online, but if the sub-table has been created successfully according to the previous rules, no new sub-table will be created with the increase of the number of shards, and there will be no original sub-table. Tables are dropped as the number of shards decreases. So you need to manually create table names and migrate data on the underlying sharded database.

The number of shards in the table in Section 4.1 is increased from 6 to 9. Modifying the sharding rule itself can succeed, but subsequent searches will fail because no new sub-tables are created. Run the following DistSQL on ShardingSphere-Proxy:

 MySQL [distsql_sharding_db]> alter SHARDING TABLE RULE t_order(
    -> RESOURCES(ds_0,ds_1, ds_2),
    -> SHARDING_COLUMN=order_id,
    -> TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=9)),
    -> KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME=snowflake))
    -> );
Query OK, 0 rows affected (0.01 sec)

MySQL [distsql_sharding_db]> select * from t_order;
ERROR 1146 (42S02): Table 'dist_ds.t_order_6' doesn't exist

If the corresponding sub-tables are created on the sub-cluster at this time, and then look up on ShardingSphere-Proxy, there will be no more errors. Connect to 3 Aurora clusters, create subtables manually

 [ec2-user@ip-111-22-3-123 bin]$ rshard1 -Ddist_ds -e "create table t_order_6(order_id bigint not null, user_id int not null, status varchar(45) default null, primary key(order_id)) engine=innodb default charset=utf8mb4; "
[ec2-user@ ip-111-22-3-123 bin]$ rshard2 -Ddist_ds -e "create table t_order_7(order_id bigint not null, user_id int not null, status varchar(45) default null, primary key(order_id)) engine=innodb default charset=utf8mb4; "
[ec2-user@ip-111-22-3-123 bin]$ rshard3 -Ddist_ds -e "create table t_order_8(order_id bigint not null, user_id int not null, status varchar(45) default null, primary key(order_id)) engine=innodb default charset=utf8mb4; "

Proxy no longer reports an error when looking up the entire logical table. Run the following SQL on ShardingSphere-Proxy:

 MySQL [distsql_sharding_db]> select * from t_order;
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 708700161915748353 |       2 | abc    |
| 708700161995440128 |       5 | abc    |
| 708700169725542400 |       9 | abc    |
| 708700161877999616 |       1 | ok     |
| 708700161936719872 |       3 | abc    |
| 708700162041577472 |       7 | abc    |
| 708700161970274305 |       4 | abc    |
| 708700162016411649 |       6 | abc    |
| 708700162058354689 |       8 | abc    |
+--------------------+---------+--------+
9 rows in set (0.01 sec)

If new data is inserted, it will be mapped to the sub-table according to the new sharding rules. View the query plan of the SQL statement on ShardingSphere-Proxy:

 MySQL [distsql_sharding_db]> preview insert into t_order values(7, 100, 'new');
+------------------+---------------------------------------------+
| data_source_name | sql                                         |
+------------------+---------------------------------------------+
| ds_1             | insert into t_order_7 values(7, 100, 'new') |
+------------------+---------------------------------------------+
1 row in set (0.00 sec)

MySQL [distsql_sharding_db]> insert into t_order values(7, 100, 'new');
Query OK, 1 row affected (0.00 sec)

Log in to the Aurora subcluster to view the subtable, and you can see that the data has been inserted successfully.

 [ec2-user@ip-111-22-3-123 bin]$ rshard2 -Ddist_ds -e "select * from t_order_7;"
+----------+---------+--------+
| order_id | user_id | status |
+----------+---------+--------+
|        7 |     100 | new    |
+----------+---------+--------+

Let's take a look at the situation of reducing sharding online. If the number of shards is reduced, for example, to 3, the existing data in the table will not be migrated, and only part of the data can be obtained when searching for the entire table. Run the following DistSQL and SQL statements on ShardingSphere-Proxy:

 MySQL [distsql_sharding_db]> alter SHARDING TABLE RULE t_order(
    ->     RESOURCES(ds_0,ds_1, ds_2),
    ->     SHARDING_COLUMN=order_id,
    ->     TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=3)),
    ->     KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME=snowflake))
    ->     );
Query OK, 0 rows affected (0.02 sec)
MySQL [distsql_sharding_db]> select * from t_order;
+--------------------+---------+--------+
| order_id           | user_id | status |
+--------------------+---------+--------+
| 708700161877999616 |       1 | ok     |
| 708700161936719872 |       3 | abc    |
| 708700162041577472 |       7 | abc    |
+--------------------+---------+--------+
3 rows in set (0.00 sec)

After the above verification, our conclusion is that the sharding rules of ShardingSphereProxy can be changed online, but the creation of sub-tables and the redistribution of data need to be done manually.

4.3 Test of binding table and broadcast table

This section verifies the support of ShardingSphere-Proxy for multi-table join. Although the operations in the OLTP database are usually relatively simple, it may also involve multi-table joins. The optimization of ShardingSphereProxy for multi-table join supports binding table and broadcast table. If the two tables are bound tables and the shard key is used for join, the join of the two tables can be performed. By copying the small table to each node, the broadcast table can realize the fast join between the large table and the small table.

4.3.1 Binding table

The binding table of ShardingSphereProxy can bind two tables through CREATE SHARDING BINDING TABLE RULES in DistSQL. Here we take the t_order table mentioned in Section 4.1 and the newly created table t_order_item as an example.

Connect to ShardingSphere-Proxy and run the following DistSQL and SQL statements.

 MySQL [distsql_sharding_db]> CREATE SHARDING TABLE RULE t_order_item(
    ->  RESOURCES(ds_0,ds_1, ds_2),
    ->  SHARDING_COLUMN=order_id,
    -> TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=6)));
Query OK, 0 rows affected (0.04 sec)

MySQL [distsql_sharding_db]> CREATE TABLE `t_order_item` ( `order_id` bigint NOT NULL, `item_id` int NOT NULL, `name` varchar(45) DEFAULT NULL, PRIMARY KEY (`item_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;
Query OK, 0 rows affected (0.08 sec)

After creating the binding rule, check the join plan, we see that the join is pushed down to the join of the corresponding sub-table and sub-table. Run on ShardingSphere-Proxy:

 MySQL [distsql_sharding_db]>  CREATE SHARDING BINDING TABLE RULES (t_order,t_order_item);
Query OK, 0 rows affected (0.04 sec)

MySQL [distsql_sharding_db]> preview select * from t_order, t_order_item where t_order.order_id=t_order_item.order_id;
+------------------+------------------------------------------------------------------------------------------+
| data_source_name | sql                                                                                      |
+------------------+------------------------------------------------------------------------------------------+
| ds_0             | select * from t_order_0, t_order_item_0 where t_order_0.order_id=t_order_item_0.order_id |
| ds_0             | select * from t_order_3, t_order_item_3 where t_order_3.order_id=t_order_item_3.order_id |
| ds_1             | select * from t_order_1, t_order_item_1 where t_order_1.order_id=t_order_item_1.order_id |
| ds_1             | select * from t_order_4, t_order_item_4 where t_order_4.order_id=t_order_item_4.order_id |
| ds_2             | select * from t_order_2, t_order_item_2 where t_order_2.order_id=t_order_item_2.order_id |
| ds_2             | select * from t_order_5, t_order_item_5 where t_order_5.order_id=t_order_item_5.order_id |
+------------------+------------------------------------------------------------------------------------------+
6 rows in set (0.01 sec)

4.3.2 Broadcast Table

Broadcast table means that each table has a complete backup in each database, which can be specified by CREATE SHARDING BROADCAST TABLE RULES.

 MySQL [distsql_sharding_db]> CREATE SHARDING BROADCAST TABLE RULES (t_user);
Query OK, 0 rows affected (0.03 sec)

MySQL [distsql_sharding_db]> create table t_user (user_id int, name varchar(100));
Query OK, 0 rows affected (0.04 sec)

Log in to each shard Aurora cluster to view the created tables. It can be seen that the difference between the sub-table name of the shard table and the numerical sequence number at the end is that the name on each library corresponding to the broadcast table is the same, which is the logical table name itself.

 [ec2-user@ip-111-22-3-123 bin]$ rshard1 -D dist_ds -e "show tables like '%user%';"
+----------------------------+
| Tables_in_dist_ds (%user%) |
+----------------------------+
| t_user                     |
+----------------------------+
[ec2-user@ip-111-22-3-123 bin]$ rshard2 -D dist_ds -e "show tables like '%user%';"
+----------------------------+
| Tables_in_dist_ds (%user%) |
+----------------------------+
| t_user                     |
+----------------------------+
[ec2-user@ip-111-22-3-123 bin]$ rshard3 -D dist_ds -e "show tables like '%user%';"
+----------------------------+
| Tables_in_dist_ds (%user%) |
+----------------------------+
| t_user                     |
+----------------------------+

Run the join of the broadcast table and other tables in ShardingSphereProxy, using the local join method.

 MySQL [distsql_sharding_db]> preview select * from t_order, t_user where t_order.user_id=t_user.user_id;
+------------------+------------------------------------------------------------------------+
| data_source_name | sql                                                                    |
+------------------+------------------------------------------------------------------------+
| ds_0             | select * from t_order_0, t_user where t_order_0.user_id=t_user.user_id |
| ds_0             | select * from t_order_3, t_user where t_order_3.user_id=t_user.user_id |
| ds_1             | select * from t_order_1, t_user where t_order_1.user_id=t_user.user_id |
| ds_1             | select * from t_order_4, t_user where t_order_4.user_id=t_user.user_id |
| ds_2             | select * from t_order_2, t_user where t_order_2.user_id=t_user.user_id |
| ds_2             | select * from t_order_5, t_user where t_order_5.user_id=t_user.user_id |
+------------------+--------

The above experiment verifies that ShardingSphere-Proxy can support join of two binding tables, as well as join of broadcast table and sharding table. For the join of two unbound sharded tables, ShardingSphere-Proxy has a Federation function that is supported, but it is not very mature. It is recommended to continue to pay attention to it in the future.

4.4 Verification of read-write separation function

This section verifies the support of ShardingSphere-Proxy for read-write separation. As the business grows, the write and read loads can effectively provide the processing power of the entire database cluster on different database nodes. Aurora can meet the user's needs for writing and strongly consistent reading through the read/write endpoint, and the read-only endpoint can meet the user's non-strongly consistent reading needs. Aurora's read and write latency is at the millisecond level, which is much lower than MySQL's binlog-based logical replication, so there are many loads that can be directly hit read-only endpoints.

The read-write separation feature provided by ShardingSphereProxy can further encapsulate Aurora's read/write endpoints and read-only endpoints. Users can directly connect to the endpoint of the Proxy to perform automatic read-write separation. The processing logic of ShardingSphereProxy for special cases is: 1) In the same thread and the same database connection, if there is a write operation, subsequent read operations will be read from the main library 2) The read request can be forced to be sent to the write through the Hint mechanism Node (main library). The following will use the first cluster of Aurora's three clusters to verify the ability of ShardingSphere-Proxy to separate read and write.

4.4.1 Viewing Aurora Cluster Read/Write Endpoints and Read-Only Endpoints

An Aurora cluster has two endpoints, a write endpoint and a read endpoint.

4.4.2 Creating a database in an Aurora cluster

Connect to the Aurora cluster and run:

 [ec2-user@ip-111-22-3-123 ~]$ rdbw -e "create database wr_ds;"

4.4.3 Data source configuration

Create a data source on ShardingSphere-Proxy. The written data source points to Aurora's read-write endpoint, and the read data source points to Aurora's read-only endpoint. Note: In the case of domain names, ShardingSphereProxy only supports the creation of data sources through url, and has not yet supported the way through HOST and Port. Connect to ShardingSphere-Proxy to create a logical database distsql_rwsplit_db and add a data source to the database:

 MySQL [(none)]> create database distsql_rwsplit_db;
Query OK, 0 rows affected (0.02 sec)
MySQL [(none)]> use distsql_rwsplit_db;
Database changed
MySQL [distsql_rwsplit_db]> add resource write_ds(url="jdbc:mysql://aurora-2-07-7-shard1.cluster-12345678.us-east-1.rds.amazonaws.com:3306/wr_ds?serverTimezone=UTC&useSSL=false",user=admin,password=12345678), read_ds(url="jdbc:mysql://aurora-2-07-7-shard1.cluster-ro-12345678.us-east-1.rds.amazonaws.com:3306/wr_ds?serverTimezone=UTC&useSSL=false",user=admin,password=12345678);
Query OK, 0 rows affected (0.08 sec)

4.4.4 Read-write separation rule configuration

Create a read-write separation rule, send write requests to the write data source, and send read requests to the read data source. The difference from the sub-database sub-table rule that RULE must be followed by a table name is that the RULE here is followed by the name of the data source, which applies to all tables created in this database. Run the following DistSQL statement on ShardingSphere-Proxy:

 MySQL [distsql_ rwsplit_db]> CREATE READWRITE_SPLITTING RULE wr_ds (
    -> WRITE_RESOURCE=write_ds,
    -> READ_RESOURCES(read_ds),
    -> TYPE(NAME=random)
    -> );
Query OK, 0 rows affected (0.36 sec)

4.4.5 Create table

Create a common table. The table creation statement is the same as the MySQL table creation statement. Run the following SQL statement on ShardingSphere-Proxy:

 MySQL [distsql_ rwsplit_db]> create table wr_table (a int, b int, c varchar(20));
Query OK, 0 rows affected (0.17 sec)

4.4.6 Check whether read-write separation is implemented

Run the following statement on ShardingSphere-Proxy to view the query plan and see which underlying data source the statement is sent to. It can be seen that the write request is sent to the write node, and the read request is sent to the read-write point.

 MySQL [distsql_rwsplit_db]> preview insert into wr_table values(1,1,'ab');
+------------------+---------------------------------------+
| data_source_name | sql                                   |
+------------------+---------------------------------------+
| write_ds         | insert into wr_table values(1,1,'ab') |
+------------------+---------------------------------------+
1 row in set (0.10 sec)
MySQL [distsql_rwsplit_db]> preview select * from wr_table;
+------------------+------------------------+
| data_source_name | sql                    |
+------------------+------------------------+
| read_ds          | select * from wr_table |
+------------------+------------------------+
1 row in set (0.02 sec)

Run a script to perform multiple operations, and then go to Aurora cluster metrics monitoring to verify. The script is a loop that runs 1000 times, inserting a record each time and looking up the total number of records in the table.

 [ec2-user@ip-111-22-3-123 shardingproxy]$ cat testReadWrite.sh

 #!/bin/bash
n=1
while [ $n -le 1000 ]
do
    mysql -h 127.0.0.1 -uroot --port 3307 -proot -Ddistsql_rwsplit_db -e "insert into wr_table values($n,$n,'ok');"
    mysql -h 127.0.0.1 -uroot --port 3307 -proot -Ddistsql_rwsplit_db -e "select count(*) from wr_table;"
    let n++
done

Looking at the read and write latency of the write and read nodes of the Aurora cluster, you can see that write latency occurs only on write nodes, and read latency occurs only on read nodes. Indicates that the read-write separation rule takes effect.

Although the replication latency between Aurora's write and read nodes is very low in the millisecond level, some applications still have strong consistency requirements, that is, they need to be read immediately after writing. At this time, the method of forcing the read request to be sent to the write node can be adopted. ShardingSphereProxy is supported by hint. First, you need to add a property proxy-hint-enabled: true to the aforementioned conf/server.yaml. Then explicitly set the readwrite_splitting hint source value to write in the connection to enable forced routing to the write node by setting the value to auto or clear hint to use the default rules. The readwrite_splitting hint source can take effect at the session level.

Run the following statements in sequence on ShardingSphere-Proxy. It can be seen that the default read request is sent to the read node. After the readwrite_splitting hint source is set to write, it will be sent to the write node, and then set to auto, which can be sent back to the read and write point.

 MySQL [distsql_rwsplit_db]> preview select count(*) from wr_table;
+------------------+-------------------------------+
| data_source_name | sql                           |
+------------------+-------------------------------+
| read_ds          | select count(*) from wr_table |
+------------------+-------------------------------+
1 row in set (0.01 sec)

MySQL [distsql_rwsplit_db]> set readwrite_splitting hint source = write;
Query OK, 0 rows affected (0.00 sec)

MySQL [distsql_rwsplit_db]> preview select count(*) from wr_table;
+------------------+-------------------------------+
| data_source_name | sql                           |
+------------------+-------------------------------+
| write_ds         | select count(*) from wr_table |
+------------------+-------------------------------+
1 row in set (0.01 sec)
MySQL [distsql_rwsplit_db]> set readwrite_splitting hint source = auto;
Query OK, 0 rows affected (0.00 sec)

MySQL [distsql_rwsplit_db]> preview select count(*) from wr_table;
+------------------+-------------------------------+
| data_source_name | sql                           |
+------------------+-------------------------------+
| read_ds          | select count(*) from wr_table |
+------------------+-------------------------------+
1 row in set (0.00 sec)

In addition, the way to change without using the YAML file is to set two variables proxy_hint_enabled and readwrite_splitting hint source directly in DistSQL.

 MySQL [distsql_rwsplit_db]> set variable proxy_hint_enabled=true;
Query OK, 0 rows affected (0.01 sec)
MySQL [distsql_rwsplit_db]> set readwrite_splitting hint source = write;
Query OK, 0 rows affected (0.01 sec)
MySQL [distsql_rwsplit_db]> preview select * from wr_table;
+------------------+------------------------+
| data_source_name | sql                    |
+------------------+------------------------+
| write_ds         | select * from wr_table |
+------------------+------------------------+
1 row in set (0.00 sec)

The above experiments verify that ShardingSphere-Proxy has good read-write separation ability. It verifies the underlying scenario of connecting a single Aurora cluster for read-write separation. ShardingSphere-Proxy is also supported if both database and table partitioning and read-write separation are required. For example, it is divided into 3 Aurora clusters first, and then each cluster needs to provide the ability of read-write separation. We can directly put the data source name (wr_ds in 4.4.4) defined after the read-write separation rule in the sub-database sub-table rules In the data source specified for each table (ds_0, ds_1, ds_2 in 4.1.3).

4.5 Failure Recovery Verification

This section verifies the awareness of ShardingSphere-Proxy for Aurora cluster failover. When the master-slave switchover occurs in the Aurora cluster, it is ideal if the proxy can dynamically detect the master-slave switchover and connect to the new master database. The experiment in this section is still to verify the first Aurora cluster.

The test script is as follows, it will continue to connect to the write node and send update requests with 1 second interval between each request.

 [ec2-user@ip-111-22-3-123 shardingproxy]$ cat testFailover.sh 
#!/bin/bash
while true
do
    mysql -h 127.0.0.1 -uroot --port 3307 -proot -Ddistsql_rwsplit_db -e "update wr_table set c='failover' where a = 1;"
    now=$(date +"%T")
    echo "update done: $now"
    sleep 1
done

Run the script, then click Action->Failover on the writer node of the Aurora cluster. Automatic switchover of Aurora write nodes and read nodes is started. During the switching process, the read/write endpoints and read-only endpoints of the entire cluster remain unchanged, but the underlying mapped nodes change.

By observing Aurora's Event (event), you can see that the entire failover is completed in about 30 seconds.

Unfortunately, the application directly connects to the ShardingSphereProxy, that is, the previous running script cannot automatically detect the underlying IP changes. Running the script keeps throwing an error:

 ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
update done: 15:04:04
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
update done: 15:04:05
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement
update done: 15:04:06

Connecting to the Proxy directly from the MySQL command line will have the same error.

 MySQL [distsql_rwsplit_db]> update wr_table set c="failover" where a =2;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

The reason for the analysis is that when Aurora fails over, the mapping between read and write endpoints and IP will change, and the connection pool of ShardingSphere is not updated to the new IP when connecting to Aurora. We can use the following workaround to make ShardingSphereProxy point to the new write node, that is, to recreate the data source. Although the definition of the data source itself has not changed, through the operation of rebuilding the alter resource of the data source, ShardingSphereProxy will re-fetch the mapping from endpoint to IP, so it can run successfully.

 MySQL [distsql_rwsplit_db]> alter resource write_ds(url="jdbc:mysql://aurora-2-07-7-shard1.cluster-12345678.us-east-1.rds.amazonaws.com:3306/wr_ds?serverTimezone=UTC&useSSL=false",user=admin,password=12345678), read_ds(url="jdbc:mysql://aurora-2-07-7-shard1.cluster-ro-12345678.us-east-1.rds.amazonaws.com:3306/wr_ds?serverTimezone=UTC&useSSL=false",user=admin,password=12345678);
Query OK, 0 rows affected (0.05 sec)

MySQL [distsql_rwsplit_db]> update wr_table set c="failover" where a =2;
Query OK, 1 row affected (0.01 sec)

Each time Aurora fails over, we can detect the failover event, or explicitly call the above statement when the application receives a read-only error. In order to reduce the impact on the application, we can use Lambda to automate the operation of failover resetting the data source. Because Aurora's failover events can be monitored, we can write a Lambda function that displays the call to change the resource operation after monitoring the failover success.

The general idea is: RDS passes Event notification information to the SNS topic through Event Subscription, and then passes the SNS topic to the Lambda method, and then explicitly connects the ShardingProxy in the Lambda method to call the DistSQL statement of the alter resource.

Specific steps are as follows:

4.5.1 Create SNS

Create an SNS by following the SNS creation guide. Open the SNS dashboard, click Create SNS topic, and select the Standard type. Other options are default or adjusted as needed.

4.5.2 Create an Event Subscription for the Aurora cluster to be performed

On the Event Subscriptions of RDS, click "Create Event Subscription", in the pop-up tab, select Target for the SNS created in the previous step, select Cluster as the Source type, select the Aurora cluster we need to pay attention to in the cluster, and select Failover for the event. event.

4.5.3 Create Lambda method

Because Lambda wants to call the ShardingProxy deployed on EC2 in the VPC, it should be bound to a special Role, which has permission to execute Lambda methods in the VPC: AWSLambdaVPCAccessExecutionRole Create Role and Policy according to the IAM Role creation document , so that the role of failoverlambda has Permissions for AWSLambdaVPCAccessExecutionRole.

Next follow the Lambda documentation to create a Lambda method

After creating the Lambda method, click Trigger, specify SNS, and specify the SNS topic created in 4.5.1.

4.5.4 Writing Lambda methods

 import os
import json
import pymysql

# connect to ShardingProxy to reset the data source    
def resetDataSource():
    db = pymysql.connect(host='111.22.3.123', user='root', password='root', port=3307, database='distsql_rwsplit_db')
    
    cur = db.cursor()
    SQL = "alter resource write_ds(url=\"jdbc:mysql://aurora-2-07-7-shard1.cluster-12345678.us-east-1.rds.amazonaws.com:3306/wr_ds?serverTimezone=UTC&useSSL=false\",user=admin,password=12345678), read_ds(url=\"jdbc:mysql://aurora-2-07-7-shard1.cluster-ro-12345678.us-east-1.rds.amazonaws.com:3306/wr_ds?serverTimezone=UTC&useSSL=false\",user=admin,password=12345678);"
    print (SQL)
    cur.execute(SQL)
    result = cur.fetchall()
    for x in result:
        print(x)
    db.close()

def lambda_handler(event, context):
    wholeMessage = event['Records'][0]['Sns']['Message']
    print ("whole message" + wholeMessage)
    
    wholeMessageInJson = json.loads(wholeMessage)
    eventMessage = wholeMessageInJson['Event Message']
    print ("event message: " + eventMessage)
    
    isFailover = eventMessage.startswith('Completed failover to DB instance')
    if isFailover == True:
        print ("Failover completed! " + eventMessage)
        resetDataSource()

    return {
        'statusCode': 200,
        'body': Lambda Invocation Successful!'
    }

The Lambda method is written in the Python language and is accessed in MySQL mode when accessing ShardingSphereProxy, so the lib library of pymysql needs to be introduced. The specific method is:

1) Install pymysql on Linux, take Amazon-Linux virtual machine as an example, it will be installed to the directory ./home/ec2-user/.local/lib/python3.7/site-packages/pymysql by default

2) Copy the pymysql directory to the temporary directory /tmp

3) Write the Lambda method and store it in the lambda_function.py file

4) package zip -r lambda.zip pymysql lambda_function.py

5) Upload via S3 or local on the console.

4.5.5 Set the security group of EC2 where ShardingSphereProxy is located

Because Lambda needs to access ShardingSphereProxy in the VPC, and ShardingSphereProxy runs on port 3307, you should configure the corresponding security group and open port 3307 for access within the same VPC. The security group configured according to the security group configuration document is as follows:

4.5.6 Verify failover

Repeat the operations at the beginning of this section, run testFailover.sh, and then manually failover the Aurora node on the RDS console page. You will find that testFailover.sh continues to output stably, and no read-only errors will occur.

 update done: 13:44:44
…
update done: 13:44:47
update done: 13:45:00
update done: 13:45:01
…
update done: 13:45:17

Go to cloudwatch to view the log of the Lambda function, and you will find that the Lambda is successfully invoked.

The above experiments verify the ability of ShardingSphere-Proxy to sense the failover of Aurora cluster. Although ShardingSphere-Proxy itself does not provide a good match, by monitoring Aurora cluster events to trigger the Lamdba method to explicitly reset the ShardingSphere-Proxy data source, we can achieve the failover capability of the combination of ShardingSphere-Proxy and Aurora.

5 Conclusion

This article expands Aurora's sub-database sub-table capabilities and read-write separation capabilities through the database middleware ShardingSphere-Proxy.

ShardingSphere-Proxy has a built-in connection pool, strong support for MySQL syntax, and excellent performance in sub-database sub-table and read-write separation. For multi-table joins, it can support joins of tables with the same sharding rules, as well as joins of small tables and large tables, which can basically meet the needs of OLTP scenarios. On dynamic sharding, ShardingSphere-Proxy provides the ability to change sharding rules online, but requires users to manually create sub-tables and migrate data in the underlying Aurora cluster, which requires a certain amount of work. In terms of failover, the integration of ShardingSphere-Proxy and Aurora is not very good, but the Aurora failover Event provided in this article can invoke the Lambda method to explicitly reset the data source, so that ShardingSphere-Proxy can sense the failover of Aurora cluster.

In general, the middleware product ShardingSphere-Proxy can still be a good match with the Aurora cluster, further improving the read and write capabilities of the Aurora cluster. It has good documentation and is an open source product that attracts more attention. It is recommended that readers evaluate this product when considering the implementation of Aurora sub-database and sub-table. In the future, we will continue to launch a series of blogs on the expansion and research of other middleware and JDBC.

Author of this article

Ma Lili
Amazon cloud technology database solution architect, more than ten years of experience in the database industry, has been involved in the development and research of NoSQL database Hadoop/Hive, enterprise-level database DB2, distributed data warehouse Greenplum/Apache HAWQ and Amazon cloud native database.

Click the link to learn more:

• Apache ShardingSphere GitHub address: https://github.com/apache/shardingsphere
• SphereEx official website: https://www.sphere-ex.com