BenchmarkSQL ShardingSphere Proxy 分片性能测试

测试目的

使用 BenchmarkSQL 工具测试 ShardingSphere Proxy 的分片性能。

测试方法

ShardingSphere Proxy 支持通过 BenchmarkSQL 5.0 进行 TPC-C 测试。 除本文说明的内容外，BenchmarkSQL 操作步骤按照原文档 HOW-TO-RUN.txt 即可。

测试工具微调

与单机数据库压测不同，分布式数据库解决方案难免在功能支持上有所取舍。使用 BenchmarkSQL 压测 ShardingSphere Proxy 建议进行如下调整。

移除外键与 extraHistID

修改 BenchmarkSQL 目录下 run/runDatabaseBuild.sh，文件第 17 行。

修改前：

  1. AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"

修改后：

  1. AFTER_LOAD="indexCreates buildFinish"

压测环境或参数建议

注意：本节中提到的任何参数都不是绝对值，都需要根据实际测试结果进行调整或取舍。

建议使用 Java 17 运行 ShardingSphere

编译 ShardingSphere 可以使用 Java 8。

使用 Java 17 可以在默认情况下尽量提升 ShardingSphere 的性能。

ShardingSphere 数据分片建议

对 BenchmarkSQL 的数据分片，可以考虑以各个表中的 warehouse id 作为分片键。

其中一个表 bmsql_item 没有 warehouse id，数据量固定 10 万行：

  • 可以取 i_id 作为分片键。但可能会导致同一个 Proxy 连接同时持有多个不同数据源的连接。
  • 或考虑不做分片，存在单个数据源内。可能会导致某一数据源压力较大。
  • 或对 i_id 进行范围分片，例如 1-50000 分布在数据源 0、50001-100000 分布在数据源 1。

BenchmarkSQL 中有如下 SQL 涉及多表：

  1. SELECT c_discount, c_last, c_credit, w_tax     
  2. FROM bmsql_customer     
  3.     JOIN bmsql_warehouse ON (w_id = c_w_id)     
  4. WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?
  1. SELECT o_id, o_entry_d, o_carrier_id     
  2. FROM bmsql_oorder     
  3. WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ?       
  4.   AND o_id = (          
  5.       SELECT max(o_id)               
  6.       FROM bmsql_oorder               
  7.       WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ?          
  8.       )

如果以 warehouse id 作为分片键，以上 SQL 涉及的表可以配置为 bindingTable：

  1. rules:
  2.   - !SHARDING
  3.     bindingTables:
  4.       - bmsql_warehouse, bmsql_customer
  5.       - bmsql_stock, bmsql_district, bmsql_order_line

以 warehouse id 为分片键的数据分片配置可以参考本文附录。

PostgreSQL JDBC URL 参数建议

对 BenchmarkSQL 所使用的配置文件中的 JDBC URL 进行调整，即参数名 conn 的值：

  • 增加参数 defaultRowFetchSize=50 可能减少多行结果集的 fetch 次数，需要根据实际测试结果适当增大或减小。
  • 增加参数 reWriteBatchedInserts=true 可能减少批量插入的耗时，例如准备数据或 New Order 业务的批量插入，需要根据实际测试结果决定是否启用。

props.pg 文件节选，建议修改的位置为第 3 行 conn 的参数值：

  1. db=postgres
  2. driver=org.postgresql.Driver
  3. conn=jdbc:postgresql://localhost:5432/postgres?defaultRowFetchSize=50&reWriteBatchedInserts=true
  4. user=benchmarksql
  5. password=PWbmsql

ShardingSphere Proxy server.yaml 参数建议

proxy-backend-query-fetch-size 参数值默认值为 -1，修改为 50 左右可以尽量减少多行结果集的 fetch 次数。 proxy-frontend-executor-size 参数默认值为 CPU * 2，可以根据实际测试结果减少至 CPU * 0.5 左右；如果涉及 NUMA，可以根据实际测试结果设置为单个 CPU 的物理核数。

server.yaml 文件节选：

  1. props:
  2.   proxy-backend-query-fetch-size: 50
  3.   # proxy-frontend-executor-size: 32 # 4 路 32C aarch64
  4.   # proxy-frontend-executor-size: 12 # 2 路 12C24T x86

附录

BenchmarkSQL 数据分片参考配置

Pool size 请根据实际压测情况适当调整。

  1. databaseName: bmsql_sharding
  2. dataSources:
  3.   ds_0:
  4.     url: jdbc:postgresql://db0.ip:5432/bmsql
  5.     username: postgres
  6.     password: postgres
  7.     connectionTimeoutMilliseconds: 3000
  8.     idleTimeoutMilliseconds: 60000
  9.     maxLifetimeMilliseconds: 1800000
  10.     maxPoolSize: 1000
  11.     minPoolSize: 1000
  12.   ds_1:
  13.     url: jdbc:postgresql://db1.ip:5432/bmsql
  14.     username: postgres
  15.     password: postgres
  16.     connectionTimeoutMilliseconds: 3000
  17.     idleTimeoutMilliseconds: 60000
  18.     maxLifetimeMilliseconds: 1800000
  19.     maxPoolSize: 1000
  20.     minPoolSize: 1000
  21.   ds_2:
  22.     url: jdbc:postgresql://db2.ip:5432/bmsql
  23.     username: postgres
  24.     password: postgres
  25.     connectionTimeoutMilliseconds: 3000
  26.     idleTimeoutMilliseconds: 60000
  27.     maxLifetimeMilliseconds: 1800000
  28.     maxPoolSize: 1000
  29.     minPoolSize: 1000
  30.   ds_3:
  31.     url: jdbc:postgresql://db3.ip:5432/bmsql
  32.     username: postgres
  33.     password: postgres
  34.     connectionTimeoutMilliseconds: 3000
  35.     idleTimeoutMilliseconds: 60000
  36.     maxLifetimeMilliseconds: 1800000
  37.     maxPoolSize: 1000
  38.     minPoolSize: 1000
  40. rules:
  41.   - !SHARDING
  42.     bindingTables:
  43.       - bmsql_warehouse, bmsql_customer
  44.       - bmsql_stock, bmsql_district, bmsql_order_line
  45.     defaultDatabaseStrategy:
  46.       none:
  47.     defaultTableStrategy:
  48.       none:
  49.     keyGenerators:
  50.       snowflake:
  51.         type: SNOWFLAKE
  52.     tables:
  53.       bmsql_config:
  54.         actualDataNodes: ds_0.bmsql_config
  56.       bmsql_warehouse:
  57.         actualDataNodes: ds_${0..3}.bmsql_warehouse
  58.         databaseStrategy:
  59.           standard:
  60.             shardingColumn: w_id
  61.             shardingAlgorithmName: mod_4
  63.       bmsql_district:
  64.         actualDataNodes: ds_${0..3}.bmsql_district
  65.         databaseStrategy:
  66.           standard:
  67.             shardingColumn: d_w_id
  68.             shardingAlgorithmName: mod_4
  70.       bmsql_customer:
  71.         actualDataNodes: ds_${0..3}.bmsql_customer
  72.         databaseStrategy:
  73.           standard:
  74.             shardingColumn: c_w_id
  75.             shardingAlgorithmName: mod_4
  77.       bmsql_item:
  78.         actualDataNodes: ds_${0..3}.bmsql_item
  79.         databaseStrategy:
  80.           standard:
  81.             shardingColumn: i_id
  82.             shardingAlgorithmName: mod_4
  84.       bmsql_history:
  85.         actualDataNodes: ds_${0..3}.bmsql_history
  86.         databaseStrategy:
  87.           standard:
  88.             shardingColumn: h_w_id
  89.             shardingAlgorithmName: mod_4
  91.       bmsql_oorder:
  92.         actualDataNodes: ds_${0..3}.bmsql_oorder
  93.         databaseStrategy:
  94.           standard:
  95.             shardingColumn: o_w_id
  96.             shardingAlgorithmName: mod_4
  98.       bmsql_stock:
  99.         actualDataNodes: ds_${0..3}.bmsql_stock
  100.         databaseStrategy:
  101.           standard:
  102.             shardingColumn: s_w_id
  103.             shardingAlgorithmName: mod_4
  105.       bmsql_new_order:
  106.         actualDataNodes: ds_${0..3}.bmsql_new_order
  107.         databaseStrategy:
  108.           standard:
  109.             shardingColumn: no_w_id
  110.             shardingAlgorithmName: mod_4
  112.       bmsql_order_line:
  113.         actualDataNodes: ds_${0..3}.bmsql_order_line
  114.         databaseStrategy:
  115.           standard:
  116.             shardingColumn: ol_w_id
  117.             shardingAlgorithmName: mod_4
  119.     shardingAlgorithms:
  120.       mod_4:
  121.         type: MOD
  122.         props:
  123.           sharding-count: 4

BenchmarkSQL 5.0 PostgreSQL 语句列表

Create tables

  1. create table bmsql_config (
  2.   cfg_name    varchar(30) primary key,
  3.   cfg_value   varchar(50)
  4. );
  6. create table bmsql_warehouse (
  7.   w_id        integer   not null,
  8.   w_ytd       decimal(12,2),
  9.   w_tax       decimal(4,4),
  10.   w_name      varchar(10),
  11.   w_street_1  varchar(20),
  12.   w_street_2  varchar(20),
  13.   w_city      varchar(20),
  14.   w_state     char(2),
  15.   w_zip       char(9)
  16. );
  18. create table bmsql_district (
  19.   d_w_id       integer       not null,
  20.   d_id         integer       not null,
  21.   d_ytd        decimal(12,2),
  22.   d_tax        decimal(4,4),
  23.   d_next_o_id  integer,
  24.   d_name       varchar(10),
  25.   d_street_1   varchar(20),
  26.   d_street_2   varchar(20),
  27.   d_city       varchar(20),
  28.   d_state      char(2),
  29.   d_zip        char(9)
  30. );
  32. create table bmsql_customer (
  33.   c_w_id         integer        not null,
  34.   c_d_id         integer        not null,
  35.   c_id           integer        not null,
  36.   c_discount     decimal(4,4),
  37.   c_credit       char(2),
  38.   c_last         varchar(16),
  39.   c_first        varchar(16),
  40.   c_credit_lim   decimal(12,2),
  41.   c_balance      decimal(12,2),
  42.   c_ytd_payment  decimal(12,2),
  43.   c_payment_cnt  integer,
  44.   c_delivery_cnt integer,
  45.   c_street_1     varchar(20),
  46.   c_street_2     varchar(20),
  47.   c_city         varchar(20),
  48.   c_state        char(2),
  49.   c_zip          char(9),
  50.   c_phone        char(16),
  51.   c_since        timestamp,
  52.   c_middle       char(2),
  53.   c_data         varchar(500)
  54. );
  56. create sequence bmsql_hist_id_seq;
  58. create table bmsql_history (
  59.   hist_id  integer,
  60.   h_c_id   integer,
  61.   h_c_d_id integer,
  62.   h_c_w_id integer,
  63.   h_d_id   integer,
  64.   h_w_id   integer,
  65.   h_date   timestamp,
  66.   h_amount decimal(6,2),
  67.   h_data   varchar(24)
  68. );
  70. create table bmsql_new_order (
  71.   no_w_id  integer   not null,
  72.   no_d_id  integer   not null,
  73.   no_o_id  integer   not null
  74. );
  76. create table bmsql_oorder (
  77.   o_w_id       integer      not null,
  78.   o_d_id       integer      not null,
  79.   o_id         integer      not null,
  80.   o_c_id       integer,
  81.   o_carrier_id integer,
  82.   o_ol_cnt     integer,
  83.   o_all_local  integer,
  84.   o_entry_d    timestamp
  85. );
  87. create table bmsql_order_line (
  88.   ol_w_id         integer   not null,
  89.   ol_d_id         integer   not null,
  90.   ol_o_id         integer   not null,
  91.   ol_number       integer   not null,
  92.   ol_i_id         integer   not null,
  93.   ol_delivery_d   timestamp,
  94.   ol_amount       decimal(6,2),
  95.   ol_supply_w_id  integer,
  96.   ol_quantity     integer,
  97.   ol_dist_info    char(24)
  98. );
  100. create table bmsql_item (
  101.   i_id     integer      not null,
  102.   i_name   varchar(24),
  103.   i_price  decimal(5,2),
  104.   i_data   varchar(50),
  105.   i_im_id  integer
  106. );
  108. create table bmsql_stock (
  109.   s_w_id       integer       not null,
  110.   s_i_id       integer       not null,
  111.   s_quantity   integer,
  112.   s_ytd        integer,
  113.   s_order_cnt  integer,
  114.   s_remote_cnt integer,
  115.   s_data       varchar(50),
  116.   s_dist_01    char(24),
  117.   s_dist_02    char(24),
  118.   s_dist_03    char(24),
  119.   s_dist_04    char(24),
  120.   s_dist_05    char(24),
  121.   s_dist_06    char(24),
  122.   s_dist_07    char(24),
  123.   s_dist_08    char(24),
  124.   s_dist_09    char(24),
  125.   s_dist_10    char(24)
  126. );

Create indexes

  1. alter table bmsql_warehouse add constraint bmsql_warehouse_pkey
  2.   primary key (w_id);
  4. alter table bmsql_district add constraint bmsql_district_pkey
  5.   primary key (d_w_id, d_id);
  7. alter table bmsql_customer add constraint bmsql_customer_pkey
  8.   primary key (c_w_id, c_d_id, c_id);
  10. create index bmsql_customer_idx1
  11.   on  bmsql_customer (c_w_id, c_d_id, c_last, c_first);
  13. alter table bmsql_oorder add constraint bmsql_oorder_pkey
  14.   primary key (o_w_id, o_d_id, o_id);
  16. create unique index bmsql_oorder_idx1
  17.   on  bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);
  19. alter table bmsql_new_order add constraint bmsql_new_order_pkey
  20.   primary key (no_w_id, no_d_id, no_o_id);
  22. alter table bmsql_order_line add constraint bmsql_order_line_pkey
  23.   primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);
  25. alter table bmsql_stock add constraint bmsql_stock_pkey
  26.   primary key (s_w_id, s_i_id);
  28. alter table bmsql_item add constraint bmsql_item_pkey
  29.   primary key (i_id);

New Order 业务

stmtNewOrderSelectWhseCust

  1. UPDATE bmsql_district 
  2.     SET d_next_o_id = d_next_o_id + 1 
  3.     WHERE d_w_id = ? AND d_id = ?

stmtNewOrderSelectDist

  1. SELECT d_tax, d_next_o_id 
  2.     FROM bmsql_district 
  3.     WHERE d_w_id = ? AND d_id = ? 
  4.     FOR UPDATE

stmtNewOrderUpdateDist

  1. UPDATE bmsql_district 
  2.     SET d_next_o_id = d_next_o_id + 1 
  3.     WHERE d_w_id = ? AND d_id = ?

stmtNewOrderInsertOrder

  1. INSERT INTO bmsql_oorder (
  2.     o_id, o_d_id, o_w_id, o_c_id, o_entry_d, 
  3.     o_ol_cnt, o_all_local) 
  4. VALUES (?, ?, ?, ?, ?, ?, ?)

stmtNewOrderInsertNewOrder

  1. INSERT INTO bmsql_new_order (
  2.     no_o_id, no_d_id, no_w_id) 
  3. VALUES (?, ?, ?)

stmtNewOrderSelectStock

  1. SELECT s_quantity, s_data, 
  2.        s_dist_01, s_dist_02, s_dist_03, s_dist_04, 
  3.        s_dist_05, s_dist_06, s_dist_07, s_dist_08, 
  4.        s_dist_09, s_dist_10 
  5.     FROM bmsql_stock 
  6.     WHERE s_w_id = ? AND s_i_id = ? 
  7.     FOR UPDATE

stmtNewOrderSelectItem

  1. SELECT i_price, i_name, i_data 
  2.     FROM bmsql_item 
  3.     WHERE i_id = ?

stmtNewOrderUpdateStock

  1. UPDATE bmsql_stock 
  2.     SET s_quantity = ?, s_ytd = s_ytd + ?, 
  3.         s_order_cnt = s_order_cnt + 1, 
  4.         s_remote_cnt = s_remote_cnt + ? 
  5.     WHERE s_w_id = ? AND s_i_id = ?

stmtNewOrderInsertOrderLine

  1. INSERT INTO bmsql_order_line (
  2.     ol_o_id, ol_d_id, ol_w_id, ol_number, 
  3.     ol_i_id, ol_supply_w_id, ol_quantity, 
  4.     ol_amount, ol_dist_info) 
  5. VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)

Payment 业务

stmtPaymentSelectWarehouse

  1. SELECT w_name, w_street_1, w_street_2, w_city, 
  2.        w_state, w_zip 
  3.     FROM bmsql_warehouse 
  4.     WHERE w_id = ?

stmtPaymentSelectDistrict

  1. SELECT d_name, d_street_1, d_street_2, d_city, 
  2.        d_state, d_zip 
  3.     FROM bmsql_district 
  4.     WHERE d_w_id = ? AND d_id = ?

stmtPaymentSelectCustomerListByLast

  1. SELECT c_id 
  2.     FROM bmsql_customer 
  3.     WHERE c_w_id = ? AND c_d_id = ? AND c_last = ? 
  4.     ORDER BY c_first

stmtPaymentSelectCustomer

  1. SELECT c_first, c_middle, c_last, c_street_1, c_street_2, 
  2.        c_city, c_state, c_zip, c_phone, c_since, c_credit, 
  3.        c_credit_lim, c_discount, c_balance 
  4.     FROM bmsql_customer 
  5.     WHERE c_w_id = ? AND c_d_id = ? AND c_id = ? 
  6.     FOR UPDATE

stmtPaymentSelectCustomerData

  1. SELECT c_data 
  2.     FROM bmsql_customer 
  3.     WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?

stmtPaymentUpdateWarehouse

  1. UPDATE bmsql_warehouse 
  2.     SET w_ytd = w_ytd + ? 
  3.     WHERE w_id = ?

stmtPaymentUpdateDistrict

  1. UPDATE bmsql_district 
  2.     SET d_ytd = d_ytd + ? 
  3.     WHERE d_w_id = ? AND d_id = ?

stmtPaymentUpdateCustomer

  1. UPDATE bmsql_customer 
  2.     SET c_balance = c_balance - ?, 
  3.         c_ytd_payment = c_ytd_payment + ?, 
  4.         c_payment_cnt = c_payment_cnt + 1 
  5.     WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?

stmtPaymentUpdateCustomerWithData

  1. UPDATE bmsql_customer 
  2.     SET c_balance = c_balance - ?, 
  3.         c_ytd_payment = c_ytd_payment + ?, 
  4.         c_payment_cnt = c_payment_cnt + 1, 
  5.         c_data = ? 
  6.     WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?

stmtPaymentInsertHistory

  1. INSERT INTO bmsql_history (
  2.     h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, 
  3.     h_date, h_amount, h_data) 
  4. VALUES (?, ?, ?, ?, ?, ?, ?, ?)

Order Status 业务

stmtOrderStatusSelectCustomerListByLast

  1. SELECT c_id 
  2.     FROM bmsql_customer 
  3.     WHERE c_w_id = ? AND c_d_id = ? AND c_last = ? 
  4.     ORDER BY c_first

stmtOrderStatusSelectCustomer

  1. SELECT c_first, c_middle, c_last, c_balance 
  2.     FROM bmsql_customer 
  3.     WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?

stmtOrderStatusSelectLastOrder

  1. SELECT o_id, o_entry_d, o_carrier_id 
  2.     FROM bmsql_oorder 
  3.     WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ? 
  4.       AND o_id = (
  5.           SELECT max(o_id) 
  6.               FROM bmsql_oorder 
  7.               WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ?
  8.           )

stmtOrderStatusSelectOrderLine

  1. SELECT ol_i_id, ol_supply_w_id, ol_quantity, 
  2.        ol_amount, ol_delivery_d 
  3.     FROM bmsql_order_line 
  4.     WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id = ? 
  5.     ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number

Stock level 业务

stmtStockLevelSelectLow

  1. SELECT count(*) AS low_stock FROM (
  2.     SELECT s_w_id, s_i_id, s_quantity 
  3.         FROM bmsql_stock 
  4.         WHERE s_w_id = ? AND s_quantity < ? AND s_i_id IN (
  5.             SELECT ol_i_id 
  6.                 FROM bmsql_district 
  7.                 JOIN bmsql_order_line ON ol_w_id = d_w_id 
  8.                  AND ol_d_id = d_id 
  9.                  AND ol_o_id >= d_next_o_id - 20 
  10.                  AND ol_o_id < d_next_o_id 
  11.                 WHERE d_w_id = ? AND d_id = ? 
  12.         ) 
  13.     ) AS L

Delivery BG 业务

stmtDeliveryBGSelectOldestNewOrder

  1. SELECT no_o_id 
  2.     FROM bmsql_new_order 
  3.     WHERE no_w_id = ? AND no_d_id = ? 
  4.     ORDER BY no_o_id ASC

stmtDeliveryBGDeleteOldestNewOrder

  1. DELETE FROM bmsql_new_order 
  2.     WHERE no_w_id = ? AND no_d_id = ? AND no_o_id = ?

stmtDeliveryBGSelectOrder

  1. SELECT o_c_id 
  2.     FROM bmsql_oorder 
  3.     WHERE o_w_id = ? AND o_d_id = ? AND o_id = ?

stmtDeliveryBGUpdateOrder

  1. UPDATE bmsql_oorder 
  2.     SET o_carrier_id = ? 
  3.     WHERE o_w_id = ? AND o_d_id = ? AND o_id = ?

stmtDeliveryBGSelectSumOLAmount

  1. SELECT sum(ol_amount) AS sum_ol_amount 
  2.     FROM bmsql_order_line 
  3.     WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id = ?

stmtDeliveryBGUpdateOrderLine

  1. UPDATE bmsql_order_line
  2. SET ol_delivery_d = ?
  3. WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id = ?

stmtDeliveryBGUpdateCustomer

  1. UPDATE bmsql_customer 
  2.     SET c_balance = c_balance + ?, 
  3.         c_delivery_cnt = c_delivery_cnt + 1 
  4.     WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?