ShardingSphere development with ApsaraDB RDS for PostgreSQL

ShardingSphere modes

ShardingSphere offers multiple modes. Choose the mode that best suits your business requirements. The following table describes each mode.

Set up configuration templates

1. On your ECS instance, run the following commands to navigate to the template directory:

   cd apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin  
   cd conf

2. Run the ll command to view all files in the directory.
   Example output:

   total 24  
   -rw-r--r-- 1 501 games 3019 Jul 30  2019 config-encrypt.yaml  
   -rw-r--r-- 1 501 games 3582 Apr 22  2019 config-master_slave.yaml  
   -rw-r--r-- 1 501 games 4278 Apr 22  2019 config-sharding.yaml  
   -rw-r--r-- 1 501 games 1918 Jul 30  2019 server.yaml

   Note

   • config-encrypt.yaml: the data masking configuration file
   • config-master_slave.yaml: the read/write splitting configuration file
   • config-sharding.yaml: the data sharding configuration file
   • server.yaml: the common configuration file
3. Modify the configuration files.
   Note For details on the configuration files, see the ShardingSphere documentation. This topic provides a brief overview of the data sharding and common configurations.

   • Data sharding example

     schemaName: # The name of the logical schema.  
     dataSources: # The data source configuration. You can configure multiple data sources. 
       <data_source_name>: # Unlike in Sharding-JDBC, you do not need to configure a database connection pool.
         url: # The database connection URL.  
         username: # The database username.  
         password: # The database password.  
         connectionTimeoutMilliseconds: 30000 # The connection timeout in milliseconds.
         idleTimeoutMilliseconds: 60000 # The idle connection timeout in milliseconds.
         maxLifetimeMilliseconds: 1800000 # The maximum lifetime of a connection in milliseconds.
         maxPoolSize: 65 # The maximum pool size.
     shardingRule: # The data sharding configuration is omitted. It is the same as the configuration for Sharding-JDBC.

   • Common configuration example
     • Proxy properties

       # The configuration properties that are the same as in Sharding-JDBC are omitted.  
       props:  
         acceptor.size: # The number of worker threads for receiving client requests. Default value: Number of CPU cores × 2.  
         proxy.transaction.type: # The default transaction type is LOCAL. Valid values: LOCAL, XA, and BASE. If you set this to XA, Atomikos is used as the transaction manager. If you set this to BASE, you must copy the JAR package that implements the ShardingTransactionManager interface to the lib/ directory.  
         proxy.opentracing.enabled: # Specifies whether to enable distributed tracing. This feature is disabled by default.
         check.table.metadata.enabled: # Specifies whether to check the metadata consistency of sharded tables at startup. Default value: false.
         proxy.frontend.flush.threshold: # For a single large query, this specifies the number of network packets to process before sending a response.

     • Authentication

       # Verifies permissions when you log on to Sharding-Proxy. After you configure usernames, passwords, and accessible databases, you must log on to the proxy with the correct credentials.  
       authentication:  
         users:  
           username: # A custom username.  
             password: **** # The password for the user.  
           sharding: # A custom username.  
             password: sharding # The password for the sharding user.  
             authorizedSchemas: sharding_db, masterslave_db # The databases that the user is authorized to access. Separate multiple databases with a comma. If this parameter is not specified, the user has root permissions and can access all databases.

Set up a test environment

• Install a Java environment on your ECS instance.

  sudo yum install -y java

• Configure a PostgreSQL 12 instance.
  • Create an account with the username r1.
  • Set the password for the account to PW123321!.
  • Create the databases db0, db1, db2, and db3, and set r1 as the owner.
  • Add the IP address of your ECS instance to the IP whitelist of the ApsaraDB RDS for PostgreSQL instance.
  Note

  • For more information, see Quickly create an ApsaraDB RDS for PostgreSQL instance and Create an account and a database.
  • For more information about how to configure an IP whitelist, see Configure an IP whitelist.
• Use the vi command to edit the common configuration file.

  vi /home/apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin/conf/server.yaml  

• Configure the common configuration file as shown in the following example.

  authentication:  
    users:  
      r1:  
        password: PW123321!  
        authorizedSchemas: db0,db1,db2,db3  
  props:  
    executor.size: 16  
    sql.show: false

Test horizontal sharding

1. Modify the data sharding configuration file.
   1. Use the vi command to open the data sharding configuration file.

      vi /home/apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin/conf/config-sharding.yaml

   2. Configure the data sharding configuration file as shown in the following example.

      schemaName: sdb
      dataSources:
        db0:
          url: jdbc:postgresql://pgm-bpxxxxx.pg.rds.aliyuncs.com:1433/db0
          username: r1
          password: PW123321!
          connectionTimeoutMilliseconds: 30000
          idleTimeoutMilliseconds: 60000
          maxLifetimeMilliseconds: 1800000
          maxPoolSize: 65
        db1:
          url: jdbc:postgresql://pgm-bpxxxxx.pg.rds.aliyuncs.com:1433/db1
          username: r1
          password: PW123321!
          connectionTimeoutMilliseconds: 30000
          idleTimeoutMilliseconds: 60000
          maxLifetimeMilliseconds: 1800000
          maxPoolSize: 65
        db2:
          url: jdbc:postgresql://pgm-bpxxxxx.pg.rds.aliyuncs.com:1433/db2
          username: r1
          password: PW123321!
          connectionTimeoutMilliseconds: 30000
          idleTimeoutMilliseconds: 60000
          maxLifetimeMilliseconds: 1800000
          maxPoolSize: 65
        db3:
          url: jdbc:postgresql://pgm-bpxxxxx.pg.rds.aliyuncs.com:1433/db3
          username: r1
          password: PW123321!
          connectionTimeoutMilliseconds: 30000
          idleTimeoutMilliseconds: 60000
          maxLifetimeMilliseconds: 1800000
          maxPoolSize: 65
      shardingRule:
        tables:
          t_order:
            actualDataNodes: db${0..3}.t_order${0..7}
            databaseStrategy:
              inline:
                shardingColumn: user_id
                algorithmExpression: db${user_id % 4}
            tableStrategy:
              inline:
                shardingColumn: order_id
                algorithmExpression: t_order${order_id % 8}
            keyGenerator:
              type: SNOWFLAKE
              column: order_id
          t_order_item:
            actualDataNodes: db${0..3}.t_order_item${0..7}
            databaseStrategy:
              inline:
                shardingColumn: user_id
                algorithmExpression: db${user_id % 4}
            tableStrategy:
              inline:
                shardingColumn: order_id
                algorithmExpression: t_order_item${order_id % 8}
            keyGenerator:
              type: SNOWFLAKE
              column: order_item_id
        bindingTables:
          - t_order,t_order_item
        defaultTableStrategy:
          none:

2. Start ShardingSphere on port 8001.

   cd ~/apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin/bin/
   ./start.sh 8001

3. Test the database connection.

   psql -h 127.0.0.1 -p 8001 -U r1 sdb

4. Create tables.

   create table t_order(order_id int8 primary key, user_id int8, info text, c1 int, crt_time timestamp);  
   create table t_order_item(order_item_id int8 primary key, order_id int8, user_id int8, info text, c1 int, c2 int, c3 int, c4 int, c5 int, crt_time timestamp);

   Note Creating tables automatically creates horizontal shards in the target databases based on the configured strategy. In the public schema of the db0 database, eight sharded tables from t_order0 to t_order7 and eight sharded tables from t_order_item0 to t_order_item7 are created.

FAQ

• To view SQL parsing and routing statements in ShardingSphere, use the following configuration:
  • Use the vi command to open the common configuration file.

    vi /home/apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin/conf/server.yaml

  • Configure the common configuration file as shown in the following example.

    authentication:  
      users:  
        r1:  
          password: PW123321!  
          authorizedSchemas: db0,db1,db2,db3  
    props:  
      executor.size: 16  
      sql.show: true  # Logs the parsed SQL statements.

• To test writes and queries, run the following commands:
  • Write example

    insert into t_order (user_id, info, c1, crt_time) values (0,'a',1,now());  
    insert into t_order (user_id, info, c1, crt_time) values (1,'b',2,now());  
    insert into t_order (user_id, info, c1, crt_time) values (2,'c',3,now());  
    insert into t_order (user_id, info, c1, crt_time) values (3,'c',4,now());  

  • Query example 1

    select * from t_order;

    Sample result

          order_id      | user_id | info | c1 |          crt_time            
    --------------------+---------+------+----+----------------------------  
     433352561047633921 |       0 | a    |  1 | 2020-02-09 19:48:21.856555  
     433352585668198400 |       1 | b    |  2 | 2020-02-09 19:48:27.726815  
     433352610813050881 |       2 | c    |  3 | 2020-02-09 19:48:33.721754  
     433352628370407424 |       3 | c    |  4 | 2020-02-09 19:48:37.907683  
    (4 rows)  

  • Query example 2

    sdb=> select * from t_order where user_id=1;

    Sample result

         order_id      | user_id | info | c1 |          crt_time            
    --------------------+---------+------+----+----------------------------  
     433352585668198400 |       1 | b    |  2 | 2020-02-09 19:48:27.726815  
    (1 row)

• To view the ShardingSphere logs, check the following path:

  /home/apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin/logs/stdout.log

• To stress-test with pgbench, follow these steps:
  1. Create and open a test SQL file.

     vi test.sql

  2. Run the pgbench stress test as shown in the following example.

     \set user_id random(1,100000000)  
     \set order_id random(1,2000000000)  
     \set order_item_id random(1,2000000000)  
     insert into t_order (user_id, order_id, info, c1 , crt_time) values (:user_id, :order_id,random()::text, random()*1000, now()) on conflict (order_id) do update set info=excluded.info,c1=excluded.c1,crt_time=excluded.crt_time;   
     insert into t_order_item (order_item_id, user_id, order_id, info, c1,c2,c3,c4,c5,crt_time) values (:order_item_id, :user_id,:order_id,random()::text, random()*1000,random()*1000,random()*1000,random()*1000,random()*1000, now()) on conflict(order_item_id) do nothing;  
     pgbench -M simple -n -r -P 1 -f ./test.sql -c 24 -j 24  -h 127.0.0.1 -p 8001 -U r1 sdb -T 120  
     progress: 1.0 s, 1100.9 tps, lat 21.266 ms stddev 6.349  
     progress: 2.0 s, 1253.0 tps, lat 18.779 ms stddev 7.913  
     progress: 3.0 s, 1219.0 tps, lat 20.083 ms stddev 13.212