BenchmarkSQL tool is used to test the sharding performance of ShardingSphere-Proxy.
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.
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_LOAD="indexCreates foreignKeys extraHistID buildFinish"
After modification:
AFTER_LOAD="indexCreates buildFinish"
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.
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:
i_id as a sharding key. However, the same Proxy connection may hold connections to multiple different data sources at the same time.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.
Adjust the JDBC URL in the configuration file used by BenchmarkSQL, that is, the value of the parameter name conn:
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.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
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.
server.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
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
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)
);
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);
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 (?, ?, ?, ?, ?, ?, ?, ?, ?)
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 (?, ?, ?, ?, ?, ?, ?, ?)
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
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
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 = ?