原文鏈接：https://www.modb.pro/db/22513?03= （複製至瀏覽器，即可查看）
摘要：GaussDB T 1.0.2單機環境TPC-C之BenchmarkSQL性能測試
1、概述
TPC是一系列事務處理和數據庫基準測試的規範。其中TPC-C是針對OLTP的基準測試模型，一方面可以衡量數據庫的性能，另一方面可以衡量硬件性價比，也是廣泛應用並關注的一種測試模型。TPC-C通過TPM去衡量性價比，即每分鐘的執行事務量。
本文介紹TPC-C之BenchmarkSQL對GaussDB T單機數據庫進行性能壓測。
對於分佈式集群下的BenchmarkSQL測試，GaussDB T需要做適配，因為分佈式下創建表必須要指定分片鍵的，默認的腳本是不指定的。
2、 BenchmarkSQL性能測試
2.1 安裝JDK
BenchmarkSQL工具要求JDK7以上。檢查系統JDK版本：
<code># java -version
openjdk version "1.8.0_181"
OpenJDK Runtime Environment (build 1.8.0_181-b13)
OpenJDK 64-Bit Server VM (build 25.181-b13, mixed mode)
#
/<code>
2.2 安裝依賴包
<code># yum -y install ant
/<code>
2.3 上傳BenchmarkSQL工具包並解壓
這裡使用benchmarksql-5.0.zip工具包。
<code># mkdir -p /software/benchmark/
# cd /software/benchmark/
# ls -l
總用量 2212
-rw-r--r-- 1 root root 2263539 3月 10 13:48 benchmarksql-5.0.zip
#
# unzip benchmarksql-5.0.zip
Archive: benchmarksql-5.0.zip
…….
…….
# ls -l
總用量 2212
drwxr-xr-x 6 root root 124 5月 26 2016 benchmarksql-5.0
-rw-r--r-- 1 root root 2263539 3月 10 13:48 benchmarksql-5.0.zip
#
# cd benchmarksql-5.0/
# ls -l
總用量 24
-rwxr-xr-x 1 root root 1130 5月 26 2016 build.xml
drwxr-xr-x 3 root root 17 5月 26 2016 doc
-rwxr-xr-x 1 root root 6376 5月 26 2016 HOW-TO-RUN.txt
drwxr-xr-x 5 root root 129 5月 26 2016 lib
-rwxr-xr-x 1 root root 5318 5月 26 2016 README.md
drwxr-xr-x 7 root root 4096 5月 26 2016 run
drwxr-xr-x 6 root root 67 5月 26 2016 src
#
/<code>
2.4 編譯BenchmarkSQL
使用ANT編譯BenchMark的包，需要用到BenchMark的build包，進入到BenchMark的目錄，執行ant進行編譯。
<code># cd /software/benchmark/benchmarksql-5.0/
# ant
Buildfile: /software/benchmark/benchmarksql-5.0/build.xml
init:
[mkdir] Created dir: /software/benchmark/benchmarksql-5.0/build
compile:
[javac] Compiling 11 source files to /software/benchmark/benchmarksql-5.0/build
dist:
[mkdir] Created dir: /software/benchmark/benchmarksql-5.0/dist
[jar] Building jar: /software/benchmark/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar
BUILD SUCCESSFUL
Total time: 4 seconds
# ls -l
總用量 28
drwxr-xr-x 2 root root 4096 3月 10 13:52 build
-rwxr-xr-x 1 root root 1130 5月 26 2016 build.xml
drwxr-xr-x 2 root root 34 3月 10 13:52 dist
drwxr-xr-x 3 root root 17 5月 26 2016 doc
-rwxr-xr-x 1 root root 6376 5月 26 2016 HOW-TO-RUN.txt
drwxr-xr-x 5 root root 129 5月 26 2016 lib
-rwxr-xr-x 1 root root 5318 5月 26 2016 README.md
drwxr-xr-x 7 root root 4096 5月 26 2016 run
drwxr-xr-x 6 root root 67 5月 26 2016 src
#
/<code>
2.5 拷貝GaussDB T的JDBC驅動到lib目錄
拷貝GaussDB T的JDBC驅動到BenchmarkSQL的lib目錄，jdbc驅動在安裝包GaussDB_T_1.0.2-CLIENT-JDBC.tar.gz裡。
<code># cd /software/benchmark/benchmarksql-5.0/lib/
# ls -l
總用量 1256
-rwxr-xr-x 1 root root 346729 5月 26 2016 apache-log4j-extras-1.1.jar
-r-x------ 1 root root 443090 3月 10 14:34 com.huawei.gauss.jdbc.ZenithDriver-GaussDB_T_1.0.2.B307.jar
drwxr-xr-x 2 root root 60 5月 26 2016 firebird
-rwxr-xr-x 1 root root 489883 5月 26 2016 log4j-1.2.17.jar
drwxr-xr-x 2 root root 42 5月 26 2016 oracle
drwxr-xr-x 2 root root 44 5月 26 2016 postgres
#
/<code>
Zsql連接數據庫，創建測試用戶並授權：
<code>$ zsql omm/[email protected]:1888 -q
connected.
SQL> create user aps2 identified by aps2_123;
Succeed.
SQL> grant connect,resource to aps2;
Succeed.
SQL> exit
$
/<code>
2.7 創建benchmark配置文件
在benchmarksql的run目錄下，編輯測試的配置文件props.gaussdb。
<code># pwd
/software/benchmark/benchmarksql-5.0/run
# cp props.ora props.gaussdb
# vi props.gaussdb
db=oracle
driver=com.huawei.gauss.jdbc.ZenithDriver
conn=jdbc:zenith:@192.168.179.123:1888
user=aps2
password=aps2_123
warehouses=10
loadWorkers=4
terminals=10
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=5
//Number of total transactions per minute
limitTxnsPerMin=300
//Set to true to run in 4.x compatible mode. Set to false to use the
//entire configured database evenly.
terminalWarehouseFixed=true
//The following five values must add up to 100
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
// Directory name to create for collecting detailed result data.
// Comment this out to suppress.
resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
osCollectorScript=./misc/os_collector_linux.py
osCollectorInterval=1
//osCollectorSSHAddr=user@dbhost
osCollectorDevices=net_eth0 blk_sda
#
/<code>
參數說明：配置文件中，db直接填寫oracle即可，驅動driver為com.huawei.gauss.jdbc.ZenithDriver，連接串conn為jdbc:zenith:@IP:端口，這裡配置連接數據庫的IP地址和端口，user為連接數據庫的用戶名aps2，password為用戶密碼aps2_123。
warehouse數據倉庫，表示markbench生成的數據表中數據量的大小。BenchmarkSQL數據庫每個warehouse大小大概是100MB，如果該參數設置為10，那整個數據庫的大小大概在1000MB。建議將數據庫的大小設置為服務器物理內存的2-5倍，如果服務器內存為16GB，那麼warehouse設置建議在328～819之間。
terminals 是指client的併發連接數，建議設置為服務器CPU總線程數的2-6倍。如果服務器為雙核16線程（單核8線程），那麼建議配置在32～96之間。
runTxnsPerTerminal是每分鐘的執行事務數，runtime限制了執行的時間。配置這兩個參數的時候，如果runTxnsPerTerminal的不為0的話，那麼runtime的值必須為0。反之，如果配置runtime的話，如果runtime不為0，則runTxnsPerTerminal一定要等於0。
limitTxnsPerMin=300 是限制每分鐘總共執行的事務數，0是無限制。
2.8 初始化測試數據
進入benchmarksql的run目錄下，運行./runDatabaseBuild.sh props.gaussdb腳本，初始化測試數據。
<code># pwd
/software/benchmark/benchmarksql-5.0/run
# ./runDatabaseBuild.sh props.gaussdb
# ------------------------------------------------------------
# Loading SQL file ./sql.common/tableCreates.sql
# ------------------------------------------------------------
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)
);
Starting BenchmarkSQL LoadData
driver=com.huawei.gauss.jdbc.ZenithDriver
conn=jdbc:zenith:@127.0.0.1:1888
user=aps2
password=***********
warehouses=10
loadWorkers=4
fileLocation (not defined)
csvNullValue (not defined - using default 'NULL')
Worker 000: Loading ITEM
Worker 001: Loading Warehouse 1
Worker 002: Loading Warehouse 2
Worker 003: Loading Warehouse 3
Worker 000: Loading ITEM done
Worker 000: Loading Warehouse 4
Worker 002: Loading Warehouse 2 done
Worker 002: Loading Warehouse 5
Worker 001: Loading Warehouse 1 done
Worker 001: Loading Warehouse 6
Worker 003: Loading Warehouse 3 done
Worker 003: Loading Warehouse 7
Worker 000: Loading Warehouse 4 done
Worker 000: Loading Warehouse 8
Worker 001: Loading Warehouse 6 done
Worker 001: Loading Warehouse 9
Worker 002: Loading Warehouse 5 done
Worker 002: Loading Warehouse 10
Worker 003: Loading Warehouse 7 done
Worker 000: Loading Warehouse 8 done
Worker 001: Loading Warehouse 9 done
Worker 002: Loading Warehouse 10 done
# ------------------------------------------------------------
# Loading SQL file ./sql.common/indexCreates.sql
# ------------------------------------------------------------
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);
# ------------------------------------------------------------
# Loading SQL file ./sql.common/foreignKeys.sql
# ------------------------------------------------------------
alter table bmsql_district add constraint d_warehouse_fkey
foreign key (d_w_id)
references bmsql_warehouse (w_id);
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);
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);
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);
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);
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);
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);
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);
alter table bmsql_stock add constraint s_warehouse_fkey
foreign key (s_w_id)
references bmsql_warehouse (w_id);
alter table bmsql_stock add constraint s_item_fkey
foreign key (s_i_id)
references bmsql_item (i_id);
# ------------------------------------------------------------
# Loading SQL file ./sql.oracle/extraHistID.sql
# ------------------------------------------------------------
-- ----
-- Extra Schema objects/definitions for history.hist_id in Oracle
-- ----
-- ----
--\tThis is an extra column not present in the TPC-C
--\tspecs. It is useful for replication systems like
--\tBucardo and Slony-I, which like to have a primary
--\tkey on a table. It is an auto-increment or serial
--\tcolumn type. The definition below is compatible
--\twith Oracle 11g, using the sequence in a trigger.
-- ----
-- Adjust the sequence above the current max(hist_id)
alter sequence bmsql_hist_id_seq increment by 30000;
declare
n integer;
i integer;
dummy integer;
begin
select max(hist_id) into n from bmsql_history;
i := 0;
while i <= n loop
select bmsql_hist_id_seq.nextval into dummy from dual;
i := i + 30000;
end loop;
end;
;
alter sequence bmsql_hist_id_seq increment by 1;
-- Create a trigger that forces hist_id to be hist_id_seq.nextval
create trigger bmsql_history_before_insert
before insert on bmsql_history
for each row
begin
if :new.hist_id is null then
select bmsql_hist_id_seq.nextval into :new.hist_id from dual;
end if;
end;
;
-- Add a primary key history(hist_id)
alter table bmsql_history add primary key (hist_id);
# ------------------------------------------------------------
# Loading SQL file ./sql.common/buildFinish.sql
# ------------------------------------------------------------
-- ----
-- Extra commands to run after the tables are created, loaded,
-- indexes built and extra's created.
-- ----
#
/<code>
2.9 開始測試
進入benchmarksql的run目錄下，運行./runBenchmark.sh props.gaussdb腳本，開始性能測試。
<code># pwd
/software/benchmark/benchmarksql-5.0/run
# ./runBenchmark.sh props.gaussdb
15:55:35,431 [main] INFO jTPCC : Term-00,
15:55:35,443 [main] INFO jTPCC : Term-00, +-------------------------------------------------+
15:55:35,443 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
15:55:35,444 [main] INFO jTPCC : Term-00, +-------------------------------------------------+
15:55:35,444 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
15:55:35,444 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
15:55:35,448 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
15:55:35,448 [main] INFO jTPCC : Term-00, +-------------------------------------------------+
15:55:35,448 [main] INFO jTPCC : Term-00,
15:55:35,448 [main] INFO jTPCC : Term-00, db=oracle
15:55:35,449 [main] INFO jTPCC : Term-00, driver=com.huawei.gauss.jdbc.ZenithDriver
15:55:35,449 [main] INFO jTPCC : Term-00, conn=jdbc:zenith:@127.0.0.1:1888
15:55:35,449 [main] INFO jTPCC : Term-00, user=aps2
15:55:35,449 [main] INFO jTPCC : Term-00,
15:55:35,449 [main] INFO jTPCC : Term-00, warehouses=10
15:55:35,450 [main] INFO jTPCC : Term-00, terminals=10
15:55:35,452 [main] INFO jTPCC : Term-00, runMins=5
15:55:35,452 [main] INFO jTPCC : Term-00, limitTxnsPerMin=300
15:55:35,452 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
15:55:35,452 [main] INFO jTPCC : Term-00,
15:55:35,453 [main] INFO jTPCC : Term-00, newOrderWeight=45
15:55:35,453 [main] INFO jTPCC : Term-00, paymentWeight=43
15:55:35,453 [main] INFO jTPCC : Term-00, orderStatusWeight=4
15:55:35,453 [main] INFO jTPCC : Term-00, deliveryWeight=4
15:55:35,453 [main] INFO jTPCC : Term-00, stockLevelWeight=4
15:55:35,453 [main] INFO jTPCC : Term-00,
15:55:35,453 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
15:55:35,454 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
15:55:35,454 [main] INFO jTPCC : Term-00,
15:55:35,552 [main] INFO jTPCC : Term-00, copied props.gaussdb to my_result_2020-03-10_155535/run.properties
15:55:35,552 [main] INFO jTPCC : Term-00, created my_result_2020-03-10_155535/data/runInfo.csv for runID 2
15:55:35,552 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2020-03-10_155535/data/result.csv
15:55:35,554 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
15:55:35,554 [main] INFO jTPCC : Term-00, osCollectorInterval=1
15:55:35,554 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null
15:55:35,554 [main] INFO jTPCC : Term-00, osCollectorDevices=net_eth0 blk_sda
15:55:35,679 [main] INFO jTPCC : Term-00,
15:55:36,214 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 156
15:55:36,215 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 59
/<code>
2.10 測試結果
運行5分鐘之後，自動顯示運行結果，如下：
<code>16:00:38,727 [Thread-3] INFO jTPCC : Term-00,
16:00:38,727 [Thread-3] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 132.81
16:00:38,727 [Thread-3] INFO jTPCC : Term-00, Measured tpmTOTAL = 299.97
16:00:38,728 [Thread-3] INFO jTPCC : Term-00, Session Start = 2020-03-10 15:55:36
16:00:38,728 [Thread-3] INFO jTPCC : Term-00, Session End = 2020-03-10 16:00:38
16:00:38,728 [Thread-3] INFO jTPCC : Term-00, Transaction Count = 1510
/<code>
結果也會保存到csv文件，可用來生成相關報表：
<code># tree my_result_2020-03-10_155535
my_result_2020-03-10_155535
├── data
│ ├── blk_sda.csv
│ ├── net_eth0.csv
│ ├── result.csv
│ ├── runInfo.csv
│ └── sys_info.csv
└── run.properties
1 directory, 6 files
#
/<code>
2.11 清理數據
<code># ./runDatabaseDestroy.sh props.gaussdb
# ------------------------------------------------------------
# Loading SQL file ./sql.common/tableDrops.sql
# ------------------------------------------------------------
drop table bmsql_config;
drop table bmsql_new_order;
drop table bmsql_order_line;
drop table bmsql_oorder;
drop table bmsql_history;
drop table bmsql_customer;
drop table bmsql_stock;
drop table bmsql_item;
drop table bmsql_district;
drop table bmsql_warehouse;
drop sequence bmsql_hist_id_seq;
#/<code>
分享到:
閱讀更多 數據和雲智能 的文章
