PostgreSQL BenchMarksql 性能测试
1 背景知识
本文主要介绍使用 BenchMarksql 6.0 对 PostgreSQL 数据库进行性能测试。
2 BenchMarksql 6.0 配置
su - postgres
#root>
cd /soft/benchmarksql/target/run
cp sample.postgresql.properties my.properties
vi my.properties
修改以下参数，这只是测试环境所以设置 warehouse=2 和 runMins=1。
# General Driver and connection parameters
#
# db={ postgres | oracle | firebird | mariadb | transact-sql }
# driver=<JDBC_DRIVER_CLASS>
# application={ Generic | PostgreSQLStoredProc | OracleStoredProc }
# conn=<JDBC_URI>
# user=<USERNAME>
# password=<PASSWORD>
db=postgres
driver=org.postgresql.Driver
application=Generic
conn=jdbc:postgresql://localhost:5432/testdb
user=benchmarksql
password=benchmarksql
# Scaling and timing configuration
warehouses=10
useWarehouseFrom=-1
useWarehouseTo=-1
loadWorkers=8
monkeys=2
sutThreads=64
maxDeliveryBGThreads=32
maxDeliveryBGPerWarehouse=1
rampupMins=0
rampupSUTMins=5
rampupTerminalMins=5
runMins=5
reportIntervalSecs=10
restartSUTThreadProbability=0.001
keyingTimeMultiplier=0.1
thinkTimeMultiplier=0.1
terminalMultiplier=1
traceTerminalIO=false
# Below are the definitions for the "attempted" transaction mix.
# The TPC-C specification requires minimum percentages for all but
# the NEW_ORDER transaction. If a test run happens to have any of
# those four types fall below those minimums, the entire test is
# invalid. We don't want that to happen, so we specify values just
# a tiny bit above the required minimum.
# The newOrderWeight is calculated as 100.0 - sum(all_other_types).
paymentWeight=43.2
orderStatusWeight=4.2
deliveryWeight=4.2
stockLevelWeight=4.2
# The TPC-C require a minimum of 1% of the NEW_ORDER transactions
# to roll back due to a user entry error (non existing item
# number. Doing it with a strict 1/100th probability can lead to
# undershooting this target, so we default to 1.01% to be sure.
rollbackPercent=1.01
# Directory name to create for collecting detailed result data.
# Comment this out to suppress. Note that the Flask UI will define
# this by itself, so don't specify it if you run through the UI.
resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
# BenchmarkSQL includes three OS metric collector scripts implemented
# in Python3. Two require to have collectd installed on the server
# systems, you want to include in the performance report. The data
# will be saved in resultDirectory/data/os-metric.json. The third
# is based on Prometheus and node_exporter.
# mcCollectdMqtt.py is a metric collector that expects the collectd
# instances on the servers to send the metric data to an MQTT broker.
#osCollectorScript=./mcCollectdMqtt.py \
# -h mymqttbroker.localdomain \
# -t collectd/mydbserver.localdomain/# \
# -t collectd/mybackrest.localdomain/#
# mcCollectdGraphite.py is a metric collector that expects the
# collectd instances on the servers to send the metric data to
# a graphite/whisper database and be available through the /render
# API.
#osCollectorScript=./mcCollectdGraphite.py \
# -u http://mygraphite.localdomain/render/ \
# -t collectd.mydbserver_localdomain.*.* \
# -t collectd.mydbserver_localdomain.*.*.* \
# -t collectd.mybackrest_localdomain.*.* \
# -t collectd.mybackrest_localdomain.*.*.*
# mcPrometheus.py retrieves the metric data from a Prometheus
# server through the API. It converts the output into the same
# format as the former two produce. The instances listed are
# the same names given in the "instance" label of the metric
# data scraped by Prometheus. The port number will be removed
# in the os-metric.json output.
#osCollectorScript=./mcPrometheus.py \
# -u http://myprometheus.localdomain:9090/api/v1/query_range \
# -i mydbserver.localdomain:9100 \
# -i mybackrest.localdomain:9100
# The report script is what generates the detailed HTML report for
# the benchmark run. It is a Jinja2 template based reporting system
# that includes graphs of various metrics, captured during the benchmark.
reportScript=./generateReport.py -t report_simple.html
#reportScript=./generateReport.py \
# -t report_extended.html \
# -c 'mydbserver.localdomain:DB server' \
# -d 'mydbserver.localdomain:DB server:hda2' \
# -i 'mydbserver.localdomain:DB server:eth0' \
# -c 'mybackrest.localdomain:pgbackrest server' \
# -d 'mybackrest.localdomain:pgbackrest server:hda2' \
# -i 'mybackrest.localdomain:pgbackrest server:eth0'
Note
对于参数的详细说明，请参考 BenchMarksql 6.0 配置文件 章节。
3 PostgreSQL 配置
psql -U postgres -d testdb
DROP USER benchmarksql;
CREATE USER benchmarksql PASSWORD 'benchmarksql' SUPERUSER;
4 未优化之前的基准测试
4.1 数据生成
su - postgres
cd /soft/benchmarksql/target/run
./runDatabaseBuild.sh my.properties
以下是数据生成的日志。
# ------------------------------------------------------------
# Loading SQL file ./sql.common/tableCreates.sql
# ------------------------------------------------------------
2024-06-07 14:15:25,263 INFO - create table bmsql_config (
cfg_name varchar(30) primary key,
cfg_value varchar(50)
);
2024-06-07 14:15:25,274 INFO - 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)
);
2024-06-07 14:15:25,276 INFO - 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)
);
2024-06-07 14:15:25,278 INFO - 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)
);
2024-06-07 14:15:25,281 INFO - create table bmsql_history (
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)
);
2024-06-07 14:15:25,283 INFO - create table bmsql_new_order (
no_w_id integer not null,
no_d_id integer not null,
no_o_id integer not null
);
2024-06-07 14:15:25,284 INFO - 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
);
2024-06-07 14:15:25,286 INFO - 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)
);
2024-06-07 14:15:25,287 INFO - 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
);
2024-06-07 14:15:25,289 INFO - 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)
);
# ------------------------------------------------------------
# Loading SQL file ./sql.postgres/extraCommandsBeforeLoad.sql
# ------------------------------------------------------------
2024-06-07 14:15:27,365 INFO - ALTER TABLE bmsql_oorder SET (FILLFACTOR = 85);
2024-06-07 14:15:27,371 INFO - ALTER TABLE bmsql_order_line SET (FILLFACTOR = 85);
2024-06-07 14:15:27,372 INFO - ALTER TABLE bmsql_warehouse SET (FILLFACTOR = 50);
2024-06-07 14:15:27,373 INFO - ALTER TABLE bmsql_district SET (FILLFACTOR = 79);
2024-06-07 14:15:27,374 INFO - ALTER TABLE bmsql_customer SET (FILLFACTOR = 90);
2024-06-07 14:15:27,375 INFO - ALTER TABLE bmsql_stock SET (FILLFACTOR = 95);
# ------------------------------------------------------------
# Loading SQL file ./sql.postgres/storedProcedureCreates.sql
# ------------------------------------------------------------
2024-06-07 14:15:29,274 INFO - CREATE OR REPLACE FUNCTION bmsql_cid_from_clast(
in_c_w_id integer,
in_c_d_id integer,
in_c_last varchar(16))
RETURNS integer AS
$
DECLARE
cust_cursor CURSOR (
p_w_id integer, p_d_id integer, p_c_last varchar(16))
FOR
SELECT c_id FROM bmsql_customer
WHERE c_w_id = p_w_id
AND c_d_id = p_d_id
AND c_last = p_c_last
ORDER BY c_first;
num_cust integer;
idx_cust integer;
ret_c_id integer;
BEGIN
-- Clause 2.5.2.2 Case 2, customer selected based on c_last.
SELECT INTO num_cust count(*)
FROM bmsql_customer
WHERE c_w_id = in_c_w_id
AND c_d_id = in_c_d_id
AND c_last = in_c_last;
IF num_cust = 0 THEN
RAISE EXCEPTION 'Customer(s) for C_W_ID=% C_D_ID=% C_LAST=% not found',
in_c_w_id, in_c_d_id, in_c_last;
END IF;
idx_cust = (num_cust + 1) / 2 - 1;
OPEN cust_cursor(in_c_w_id, in_c_d_id, in_c_last);
MOVE FORWARD idx_cust IN cust_cursor;
FETCH FROM cust_cursor INTO ret_c_id;
CLOSE cust_cursor;
RETURN ret_c_id;
END;
$
LANGUAGE plpgsql;
2024-06-07 14:15:29,283 INFO - CREATE OR REPLACE FUNCTION bmsql_proc_new_order(
IN in_w_id integer,
IN in_d_id integer,
IN in_c_id integer,
IN in_ol_supply_w_id integer[],
IN in_ol_i_id integer[],
IN in_ol_quantity integer[],
OUT out_w_tax decimal(4, 4),
OUT out_d_tax decimal(4, 4),
OUT out_o_id integer,
OUT out_o_entry_d timestamp,
OUT out_ol_cnt integer,
OUT out_ol_amount decimal(12, 2)[],
OUT out_total_amount decimal(12, 2),
OUT out_c_last varchar(16),
OUT out_c_credit char(2),
OUT out_c_discount decimal(4, 4),
OUT out_i_name varchar(24)[],
OUT out_i_price decimal(5, 2)[],
OUT out_s_quantity integer[],
OUT out_brand_generic char[]
) AS
$
DECLARE
var_all_local integer := 1;
var_x integer;
var_y integer;
var_tmp integer;
var_seq integer[15];
var_item_row record;
var_stock_row record;
BEGIN
-- The o_entry_d is now.
out_o_entry_d := CURRENT_TIMESTAMP;
out_total_amount := 0.00;
-- When processing the order lines we must select the STOCK rows
-- FOR UPDATE. This is because we must perform business logic
-- (the juggling with the S_QUANTITY) here in the application
-- and cannot do that in an atomic UPDATE statement while getting
-- the original value back at the same time (UPDATE ... RETURNING
-- may not be vendor neutral). This can lead to possible deadlocks
-- if two transactions try to lock the same two stock rows in
-- opposite order. To avoid that we process the order lines in
-- the order of the order of ol_supply_w_id, ol_i_id.
out_ol_cnt := 0;
FOR var_x IN 1 .. array_length(in_ol_i_id, 1) LOOP
IF in_ol_i_id[var_x] IS NOT NULL AND in_ol_i_id[var_x] <> 0 THEN
out_ol_cnt := out_ol_cnt + 1;
var_seq[var_x] = var_x;
IF in_ol_supply_w_id[var_x] <> in_w_id THEN
var_all_local := 0;
END IF;
END IF;
END LOOP;
FOR var_x IN 1 .. out_ol_cnt - 1 LOOP
FOR var_y IN var_x + 1 .. out_ol_cnt LOOP
IF in_ol_supply_w_id[var_seq[var_y]] < in_ol_supply_w_id[var_seq[var_x]] THEN
var_tmp = var_seq[var_x];
var_seq[var_x] = var_seq[var_y];
var_seq[var_y] = var_tmp;
ELSE
IF in_ol_supply_w_id[var_seq[var_y]] = in_ol_supply_w_id[var_seq[var_x]]
AND in_ol_i_id[var_seq[var_y]] < in_ol_i_id[var_seq[var_x]] THEN
var_tmp = var_seq[var_x];
var_seq[var_x] = var_seq[var_y];
var_seq[var_y] = var_tmp;
END IF;
END IF;
END LOOP;
END LOOP;
-- Retrieve the required data from DISTRICT
SELECT INTO out_d_tax, out_o_id
d_tax, d_next_o_id
FROM bmsql_district
WHERE d_w_id = in_w_id AND d_id = in_d_id
FOR UPDATE;
-- Retrieve the required data from CUSTOMER and WAREHOUSE
SELECT INTO out_w_tax, out_c_last, out_c_credit, out_c_discount
w_tax, c_last, c_credit, c_discount
FROM bmsql_customer
JOIN bmsql_warehouse ON (w_id = c_w_id)
WHERE c_w_id = in_w_id AND c_d_id = in_d_id AND c_id = in_c_id;
-- Update the DISTRICT bumping the D_NEXT_O_ID
UPDATE bmsql_district
SET d_next_o_id = d_next_o_id + 1
WHERE d_w_id = in_w_id AND d_id = in_d_id;
-- Insert the ORDER row
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 (
out_o_id, in_d_id, in_w_id, in_c_id, out_o_entry_d,
out_ol_cnt, var_all_local);
-- Insert the NEW_ORDER row
INSERT INTO bmsql_new_order (
no_o_id, no_d_id, no_w_id)
VALUES (
out_o_id, in_d_id, in_w_id);
-- Per ORDER_LINE
FOR var_x IN 1 .. out_ol_cnt LOOP
-- We process the lines in the sequence orderd by warehouse, item.
var_y = var_seq[var_x];
SELECT INTO var_item_row
i_name, i_price, i_data
FROM bmsql_item
WHERE i_id = in_ol_i_id[var_y];
IF NOT FOUND THEN
RAISE EXCEPTION 'Item number is not valid';
END IF;
-- Found ITEM
out_i_name[var_y] = var_item_row.i_name;
out_i_price[var_y] = var_item_row.i_price;
SELECT INTO var_stock_row
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 = in_ol_supply_w_id[var_y]
AND s_i_id = in_ol_i_id[var_y]
FOR UPDATE;
IF NOT FOUND THEN
RAISE EXCEPTION 'STOCK not found: %,%', in_ol_supply_w_id[var_y],
in_ol_i_id[var_y];
END IF;
out_s_quantity[var_y] = var_stock_row.s_quantity;
out_ol_amount[var_y] = out_i_price[var_y] * in_ol_quantity[var_y];
IF var_item_row.i_data LIKE '%ORIGINAL%'
AND var_stock_row.s_data LIKE '%ORIGINAL%' THEN
out_brand_generic[var_y] := 'B';
ELSE
out_brand_generic[var_y] := 'G';
END IF;
out_total_amount = out_total_amount +
out_ol_amount[var_y] * (1.0 - out_c_discount)
* (1.0 + out_w_tax + out_d_tax);
-- Update the STOCK row.
UPDATE bmsql_stock SET
s_quantity = CASE
WHEN var_stock_row.s_quantity >= in_ol_quantity[var_y] + 10 THEN
var_stock_row.s_quantity - in_ol_quantity[var_y]
ELSE
var_stock_row.s_quantity + 91
END,
s_ytd = s_ytd + in_ol_quantity[var_y],
s_order_cnt = s_order_cnt + 1,
s_remote_cnt = s_remote_cnt + CASE
WHEN in_w_id <> in_ol_supply_w_id[var_y] THEN
1
ELSE
0
END
WHERE s_w_id = in_ol_supply_w_id[var_y]
AND s_i_id = in_ol_i_id[var_y];
-- Insert the ORDER_LINE row.
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 (
out_o_id, in_d_id, in_w_id, var_y,
in_ol_i_id[var_y], in_ol_supply_w_id[var_y], in_ol_quantity[var_y],
out_ol_amount[var_y],
CASE
WHEN in_d_id = 1 THEN var_stock_row.s_dist_01
WHEN in_d_id = 2 THEN var_stock_row.s_dist_02
WHEN in_d_id = 3 THEN var_stock_row.s_dist_03
WHEN in_d_id = 4 THEN var_stock_row.s_dist_04
WHEN in_d_id = 5 THEN var_stock_row.s_dist_05
WHEN in_d_id = 6 THEN var_stock_row.s_dist_06
WHEN in_d_id = 7 THEN var_stock_row.s_dist_07
WHEN in_d_id = 8 THEN var_stock_row.s_dist_08
WHEN in_d_id = 9 THEN var_stock_row.s_dist_09
WHEN in_d_id = 10 THEN var_stock_row.s_dist_10
END);
END LOOP;
RETURN;
END;
$
LANGUAGE plpgsql;
2024-06-07 14:15:29,289 INFO - CREATE OR REPLACE FUNCTION bmsql_proc_payment(
IN in_w_id integer,
IN in_d_id integer,
INOUT in_c_id integer,
IN in_c_d_id integer,
IN in_c_w_id integer,
IN in_c_last varchar(16),
IN in_h_amount decimal(6,2),
OUT out_w_name varchar(10),
OUT out_w_street_1 varchar(20),
OUT out_w_street_2 varchar(20),
OUT out_w_city varchar(20),
OUT out_w_state char(2),
OUT out_w_zip char(9),
OUT out_d_name varchar(10),
OUT out_d_street_1 varchar(20),
OUT out_d_street_2 varchar(20),
OUT out_d_city varchar(20),
OUT out_d_state char(2),
OUT out_d_zip char(9),
OUT out_c_first varchar(16),
OUT out_c_middle char(2),
OUT out_c_street_1 varchar(20),
OUT out_c_street_2 varchar(20),
OUT out_c_city varchar(20),
OUT out_c_state char(2),
OUT out_c_zip char(9),
OUT out_c_phone char(16),
OUT out_c_since timestamp,
OUT out_c_credit char(2),
OUT out_c_credit_lim decimal(12,2),
OUT out_c_discount decimal(4,4),
OUT out_c_balance decimal(12,2),
OUT out_c_data varchar(500),
OUT out_h_date timestamp
) AS
$
BEGIN
out_h_date := CURRENT_TIMESTAMP;
--Update the DISTRICT
UPDATE bmsql_district
SET d_ytd = d_ytd + in_h_amount
WHERE d_w_id = in_w_id AND d_id = in_d_id;
--Select the DISTRICT
SELECT INTO out_d_name, out_d_street_1, out_d_street_2,
out_d_city, out_d_state, out_d_zip
d_name, d_street_1, d_street_2, d_city, d_state, d_zip
FROM bmsql_district
WHERE d_w_id = in_w_id AND d_id = in_d_id
FOR UPDATE;
--Update the WAREHOUSE
UPDATE bmsql_warehouse
SET w_ytd = w_ytd + in_h_amount
WHERE w_id = in_w_id;
--Select the WAREHOUSE
SELECT INTO out_w_name, out_w_street_1, out_w_street_2,
out_w_city, out_w_state, out_w_zip
w_name, w_street_1, w_street_2, w_city, w_state, w_zip
FROM bmsql_warehouse
WHERE w_id = in_w_id
FOR UPDATE;
--If C_Last is given instead of C_ID (60%), determine the C_ID.
IF in_c_last IS NOT NULL THEN
in_c_id = bmsql_cid_from_clast(in_c_w_id, in_c_d_id, in_c_last);
END IF;
--Select the CUSTOMER
SELECT INTO out_c_first, out_c_middle, in_c_last, out_c_street_1,
out_c_street_2, out_c_city, out_c_state, out_c_zip,
out_c_phone, out_c_since, out_c_credit, out_c_credit_lim,
out_c_discount, out_c_balance
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 = in_c_w_id AND c_d_id = in_c_d_id AND c_id = in_c_id
FOR UPDATE;
--Update the CUSTOMER
out_c_balance = out_c_balance-in_h_amount;
IF out_c_credit = 'GC' THEN
--Customer with good credit, don't update C_DATA
UPDATE bmsql_customer
SET c_balance = c_balance - in_h_amount,
c_ytd_payment = c_ytd_payment + in_h_amount,
c_payment_cnt = c_payment_cnt + 1
WHERE c_w_id = in_c_w_id AND c_d_id=in_c_d_id AND c_id=in_c_id;
out_c_data := '';
ELSE
--Customer with bad credit, need to do the C_DATA work.
SELECT INTO out_c_data
c_data
FROM bmsql_customer
WHERE c_w_id = in_c_w_id AND c_d_id = in_c_d_id
AND c_id = in_c_id;
out_c_data := substring('C_ID=' || in_c_id::text ||
' C_D_ID=' || in_c_d_id::text ||
' C_W_ID=' || in_c_w_id::text ||
' D_ID=' || in_d_id::text ||
' W_ID=' || in_w_id::text ||
' H_AMOUNT=' || round(in_h_amount,2)::text || ' ' ||
out_c_data from 1 for 500);
UPDATE bmsql_customer
SET c_balance = c_balance - in_h_amount,
c_ytd_payment = c_ytd_payment + in_h_amount,
c_payment_cnt = c_payment_cnt + 1,
c_data = out_c_data
WHERE c_w_id = in_c_w_id AND c_d_id = in_c_d_id
AND c_id = in_c_id;
END IF;
--Insert the HISTORY row
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 (
in_c_id, in_c_d_id, in_c_w_id, in_d_id, in_w_id,
out_h_date, in_h_amount, out_w_name||' '|| out_d_name
);
END;
$
LANGUAGE plpgsql;
2024-06-07 14:15:29,292 INFO - CREATE OR REPLACE FUNCTION bmsql_proc_order_status(
IN in_w_id integer,
IN in_d_id integer,
INOUT in_c_id integer,
IN in_c_last varchar(16),
OUT out_c_first varchar(16),
OUT out_c_middle char(2),
OUT out_c_balance decimal(12,2),
OUT out_o_id integer,
OUT out_o_entry_d varchar(24),
OUT out_o_carrier_id integer,
OUT out_ol_supply_w_id integer[],
OUT out_ol_i_id integer[],
OUT out_ol_quantity integer[],
OUT out_ol_amount decimal(12,2)[],
OUT out_ol_delivery_d timestamp[]
) AS
$
DECLARE
v_order_line record;
v_ol_idx integer := 1;
BEGIN
--If C_LAST is given instead of C_ID (60%), determine the C_ID.
IF in_c_last IS NOT NULL THEN
in_c_id = bmsql_cid_from_clast(in_w_id, in_d_id, in_c_last);
END IF;
--Select the CUSTOMER
SELECT INTO out_c_first, out_c_middle, in_c_last, out_c_balance
c_first, c_middle, c_last, c_balance
FROM bmsql_customer
WHERE c_w_id=in_w_id AND c_d_id=in_d_id AND c_id = in_c_id;
--Select the last ORDER for this customer.
SELECT INTO out_o_id, out_o_entry_d, out_o_carrier_id
o_id, o_entry_d, coalesce(o_carrier_id, -1)
FROM bmsql_oorder
WHERE o_w_id = in_w_id AND o_d_id = in_d_id AND o_c_id = in_c_id
AND o_id = (
SELECT max(o_id)
FROM bmsql_oorder
WHERE o_w_id = in_w_id AND o_d_id = in_d_id AND o_c_id = in_c_id
);
FOR v_order_line IN SELECT ol_i_id, ol_supply_w_id, ol_quantity,
ol_amount, ol_delivery_d
FROM bmsql_order_line
WHERE ol_w_id = in_w_id AND ol_d_id = in_d_id AND ol_o_id = out_o_id
ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number
LOOP
out_ol_i_id[v_ol_idx] = v_order_line.ol_i_id;
out_ol_supply_w_id[v_ol_idx] = v_order_line.ol_supply_w_id;
out_ol_quantity[v_ol_idx] = v_order_line.ol_quantity;
out_ol_amount[v_ol_idx] = v_order_line.ol_amount;
out_ol_delivery_d[v_ol_idx] = v_order_line.ol_delivery_d;
v_ol_idx = v_ol_idx + 1;
END LOOP;
WHILE v_ol_idx < 16 LOOP
out_ol_i_id[v_ol_idx] = 0;
out_ol_supply_w_id[v_ol_idx] = 0;
out_ol_quantity[v_ol_idx] = 0;
out_ol_amount[v_ol_idx] = 0.0;
out_ol_delivery_d[v_ol_idx] = NULL;
v_ol_idx = v_ol_idx +1;
END LOOP;
END;
$
Language plpgsql;
2024-06-07 14:15:29,295 INFO - CREATE OR REPLACE FUNCTION bmsql_proc_stock_level(
IN in_w_id integer,
IN in_d_id integer,
IN in_threshold integer,
OUT out_low_stock integer
) AS
$
BEGIN
SELECT INTO out_low_stock
count(*) AS low_stock
FROM (
SELECT s_w_id, s_i_id, s_quantity
FROM bmsql_stock
WHERE s_w_id = in_w_id AND s_quantity < in_threshold
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 = in_w_id AND d_id = in_d_id
)
) AS L;
END;
$
LANGUAGE plpgsql;
2024-06-07 14:15:29,296 INFO - CREATE OR REPLACE FUNCTION bmsql_proc_delivery_bg(
IN in_w_id integer,
IN in_o_carrier_id integer,
IN in_ol_delivery_d timestamp,
OUT out_delivered_o_id integer[]
) AS
$
DECLARE
var_d_id integer;
var_o_id integer;
var_c_id integer;
var_sum_ol_amount decimal(12, 2);
BEGIN
FOR var_d_id IN 1..10 LOOP
var_o_id = -1;
/*
* Try to find the oldest undelivered order for this
* DISTRICT. There may not be one, which is a case
* that needs to be reported.
*/
WHILE var_o_id < 0 LOOP
SELECT INTO var_o_id
no_o_id
FROM bmsql_new_order
WHERE no_w_id = in_w_id AND no_d_id = var_d_id
ORDER BY no_o_id ASC;
IF NOT FOUND THEN
var_o_id = -1;
EXIT;
END IF;
DELETE FROM bmsql_new_order
WHERE no_w_id = in_w_id AND no_d_id = var_d_id
AND no_o_id = var_o_id;
IF NOT FOUND THEN
var_o_id = -1;
END IF;
END LOOP;
IF var_o_id < 0 THEN
-- No undelivered NEW_ORDER found for this District.
var_d_id = var_d_id + 1;
CONTINUE;
END IF;
/*
* We found out oldert undelivered order for this DISTRICT
* and the NEW_ORDER line has been deleted. Process the
* rest of the DELIVERY_BG.
*/
-- Update the ORDER setting the o_carrier_id.
UPDATE bmsql_oorder
SET o_carrier_id = in_o_carrier_id
WHERE o_w_id = in_w_id AND o_d_id = var_d_id AND o_id = var_o_id;
-- Get the o_c_id from the ORDER.
SELECT INTO var_c_id
o_c_id
FROM bmsql_oorder
WHERE o_w_id = in_w_id AND o_d_id = var_d_id AND o_id = var_o_id;
-- Update ORDER_LINE setting the ol_delivery_d.
UPDATE bmsql_order_line
SET ol_delivery_d = in_ol_delivery_d
WHERE ol_w_id = in_w_id AND ol_d_id = var_d_id
AND ol_o_id = var_o_id;
-- SELECT the sum(ol_amount) from ORDER_LINE.
SELECT INTO var_sum_ol_amount
sum(ol_amount) AS sum_ol_amount
FROM bmsql_order_line
WHERE ol_w_id = in_w_id AND ol_d_id = var_d_id
AND ol_o_id = var_o_id;
-- Update the CUSTOMER.
UPDATE bmsql_customer
SET c_balance = c_balance + var_sum_ol_amount,
c_delivery_cnt = c_delivery_cnt + 1
WHERE c_w_id = in_w_id AND c_d_id = var_d_id and c_id = var_c_id;
out_delivered_o_id[var_d_id] = var_o_id;
var_d_id = var_d_id +1 ;
END LOOP;
END;
$
LANGUAGE plpgsql;
2024-06-07 14:15:30,926 INFO - Starting BenchmarkSQL LoadData
2024-06-07 14:15:30,930 INFO -
2024-06-07 14:15:30,932 INFO - random initialized
2024-06-07 14:15:30,934 INFO - driver=org.postgresql.Driver
2024-06-07 14:15:30,939 INFO - conn=jdbc:postgresql://localhost:5432/testdb
2024-06-07 14:15:30,939 INFO - user=benchmarksql
2024-06-07 14:15:30,939 INFO - password=***********
2024-06-07 14:15:30,939 INFO - warehouses=10
2024-06-07 14:15:30,939 INFO - loadWorkers=8
2024-06-07 14:15:30,940 WARN - fileLocation (not defined)
2024-06-07 14:15:30,940 WARN - csvNullValue (not defined - using default 'NULL')
2024-06-07 14:15:30,940 INFO -
2024-06-07 14:15:31,382 INFO - Worker 000: Loading ITEM
2024-06-07 14:15:31,445 INFO - Worker 001: Loading Warehouse 1
2024-06-07 14:15:31,505 INFO - Worker 002: Loading Warehouse 2
2024-06-07 14:15:31,604 INFO - Worker 003: Loading Warehouse 3
2024-06-07 14:15:31,725 INFO - Worker 004: Loading Warehouse 4
2024-06-07 14:15:32,029 INFO - Worker 005: Loading Warehouse 5
2024-06-07 14:15:32,177 INFO - Worker 006: Loading Warehouse 6
2024-06-07 14:15:32,268 INFO - Worker 007: Loading Warehouse 7
2024-06-07 14:15:40,030 INFO - Worker 000: Loading ITEM done
2024-06-07 14:15:40,030 INFO - Worker 000: Loading Warehouse 8
2024-06-07 14:15:52,431 INFO - Worker 001: Loading Warehouse 1 done
2024-06-07 14:15:52,431 INFO - Worker 001: Loading Warehouse 9
2024-06-07 14:15:53,044 INFO - Worker 002: Loading Warehouse 2 done
2024-06-07 14:15:53,044 INFO - Worker 002: Loading Warehouse 10
2024-06-07 14:15:53,399 INFO - Worker 003: Loading Warehouse 3 done
2024-06-07 14:15:53,400 INFO - Loading Orders with O_ID 1 and higher
2024-06-07 14:15:53,533 INFO - Worker 005: Loading Warehouse 5 done
2024-06-07 14:15:53,719 INFO - Worker 006: Loading Warehouse 6 done
2024-06-07 14:15:53,788 INFO - Worker 004: Loading Warehouse 4 done
2024-06-07 14:15:54,199 INFO - Worker 007: Loading Warehouse 7 done
2024-06-07 14:15:58,306 INFO - Worker 000: Loading Warehouse 8 done
2024-06-07 14:15:58,879 INFO - Loading Orders with O_ID 101 and higher
2024-06-07 14:16:03,146 INFO - Loading Orders with O_ID 201 and higher
2024-06-07 14:16:05,059 INFO - Worker 001: Loading Warehouse 9 done
2024-06-07 14:16:05,572 INFO - Worker 002: Loading Warehouse 10 done
2024-06-07 14:16:06,372 INFO - Loading Orders with O_ID 301 and higher
2024-06-07 14:16:08,973 INFO - Loading Orders with O_ID 401 and higher
2024-06-07 14:16:11,155 INFO - Loading Orders with O_ID 501 and higher
2024-06-07 14:16:13,563 INFO - Loading Orders with O_ID 601 and higher
2024-06-07 14:16:16,076 INFO - Loading Orders with O_ID 701 and higher
2024-06-07 14:16:18,437 INFO - Loading Orders with O_ID 801 and higher
2024-06-07 14:16:20,805 INFO - Loading Orders with O_ID 901 and higher
2024-06-07 14:16:23,291 INFO - Loading Orders with O_ID 1001 and higher
2024-06-07 14:16:25,600 INFO - Loading Orders with O_ID 1101 and higher
2024-06-07 14:16:28,181 INFO - Loading Orders with O_ID 1201 and higher
2024-06-07 14:16:30,557 INFO - Loading Orders with O_ID 1301 and higher
2024-06-07 14:16:32,825 INFO - Loading Orders with O_ID 1401 and higher
2024-06-07 14:16:35,334 INFO - Loading Orders with O_ID 1501 and higher
2024-06-07 14:16:37,764 INFO - Loading Orders with O_ID 1601 and higher
2024-06-07 14:16:40,427 INFO - Loading Orders with O_ID 1701 and higher
2024-06-07 14:16:43,240 INFO - Loading Orders with O_ID 1801 and higher
2024-06-07 14:16:45,620 INFO - Loading Orders with O_ID 1901 and higher
2024-06-07 14:16:48,635 INFO - Loading Orders with O_ID 2001 and higher
2024-06-07 14:16:51,566 INFO - Loading Orders with O_ID 2101 and higher
2024-06-07 14:16:54,364 INFO - Loading Orders with O_ID 2201 and higher
2024-06-07 14:16:56,988 INFO - Loading Orders with O_ID 2301 and higher
2024-06-07 14:17:00,048 INFO - Loading Orders with O_ID 2401 and higher
2024-06-07 14:17:03,447 INFO - Loading Orders with O_ID 2501 and higher
2024-06-07 14:17:06,361 INFO - Loading Orders with O_ID 2601 and higher
2024-06-07 14:17:09,531 INFO - Loading Orders with O_ID 2701 and higher
2024-06-07 14:17:12,844 INFO - Loading Orders with O_ID 2801 and higher
2024-06-07 14:17:15,430 INFO - Loading Orders with O_ID 2901 and higher
2024-06-07 14:17:17,736 INFO - Loading Orders done
# ------------------------------------------------------------
# Loading SQL file ./sql.common/indexCreates.sql
# ------------------------------------------------------------
2024-06-07 14:17:19,579 INFO - alter table bmsql_warehouse add constraint bmsql_warehouse_pkey
primary key (w_id);
2024-06-07 14:17:19,594 INFO - alter table bmsql_district add constraint bmsql_district_pkey
primary key (d_w_id, d_id);
2024-06-07 14:17:19,597 INFO - alter table bmsql_customer add constraint bmsql_customer_pkey
primary key (c_w_id, c_d_id, c_id);
2024-06-07 14:17:19,882 INFO - create index bmsql_customer_idx1
on bmsql_customer (c_w_id, c_d_id, c_last, c_first);
2024-06-07 14:17:20,634 INFO - alter table bmsql_oorder add constraint bmsql_oorder_pkey
primary key (o_w_id, o_d_id, o_id);
2024-06-07 14:17:20,890 INFO - create unique index bmsql_oorder_idx1
on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);
2024-06-07 14:17:21,192 INFO - alter table bmsql_new_order add constraint bmsql_new_order_pkey
primary key (no_w_id, no_d_id, no_o_id);
2024-06-07 14:17:21,298 INFO - alter table bmsql_order_line add constraint bmsql_order_line_pkey
primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);
2024-06-07 14:17:24,281 INFO - alter table bmsql_stock add constraint bmsql_stock_pkey
primary key (s_w_id, s_i_id);
2024-06-07 14:17:25,823 INFO - alter table bmsql_item add constraint bmsql_item_pkey
primary key (i_id);
# ------------------------------------------------------------
# Loading SQL file ./sql.common/foreignKeys.sql
# ------------------------------------------------------------
2024-06-07 14:17:27,558 INFO - alter table bmsql_district add constraint d_warehouse_fkey
foreign key (d_w_id)
references bmsql_warehouse (w_id);
2024-06-07 14:17:27,568 INFO - alter table bmsql_customer add constraint c_district_fkey
foreign key (c_w_id, c_d_id)
references bmsql_district (d_w_id, d_id);
2024-06-07 14:17:27,630 INFO - alter table bmsql_history add constraint h_customer_fkey
foreign key (h_c_w_id, h_c_d_id, h_c_id)
references bmsql_customer (c_w_id, c_d_id, c_id);
2024-06-07 14:17:27,972 INFO - alter table bmsql_history add constraint h_district_fkey
foreign key (h_w_id, h_d_id)
references bmsql_district (d_w_id, d_id);
2024-06-07 14:17:28,038 INFO - alter table bmsql_new_order add constraint no_order_fkey
foreign key (no_w_id, no_d_id, no_o_id)
references bmsql_oorder (o_w_id, o_d_id, o_id);
2024-06-07 14:17:28,208 INFO - alter table bmsql_oorder add constraint o_customer_fkey
foreign key (o_w_id, o_d_id, o_c_id)
references bmsql_customer (c_w_id, c_d_id, c_id);
2024-06-07 14:17:28,593 INFO - alter table bmsql_order_line add constraint ol_order_fkey
foreign key (ol_w_id, ol_d_id, ol_o_id)
references bmsql_oorder (o_w_id, o_d_id, o_id);
2024-06-07 14:17:29,790 INFO - alter table bmsql_order_line add constraint ol_stock_fkey
foreign key (ol_supply_w_id, ol_i_id)
references bmsql_stock (s_w_id, s_i_id);
2024-06-07 14:17:31,501 INFO - alter table bmsql_stock add constraint s_warehouse_fkey
foreign key (s_w_id)
references bmsql_warehouse (w_id);
2024-06-07 14:17:31,659 INFO - alter table bmsql_stock add constraint s_item_fkey
foreign key (s_i_id)
references bmsql_item (i_id);
# ------------------------------------------------------------
# Loading SQL file ./sql.postgres/buildFinish.sql
# ------------------------------------------------------------
2024-06-07 14:17:33,580 INFO - -- ----
2024-06-07 14:17:33,584 INFO - -- Extra commands to run after the tables are created, loaded,
2024-06-07 14:17:33,584 INFO - -- indexes built and extra's created.
2024-06-07 14:17:33,584 INFO - -- PostgreSQL version.
2024-06-07 14:17:33,585 INFO - -- ----
2024-06-07 14:17:33,587 INFO - vacuum freeze analyze;
[postgres@node1 run]$
4.2 进行基准测试
可以通过两种方式进行基准测试。
- 第一种方式：使用命令行进行基准测试。
nohup ./runBenchmark.sh my.properties > run.log 2>&1 &
- 第二种方式：使用 BenchmarkSQL WEB服务 可以运行基准测试，并直观的看到性能的结果。
4.3 基准测试报告解读
使用 BenchmarkSQL WEB服务 可以运行基准测试，并直观的看到性能的结果。
1、请点击 [[BenchmarkSQL Run.html]]报告并查看基准测试的结果。
2、这里是部分截图。
5 已优化之后的基准测试
6 小结 & FAQ
6.1 重建测试数据
- 切换用户进入
run目录。
su - postgres
#postgres>
cd /soft/benchmarksql/target/run
- 清除数据。
./runDatabaseDestroy.sh my.properties
- 重建数据。
./runDatabaseBuild.sh my.properties