ShardingSphere Proxy 支持通过 BenchmarkSQL 5.0 进行 TPC-C 测试。
除本文说明的内容外，BenchmarkSQL 操作步骤按照原文档 HOW-TO-RUN.txt 即可。
与单机数据库压测不同，分布式数据库解决方案难免在功能支持上有所取舍。使用 BenchmarkSQL 压测 ShardingSphere Proxy 建议进行如下调整。
修改 BenchmarkSQL 目录下 run/runDatabaseBuild.sh，文件第 17 行。
修改前：
AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"
修改后：
AFTER_LOAD="indexCreates buildFinish"
对 BenchmarkSQL 的数据分片，可以考虑以 warehouse id 作为分片键。其中一个表 bmsql_item 没有 warehouse id，可以取 i_id 作为分片键。
BenchmarkSQL 中有如下 SQL 涉及多表：
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 = ?
)
如果以 warehouse id 作为分片键，以上 SQL 涉及的表可以配置为 bindingTable：
rules:
- !SHARDING
bindingTables:
- bmsql_warehouse, bmsql_customer
- bmsql_stock, bmsql_district, bmsql_order_line
以 warehouse id 为分片键的数据分片配置可以参考本文附录。
对 BenchmarkSQL 所使用的配置文件中的 JDBC URL 进行调整，即参数名 conn 的值。
增加参数 defaultRowFetchSize=1 可能减少 Delivery 业务耗时。
props.pg 文件节选，建议修改的位置为第 3 行 conn 的参数值：
db=postgres
driver=org.postgresql.Driver
conn=jdbc:postgresql://localhost:5432/postgres?defaultRowFetchSize=1
user=benchmarksql
password=PWbmsql
warehouses=1
loadWorkers=4
terminals=1
proxy-backend-query-fetch-size 参数值默认值为 -1，修改为 1000 可能减少 Delivery 业务耗时。
server.yaml 文件节选：
props:
proxy-backend-query-fetch-size: 1000
其他参数如 max-connections-size-per-query 等可以在压测过程中适当增大，比如取 Actual tables 最大的数量。
假如有个表分 4 库 x 4 表，共 16 个表，参数值可以尝试取 16。
实际效果与取决于数据分片方式，如果分片配置能够让所有 SQL 都路由到单点，该参数可能对性能没有影响。
Pool size 请根据实际压测情况适当调整。
schemaName: 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: bmsql_warehouse_database_inline
bmsql_district:
actualDataNodes: ds_${0..3}.bmsql_district
databaseStrategy:
standard:
shardingColumn: d_w_id
shardingAlgorithmName: bmsql_district_database_inline
bmsql_customer:
actualDataNodes: ds_${0..3}.bmsql_customer
databaseStrategy:
standard:
shardingColumn: c_w_id
shardingAlgorithmName: bmsql_customer_database_inline
bmsql_item:
actualDataNodes: ds_${0..3}.bmsql_item
databaseStrategy:
standard:
shardingColumn: i_id
shardingAlgorithmName: bmsql_item_database_inline
bmsql_history:
actualDataNodes: ds_${0..3}.bmsql_history
databaseStrategy:
standard:
shardingColumn: h_w_id
shardingAlgorithmName: bmsql_history_database_inline
bmsql_oorder:
actualDataNodes: ds_${0..3}.bmsql_oorder_${0..3}
databaseStrategy:
standard:
shardingColumn: o_w_id
shardingAlgorithmName: bmsql_oorder_database_inline
tableStrategy:
standard:
shardingColumn: o_c_id
shardingAlgorithmName: bmsql_oorder_table_inline
bmsql_stock:
actualDataNodes: ds_${0..3}.bmsql_stock
databaseStrategy:
standard:
shardingColumn: s_w_id
shardingAlgorithmName: bmsql_stock_database_inline
bmsql_new_order:
actualDataNodes: ds_${0..3}.bmsql_new_order
databaseStrategy:
standard:
shardingColumn: no_w_id
shardingAlgorithmName: bmsql_new_order_database_inline
bmsql_order_line:
actualDataNodes: ds_${0..3}.bmsql_order_line
databaseStrategy:
standard:
shardingColumn: ol_w_id
shardingAlgorithmName: bmsql_order_line_database_inline
shardingAlgorithms:
bmsql_warehouse_database_inline:
type: INLINE
props:
algorithm-expression: ds_${w_id & 3}
bmsql_district_database_inline:
type: INLINE
props:
algorithm-expression: ds_${d_w_id & 3}
bmsql_customer_database_inline:
type: INLINE
props:
algorithm-expression: ds_${c_w_id & 3}
bmsql_item_database_inline:
type: INLINE
props:
algorithm-expression: ds_${i_id & 3}
bmsql_history_database_inline:
type: INLINE
props:
algorithm-expression: ds_${h_w_id & 3}
bmsql_oorder_database_inline:
type: INLINE
props:
algorithm-expression: ds_${o_w_id & 3}
bmsql_oorder_table_inline:
type: INLINE
props:
algorithm-expression: bmsql_oorder_${o_c_id & 3}
bmsql_stock_database_inline:
type: INLINE
props:
algorithm-expression: ds_${s_w_id & 3}
bmsql_new_order_database_inline:
type: INLINE
props:
algorithm-expression: ds_${no_w_id & 3}
bmsql_order_line_database_inline:
type: INLINE
props:
algorithm-expression: ds_${ol_w_id & 3}
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 = ?