RDS PostgreSQL支持中间件ShardingSphere,本文介绍如何基于ShardingSphere进行开发。

前提条件

RDS PostgreSQL所有版本实例均支持ShardingSphere。

背景信息

ShardingSphere适合分片彻底、数据库逻辑分片极其清晰的业务,支持如下功能:

  • 数据分片
    • 分库和分表
    • 读写分离
    • 分片策略定制化
    • 无中心化分布式主键
  • 分布式事务
    • 标准化事务接口
    • XA强一致事务
    • 柔性事务
  • 数据库治理
    • 配置动态化
    • 编排和治理
    • 数据脱敏
    • 可视化链路追踪
    • 弹性伸缩(规划中)

更多详情请参见ShardingSphere官方手册

ShardingSphere模式介绍

ShardingSphere支持多种模式,建议根据实际业务情况选择。各模式介绍如下表。

类别Sharding-JDBCSharding-ProxySharding-Sidecar
支持数据库任意实现JDBC规范的数据库(MySQL、PostgreSQL、Oracle、SQL Server等)MySQL、PostgreSQLMySQL、PostgreSQL
连接消耗数
异构语言支持仅Java任意任意
性能损耗低损耗略高损耗低
无中心化
静态入口

设置配置模板

  1. ECS实例上使用如下命令进入模板目录。
    cd apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin  
    cd conf
  2. 使用ll命令查看目录内所有文件。
    结果示例如下:
    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
    说明
    • config-encrypt.yaml:数据脱敏配置文件
    • config-master_slave.yaml:读写分离配置文件
    • config-sharding.yaml:数据分片配置文件
    • server.yaml:公共配置文件
  3. 修改配置文件。
    说明 配置文件的详细说明请参见ShardingSphere官方手册,本文仅以数据分片和公共配置进行简单说明。
    • 数据分片示例
      schemaName: #逻辑数据源名称  
      
      dataSources: #数据源配置,可配置多个data_source_name。 
        <data_source_name>: #与Sharding-JDBC配置不同,无需配置数据库连接池。
          url: #数据库URL连接  
          username: #数据库用户名  
          password: #数据库密码  
          connectionTimeoutMilliseconds: 30000 #连接超时毫秒数
          idleTimeoutMilliseconds: 60000 #空闲连接回收超时毫秒数
          maxLifetimeMilliseconds: 1800000 #连接最大存活时间毫秒数
          maxPoolSize: 65 #最大连接数
      
      shardingRule: #省略数据分片配置,与Sharding-JDBC配置一致。
    • 公共配置示例
      • Proxy属性
        #省略与Sharding-JDBC一致的配置属性  
        
        props:  
          acceptor.size: #用于设置接收客户端请求的工作线程个数,默认为CPU核数*2。  
          proxy.transaction.type: #默认为LOCAL事务,允许LOCAL,XA,BASE三个值,XA采用Atomikos作为事务管理器,BASE类型需要拷贝实现ShardingTransactionManager的接口的jar包至lib目录中。  
          proxy.opentracing.enabled: #是否开启链路追踪功能,默认为不开启。
          check.table.metadata.enabled: #是否在启动时检查分表元数据一致性,默认值: false。
          proxy.frontend.flush.threshold: # 对于单个大查询,每多少个网络包返回一次。
      • 权限验证
        # 用于执行登录Sharding Proxy的权限验证。配置用户名、密码、可访问的数据库后,必须使用正确的用户名、密码才可登录Proxy。  
        
        authentication:  
          users:  
            username: # 自定义用户名  
              password: **** # 用户对应的密码  
            sharding: # 自定义用户名  
              password: sharding # sharding用户对应的密码  
              authorizedSchemas: sharding_db, masterslave_db # 该用户授权可访问的数据库,多个用逗号分隔。缺省将拥有root权限,可访问全部数据库。

准备测试环境

  • ECS实例安装Java测试环境
    sudo yum install -y java
  • 配置PostgreSQL 12实例
    • 创建账号:r1
    • 账号对应密码:PW123321!
    • 创建数据库:db0、db1、db2、db3,并且owner为r1。
    • 实例白名单放通ECS的IP。
    说明
    配置实例环境
  • 使用vi命令编辑公共配置文件。
    vi /home/apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin/conf/server.yaml  
  • 参考如下示例,配置公共配置文件。
    authentication:  
      users:  
        r1:  
          password: PW123321!  
          authorizedSchemas: db0,db1,db2,db3  
    props:  
      executor.size: 16  
      sql.show: false

水平分片测试

  1. 修改数据分片配置文件。
    1. 使用vi命令打开公共配置文件。
      vi /home/apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin/conf/config-sharding.yaml
    2. 参考如下示例,配置公共配置文件。
      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. 启动ShardingSphere,监听8001端口。
    cd ~/apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin/bin/
    ./start.sh 8001
  3. 测试连接数据库。
    psql -h 127.0.0.1 -p 8001 -U r1 sdb
  4. 创建表。
    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);
    说明 创建表会根据配置的策略,在目标库中自动创建水平分片。 分表成功

常见问题

  • 如果想知道ShardingSphere的解析SQL、路由SQL的语句,请参见如下配置:
    • 使用vi命令打开公共配置文件。
      vi /home/apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin/conf/server.yaml
    • 参考如下示例,配置公共配置文件。
      authentication:  
        users:  
          r1:  
            password: PW123321!  
            authorizedSchemas: db0,db1,db2,db3  
      props:  
        executor.size: 16  
        sql.show: true  # 表示把解析的SQL打印到日志
  • 如果需要做写入、查询等测试,请参见如下命令:
    • 写入示例
      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());  
    • 查询示例1
      select * from t_order;
      结果示例
            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)  
    • 查询示例2
      sdb=> select * from t_order where user_id=1;
      结果示例
           order_id      | user_id | info | c1 |          crt_time            
      --------------------+---------+------+----+----------------------------  
       433352585668198400 |       1 | b    |  2 | 2020-02-09 19:48:27.726815  
      (1 row)
  • 如果需要查看ShardingSphere日志,路径如下:
    /home/apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin/logs/stdout.log
  • 如果需要使用pgbench压测,请参见如下命令:
    1. 创建测试sql文件并打开。
      vi test.sql
    2. 参考如下示例,配置pgbench压测命令。
      \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