Retro Eye care Haitian Deep Dark Default

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:

AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"

After modification:

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:

SELECT c_discount, c_last, c_credit, w_tax     
FROM bmsql_customer     
    JOIN bmsql_warehouse ON (w_id = c_w_id)     
WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?
SELECT o_id, o_entry_d, o_carrier_id     
FROM bmsql_oorder     
WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ?       
  AND o_id = (          
      SELECT max(o_id)               
      FROM bmsql_oorder               
      WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ?          
      )

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

rules:
  - !SHARDING
    bindingTables:
      - bmsql_warehouse, bmsql_customer
      - 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.

db=postgres
driver=org.postgresql.Driver
conn=jdbc:postgresql://localhost:5432/postgres?defaultRowFetchSize=50&reWriteBatchedInserts=true
user=benchmarksql
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:

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

Appendix

BenchmarkSQL data sharding reference configuration

Adjust pool size according to the actual stress testing process.

databaseName: bmsql_sharding
dataSources:
  ds_0:
    url: jdbc:postgresql://db0.ip:5432/bmsql
    username: postgres
    password: postgres
    connectionTimeoutMilliseconds: 3000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 1000
    minPoolSize: 1000
  ds_1:
    url: jdbc:postgresql://db1.ip:5432/bmsql
    username: postgres
    password: postgres
    connectionTimeoutMilliseconds: 3000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 1000
    minPoolSize: 1000
  ds_2:
    url: jdbc:postgresql://db2.ip:5432/bmsql
    username: postgres
    password: postgres
    connectionTimeoutMilliseconds: 3000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 1000
    minPoolSize: 1000
  ds_3:
    url: jdbc:postgresql://db3.ip:5432/bmsql
    username: postgres
    password: postgres
    connectionTimeoutMilliseconds: 3000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 1000
    minPoolSize: 1000

rules:
  - !SHARDING
    bindingTables:
      - bmsql_warehouse, bmsql_customer
      - bmsql_stock, bmsql_district, bmsql_order_line
    defaultDatabaseStrategy:
      none:
    defaultTableStrategy:
      none:
    keyGenerators:
      snowflake:
        type: SNOWFLAKE
    tables:
      bmsql_config:
        actualDataNodes: ds_0.bmsql_config

      bmsql_warehouse:
        actualDataNodes: ds_${0..3}.bmsql_warehouse
        databaseStrategy:
          standard:
            shardingColumn: w_id
            shardingAlgorithmName: mod_4

      bmsql_district:
        actualDataNodes: ds_${0..3}.bmsql_district
        databaseStrategy:
          standard:
            shardingColumn: d_w_id
            shardingAlgorithmName: mod_4

      bmsql_customer:
        actualDataNodes: ds_${0..3}.bmsql_customer
        databaseStrategy:
          standard:
            shardingColumn: c_w_id
            shardingAlgorithmName: mod_4

      bmsql_item:
        actualDataNodes: ds_${0..3}.bmsql_item
        databaseStrategy:
          standard:
            shardingColumn: i_id
            shardingAlgorithmName: mod_4

      bmsql_history:
        actualDataNodes: ds_${0..3}.bmsql_history
        databaseStrategy:
          standard:
            shardingColumn: h_w_id
            shardingAlgorithmName: mod_4

      bmsql_oorder:
        actualDataNodes: ds_${0..3}.bmsql_oorder
        databaseStrategy:
          standard:
            shardingColumn: o_w_id
            shardingAlgorithmName: mod_4

      bmsql_stock:
        actualDataNodes: ds_${0..3}.bmsql_stock
        databaseStrategy:
          standard:
            shardingColumn: s_w_id
            shardingAlgorithmName: mod_4

      bmsql_new_order:
        actualDataNodes: ds_${0..3}.bmsql_new_order
        databaseStrategy:
          standard:
            shardingColumn: no_w_id
            shardingAlgorithmName: mod_4

      bmsql_order_line:
        actualDataNodes: ds_${0..3}.bmsql_order_line
        databaseStrategy:
          standard:
            shardingColumn: ol_w_id
            shardingAlgorithmName: mod_4

    shardingAlgorithms:
      mod_4:
        type: MOD
        props:
          sharding-count: 4

BenchmarkSQL 5.0 PostgreSQL statement list

Create tables

create table bmsql_config (
  cfg_name    varchar(30) primary key,
  cfg_value   varchar(50)
);

create table bmsql_warehouse (
  w_id        integer   not null,
  w_ytd       decimal(12,2),
  w_tax       decimal(4,4),
  w_name      varchar(10),
  w_street_1  varchar(20),
  w_street_2  varchar(20),
  w_city      varchar(20),
  w_state     char(2),
  w_zip       char(9)
);

create table bmsql_district (
  d_w_id       integer       not null,
  d_id         integer       not null,
  d_ytd        decimal(12,2),
  d_tax        decimal(4,4),
  d_next_o_id  integer,
  d_name       varchar(10),
  d_street_1   varchar(20),
  d_street_2   varchar(20),
  d_city       varchar(20),
  d_state      char(2),
  d_zip        char(9)
);

create table bmsql_customer (
  c_w_id         integer        not null,
  c_d_id         integer        not null,
  c_id           integer        not null,
  c_discount     decimal(4,4),
  c_credit       char(2),
  c_last         varchar(16),
  c_first        varchar(16),
  c_credit_lim   decimal(12,2),
  c_balance      decimal(12,2),
  c_ytd_payment  decimal(12,2),
  c_payment_cnt  integer,
  c_delivery_cnt integer,
  c_street_1     varchar(20),
  c_street_2     varchar(20),
  c_city         varchar(20),
  c_state        char(2),
  c_zip          char(9),
  c_phone        char(16),
  c_since        timestamp,
  c_middle       char(2),
  c_data         varchar(500)
);

create sequence bmsql_hist_id_seq;

create table bmsql_history (
  hist_id  integer,
  h_c_id   integer,
  h_c_d_id integer,
  h_c_w_id integer,
  h_d_id   integer,
  h_w_id   integer,
  h_date   timestamp,
  h_amount decimal(6,2),
  h_data   varchar(24)
);

create table bmsql_new_order (
  no_w_id  integer   not null,
  no_d_id  integer   not null,
  no_o_id  integer   not null
);

create table bmsql_oorder (
  o_w_id       integer      not null,
  o_d_id       integer      not null,
  o_id         integer      not null,
  o_c_id       integer,
  o_carrier_id integer,
  o_ol_cnt     integer,
  o_all_local  integer,
  o_entry_d    timestamp
);

create table bmsql_order_line (
  ol_w_id         integer   not null,
  ol_d_id         integer   not null,
  ol_o_id         integer   not null,
  ol_number       integer   not null,
  ol_i_id         integer   not null,
  ol_delivery_d   timestamp,
  ol_amount       decimal(6,2),
  ol_supply_w_id  integer,
  ol_quantity     integer,
  ol_dist_info    char(24)
);

create table bmsql_item (
  i_id     integer      not null,
  i_name   varchar(24),
  i_price  decimal(5,2),
  i_data   varchar(50),
  i_im_id  integer
);

create table bmsql_stock (
  s_w_id       integer       not null,
  s_i_id       integer       not null,
  s_quantity   integer,
  s_ytd        integer,
  s_order_cnt  integer,
  s_remote_cnt integer,
  s_data       varchar(50),
  s_dist_01    char(24),
  s_dist_02    char(24),
  s_dist_03    char(24),
  s_dist_04    char(24),
  s_dist_05    char(24),
  s_dist_06    char(24),
  s_dist_07    char(24),
  s_dist_08    char(24),
  s_dist_09    char(24),
  s_dist_10    char(24)
);

Create indexes

alter table bmsql_warehouse add constraint bmsql_warehouse_pkey
  primary key (w_id);

alter table bmsql_district add constraint bmsql_district_pkey
  primary key (d_w_id, d_id);

alter table bmsql_customer add constraint bmsql_customer_pkey
  primary key (c_w_id, c_d_id, c_id);

create index bmsql_customer_idx1
  on  bmsql_customer (c_w_id, c_d_id, c_last, c_first);

alter table bmsql_oorder add constraint bmsql_oorder_pkey
  primary key (o_w_id, o_d_id, o_id);

create unique index bmsql_oorder_idx1
  on  bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);

alter table bmsql_new_order add constraint bmsql_new_order_pkey
  primary key (no_w_id, no_d_id, no_o_id);

alter table bmsql_order_line add constraint bmsql_order_line_pkey
  primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);

alter table bmsql_stock add constraint bmsql_stock_pkey
  primary key (s_w_id, s_i_id);

alter table bmsql_item add constraint bmsql_item_pkey
  primary key (i_id);

New Order business

stmtNewOrderSelectWhseCust

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

stmtNewOrderSelectDist

SELECT d_tax, d_next_o_id 
    FROM bmsql_district 
    WHERE d_w_id = ? AND d_id = ? 
    FOR UPDATE

stmtNewOrderUpdateDist

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

stmtNewOrderInsertOrder

INSERT INTO bmsql_oorder (
    o_id, o_d_id, o_w_id, o_c_id, o_entry_d, 
    o_ol_cnt, o_all_local) 
VALUES (?, ?, ?, ?, ?, ?, ?)

stmtNewOrderInsertNewOrder

INSERT INTO bmsql_new_order (
    no_o_id, no_d_id, no_w_id) 
VALUES (?, ?, ?)

stmtNewOrderSelectStock

SELECT s_quantity, s_data, 
       s_dist_01, s_dist_02, s_dist_03, s_dist_04, 
       s_dist_05, s_dist_06, s_dist_07, s_dist_08, 
       s_dist_09, s_dist_10 
    FROM bmsql_stock 
    WHERE s_w_id = ? AND s_i_id = ? 
    FOR UPDATE

stmtNewOrderSelectItem

SELECT i_price, i_name, i_data 
    FROM bmsql_item 
    WHERE i_id = ?

stmtNewOrderUpdateStock

UPDATE bmsql_stock 
    SET s_quantity = ?, s_ytd = s_ytd + ?, 
        s_order_cnt = s_order_cnt + 1, 
        s_remote_cnt = s_remote_cnt + ? 
    WHERE s_w_id = ? AND s_i_id = ?

stmtNewOrderInsertOrderLine

INSERT INTO bmsql_order_line (
    ol_o_id, ol_d_id, ol_w_id, ol_number, 
    ol_i_id, ol_supply_w_id, ol_quantity, 
    ol_amount, ol_dist_info) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)

Payment business

stmtPaymentSelectWarehouse

SELECT w_name, w_street_1, w_street_2, w_city, 
       w_state, w_zip 
    FROM bmsql_warehouse 
    WHERE w_id = ? 

stmtPaymentSelectDistrict

SELECT d_name, d_street_1, d_street_2, d_city, 
       d_state, d_zip 
    FROM bmsql_district 
    WHERE d_w_id = ? AND d_id = ?

stmtPaymentSelectCustomerListByLast

SELECT c_id 
    FROM bmsql_customer 
    WHERE c_w_id = ? AND c_d_id = ? AND c_last = ? 
    ORDER BY c_first

stmtPaymentSelectCustomer

SELECT c_first, c_middle, c_last, c_street_1, c_street_2, 
       c_city, c_state, c_zip, c_phone, c_since, c_credit, 
       c_credit_lim, c_discount, c_balance 
    FROM bmsql_customer 
    WHERE c_w_id = ? AND c_d_id = ? AND c_id = ? 
    FOR UPDATE

stmtPaymentSelectCustomerData

SELECT c_data 
    FROM bmsql_customer 
    WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?

stmtPaymentUpdateWarehouse

UPDATE bmsql_warehouse 
    SET w_ytd = w_ytd + ? 
    WHERE w_id = ?

stmtPaymentUpdateDistrict

UPDATE bmsql_district 
    SET d_ytd = d_ytd + ? 
    WHERE d_w_id = ? AND d_id = ?

stmtPaymentUpdateCustomer

UPDATE bmsql_customer 
    SET c_balance = c_balance - ?, 
        c_ytd_payment = c_ytd_payment + ?, 
        c_payment_cnt = c_payment_cnt + 1 
    WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?

stmtPaymentUpdateCustomerWithData

UPDATE bmsql_customer 
    SET c_balance = c_balance - ?, 
        c_ytd_payment = c_ytd_payment + ?, 
        c_payment_cnt = c_payment_cnt + 1, 
        c_data = ? 
    WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?

stmtPaymentInsertHistory

INSERT INTO bmsql_history (
    h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, 
    h_date, h_amount, h_data) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?)

Order Status business

stmtOrderStatusSelectCustomerListByLast

SELECT c_id 
    FROM bmsql_customer 
    WHERE c_w_id = ? AND c_d_id = ? AND c_last = ? 
    ORDER BY c_first

stmtOrderStatusSelectCustomer

SELECT c_first, c_middle, c_last, c_balance 
    FROM bmsql_customer 
    WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?

stmtOrderStatusSelectLastOrder

SELECT o_id, o_entry_d, o_carrier_id 
    FROM bmsql_oorder 
    WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ? 
      AND o_id = (
          SELECT max(o_id) 
              FROM bmsql_oorder 
              WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ?
          )

stmtOrderStatusSelectOrderLine

SELECT ol_i_id, ol_supply_w_id, ol_quantity, 
       ol_amount, ol_delivery_d 
    FROM bmsql_order_line 
    WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id = ? 
    ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number

Stock level business

stmtStockLevelSelectLow

SELECT count(*) AS low_stock FROM (
    SELECT s_w_id, s_i_id, s_quantity 
        FROM bmsql_stock 
        WHERE s_w_id = ? AND s_quantity < ? AND s_i_id IN (
            SELECT ol_i_id 
                FROM bmsql_district 
                JOIN bmsql_order_line ON ol_w_id = d_w_id 
                 AND ol_d_id = d_id 
                 AND ol_o_id >= d_next_o_id - 20 
                 AND ol_o_id < d_next_o_id 
                WHERE d_w_id = ? AND d_id = ? 
        ) 
    ) AS L

Delivery BG business

stmtDeliveryBGSelectOldestNewOrder

SELECT no_o_id 
    FROM bmsql_new_order 
    WHERE no_w_id = ? AND no_d_id = ? 
    ORDER BY no_o_id ASC

stmtDeliveryBGDeleteOldestNewOrder

DELETE FROM bmsql_new_order 
    WHERE no_w_id = ? AND no_d_id = ? AND no_o_id = ?

stmtDeliveryBGSelectOrder

SELECT o_c_id 
    FROM bmsql_oorder 
    WHERE o_w_id = ? AND o_d_id = ? AND o_id = ?

stmtDeliveryBGUpdateOrder

UPDATE bmsql_oorder 
    SET o_carrier_id = ? 
    WHERE o_w_id = ? AND o_d_id = ? AND o_id = ?

stmtDeliveryBGSelectSumOLAmount

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

stmtDeliveryBGUpdateOrderLine

UPDATE bmsql_order_line
SET ol_delivery_d = ?
WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id = ?

stmtDeliveryBGUpdateCustomer

UPDATE bmsql_customer 
    SET c_balance = c_balance + ?, 
        c_delivery_cnt = c_delivery_cnt + 1 
    WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?