BenchmarkSQL ShardingSphere Proxy 分片性能测试

    测试方法

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

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

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

    修改前：

    修改后：

    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

    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.         databaseStrategy:
    100.           standard:
    101.             shardingColumn: s_w_id
    102.             shardingAlgorithmName: mod_4
    104.       bmsql_new_order:
    105.         actualDataNodes: ds_${0..3}.bmsql_new_order
    107.           standard:
    108.             shardingColumn: no_w_id
    109.             shardingAlgorithmName: mod_4
    111.       bmsql_order_line:
    112.         actualDataNodes: ds_${0..3}.bmsql_order_line
    113.         databaseStrategy:
    114.           standard:
    115.             shardingColumn: ol_w_id
    116.             shardingAlgorithmName: mod_4
    118.     shardingAlgorithms:
    119.       mod_4:
    120.         type: MOD
    121.         props:
    122.           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)

    Create indexes

    1. alter table bmsql_warehouse add constraint bmsql_warehouse_pkey
    2.   primary key (w_id);
    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);

    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

    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
    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

    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

    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 = ?

    stmtDeliveryBGUpdateCustomer

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