BenchmarkSQL ShardingSphere-Proxy Sharding Performance Test

Objective

BenchmarkSQL tool is used to test the sharding performance of ShardingSphere-Proxy.

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 HOW-TO-RUN.txt.

Fine tuning to test tools

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.

Remove the foreign key and extraHistID

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

Before modification:

  1. AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"

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

ShardingSphere-Proxy global.yaml parameter recommendations

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.

The default value of proxy-frontend-executor-size is CPU * 2 and can be reduced to about CPU * 0.5 based on actual test results. If NUMA is involved, set this parameter to the number of physical cores per CPU based on actual test results.

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

Appendix

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

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

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

Delivery BG business

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