BenchmarkSQL ShardingSphere-Proxy Sharding Performance Test

    Method

    ShardingSphere-Proxy supports the TPC-C test through BenchmarkSQL 5.0. In addition to the content described in this document, BenchmarkSQL is operated according to the original document .

    Unlike stand-alone database stress testing, distributed database solutions inevitably face trade-offs in functions. It is recommended to make the following adjustments when using BenchmarkSQL to carry out stress testing on ShardingSphere-Proxy.

    Modify run/runDatabaseBuild.sh in the BenchmarkSQL directory at line 17.

    Before modification:

    After modification:

    1. AFTER_LOAD="indexCreates buildFinish"

    Stress testing environment or parameter recommendations

    Note: None of the parameters mentioned in this section are absolute values and need to be adjusted based on actual test results.

    ShardingSphere can be compiled using Java 8.

    When using Java 17, maximize the ShardingSphere performance by default.

    ShardingSphere data sharding recommendations

    The data sharding of BenchmarkSQL can use the warehouse id in each table as the sharding key.

    One of the tables bmsql_item has no warehouse id and has a fixed data volume of 100,000 rows:

    • You can take i_id as a sharding key. However, the same Proxy connection may hold connections to multiple different data sources at the same time.
    • Or you can give up sharding and store it in a single data source. But a data source may be under great pressure.
    • Or you may choose range-based sharding for i_id, such as 1-50000 for data source 0 and 50001-100000 for data source 1.

    BenchmarkSQL has the following SQL involving multiple tables:

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

    If the warehouse id is used as the sharding key, the tables involved in the above SQL can be configured as bindingTable:

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

    For the data sharding configuration with warehouse id as the sharding key, refer to the appendix of this document.

    PostgreSQL JDBC URL parameter recommendations

    Adjust the JDBC URL in the configuration file used by BenchmarkSQL, that is, the value of the parameter name conn:

    • Adding the parameter defaultRowFetchSize=50 may reduce the number of fetch for multi-row result sets. You need to increase or decrease the number according to actual test results.
    • Adding the parameter reWriteBatchedInserts=true may reduce the time spent on bulk inserts, such as preparing data or bulk inserts for the New Order business. Whether to enable the operation depends on actual test results.

    props.pg file excerpt. It is suggested to change the parameter value of conn in line 3.

    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

    The default value of proxy-backend-query-fetch-size is -1. Changing it to about 50 can minimize the number of fetch for multi-row result sets.

    server.yaml file excerpt:

    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 data sharding reference configuration

    Adjust pool size according to the actual stress testing process.

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

    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 business

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

    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 business

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

    stmtDeliveryBGUpdateOrderLine

    stmtDeliveryBGUpdateCustomer

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