GaussDB T OLTP数据库tpc-c压测之benchmarksql

上手华为GaussDB T数据库后，想做做性能测试，首先想到的当然是tpc家出的benchmarksql，整个过程比较顺利，由于机器为虚拟机，仅2c2g，测试结果仅供参考。

1、下载并安装benchmarksql

下载地址  : benchmarksql-5.0.zip  https://www.modb.pro/download/3530

# 安装ant
yum install ant
# 解压
unzip benchmarksql-5.0.zip 

# 运行ant安装
[root@pr7 benchmarksql-5.0]# pwd
/opt/software/benchmarksql-5.0
[root@pr7 benchmarksql-5.0]# ant
Buildfile: /opt/software/benchmarksql-5.0/build.xml

init:
    [mkdir] Created dir: /opt/software/benchmarksql-5.0/build

compile:
    [javac] Compiling 11 source files to /opt/software/benchmarksql-5.0/build

dist:
    [mkdir] Created dir: /opt/software/benchmarksql-5.0/dist
      [jar] Building jar: /opt/software/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar

BUILD SUCCESSFUL
Total time: 2 seconds
[root@pr7 benchmarksql-5.0]# pwd
/opt/software/benchmarksql-5.0
[root@pr7 benchmarksql-5.0]# ls -l
total 28
drwxr-xr-x 2 root root 4096 Dec 26 11:07 build
-rwxr-xr-x 1 root root 1130 May 26  2016 build.xml
drwxr-xr-x 2 root root   33 Dec 26 11:07 dist
drwxr-xr-x 3 root root   16 May 26  2016 doc
-rwxr-xr-x 1 root root 6376 May 26  2016 HOW-TO-RUN.txt
drwxr-xr-x 5 root root  123 May 26  2016 lib
-rwxr-xr-x 1 root root 5318 May 26  2016 README.md
drwxr-xr-x 7 root root 4096 May 26  2016 run
drwxr-xr-x 6 root root   63 May 26  2016 src

2、拷贝gaussdb T jdbc驱动到lib目录下

jdbc驱动在安装包中有，也可以通过以下链接下载：
华为GaussDB 100 JDBC驱动 https://www.modb.pro/download/3245
然后将驱动拷贝到benchmarksql的lib目录下。

[root@pr7 lib]# pwd
/opt/software/benchmarksql-5.0/lib
[root@pr7 lib]# ls -lrt
total 1240
drwxr-xr-x 2 root root     43 May 26  2016 postgres
drwxr-xr-x 2 root root     40 May 26  2016 oracle
-rwxr-xr-x 1 root root 489883 May 26  2016 log4j-1.2.17.jar
drwxr-xr-x 2 root root     58 May 26  2016 firebird
-rwxr-xr-x 1 root root 346729 May 26  2016 apache-log4j-extras-1.1.jar
-r-x------ 1 root root 428471 Jul 29 19:16 com.huawei.gauss.jdbc.ZenithDriver-GaussDB_100_1.0.0.B019.jar

3、创建用户并赋权

SQL> create user steven identified by modb123$;

Succeed.

SQL> grant connect,resource to steven;

Succeed.

4、编辑props配置文件

在benchmarksql的run目录下编辑测试的配置文件props.gauss。

[root@pr7 run]# pwd
/opt/software/benchmarksql-5.0/run

[root@pr7 run]# vi props.gauss 
db=oracle
driver=com.huawei.gauss.jdbc.ZenithDriver
conn=jdbc:zenith:@127.0.0.1:1888
user=steven
password=modb123$

warehouses=10
loadWorkers=10

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=0

//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

db直接填oracle即可，驱动driver为com.huawei.gauss.jdbc.ZenithDriver，连接串conn为

jdbc:zenith:@127.0.0.1:1888，用户密码为steven/modb123$。

5、初始化测试数据

直接运行./runDatabaseBuild.sh props.gauss脚本初始化测试数据。

[root@pr7 run]# ./runDatabaseBuild.sh props.gauss 
# ------------------------------------------------------------
# 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=steven
password=***********
warehouses=10
loadWorkers=10
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 004: Loading Warehouse      4
Worker 005: Loading Warehouse      5
Worker 006: Loading Warehouse      6
Worker 007: Loading Warehouse      7
Worker 008: Loading Warehouse      8
Worker 009: Loading Warehouse      9
Worker 000: Loading ITEM done
Worker 000: Loading Warehouse     10
Worker 004: Loading Warehouse      4 done
Worker 008: Loading Warehouse      8 done
Worker 006: Loading Warehouse      6 done
Worker 009: Loading Warehouse      9 done
Worker 001: Loading Warehouse      1 done
Worker 007: Loading Warehouse      7 done
Worker 002: Loading Warehouse      2 done
Worker 003: Loading Warehouse      3 done
Worker 005: Loading Warehouse      5 done
Worker 000: 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
-- ----
-- ----
--      This is an extra column not present in the TPC-C
--      specs. It is useful for replication systems like
--      Bucardo and Slony-I, which like to have a primary
--      key on a table. It is an auto-increment or serial
--      column type. The definition below is compatible
--      with 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.
-- ----
···

6、开始测试

运行./runBenchmark.sh props.gauss脚本开始测试

[root@pr7 run]# ./runBenchmark.sh props.gauss 
11:22:02,614 [main] INFO   jTPCC : Term-00, 
11:22:02,619 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
11:22:02,620 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0
11:22:02,620 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
11:22:02,620 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa
11:22:02,620 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier
11:22:02,623 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck
11:22:02,623 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
11:22:02,624 [main] INFO   jTPCC : Term-00, 
11:22:02,624 [main] INFO   jTPCC : Term-00, db=oracle
11:22:02,624 [main] INFO   jTPCC : Term-00, driver=com.huawei.gauss.jdbc.ZenithDriver
11:22:02,624 [main] INFO   jTPCC : Term-00, conn=jdbc:zenith:@127.0.0.1:1888
11:22:02,624 [main] INFO   jTPCC : Term-00, user=steven
11:22:02,624 [main] INFO   jTPCC : Term-00, 
11:22:02,624 [main] INFO   jTPCC : Term-00, warehouses=10
11:22:02,624 [main] INFO   jTPCC : Term-00, terminals=10
11:22:02,627 [main] INFO   jTPCC : Term-00, runMins=5
11:22:02,627 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=0
11:22:02,627 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=true11:22:02,627 [main] INFO   jTPCC : Term-00, 
11:22:02,627 [main] INFO   jTPCC : Term-00, newOrderWeight=45
11:22:02,627 [main] INFO   jTPCC : Term-00, paymentWeight=43
11:22:02,628 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
11:22:02,628 [main] INFO   jTPCC : Term-00, deliveryWeight=4
11:22:02,628 [main] INFO   jTPCC : Term-00, stockLevelWeight=4
11:22:02,628 [main] INFO   jTPCC : Term-00, 
11:22:02,628 [main] INFO   jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
11:22:02,628 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
11:22:02,628 [main] INFO   jTPCC : Term-00, 
11:22:02,659 [main] INFO   jTPCC : Term-00, copied props.gauss to my_result_2019-12-26_112202/run.properties
11:22:02,659 [main] INFO   jTPCC : Term-00, created my_result_2019-12-26_112202/data/runInfo.csv for runID 1
11:22:02,660 [main] INFO   jTPCC : Term-00, writing per transaction results to my_result_2019-12-26_112202/data/result.csv
11:22:02,661 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
11:22:02,661 [main] INFO   jTPCC : Term-00, osCollectorInterval=1
11:22:02,662 [main] INFO   jTPCC : Term-00, osCollectorSSHAddr=null
11:22:02,662 [main] INFO   jTPCC : Term-00, osCollectorDevices=null
11:22:02,845 [main] INFO   jTPCC : Term-00,
11:22:03,441 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 151
11:22:03,441 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    225
11:22:03,441 [main] INFO   jTPCC : Term-00, 
Term-00, Running Average tpmTOTAL: 13792.17    Current tpmTOTAL: 311508    Memory Usage: 16MB / 37MB          
Term-00, Running Average tpmTOTAL: 13854.32    Current tpmTOTAL: 323400    Memory Usage: 24MB / 37MB          
Term-00, Running Average tpmTOTAL: 13862.95    Current tpmTOTAL: 337440    Memory Usage: 22MB / 37MB          
Term-00, Running Average tpmTOTAL: 13844.20    Current tpmTOTAL: 461460    Memory Usage: 25MB / 37MB

运行过程中vmstat结果如下：

[root@pr7 ~]# vmstat  2 20
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1 10 209880  67776      0 1114824    0    1    45    88   32  203  1  0 99  0  0
11  1 211524  74604      0 1108640    0  824 26518  6166 6786 21321 44 32  7 18  0
 6  4 211476  77896      0 1104372  752  336 18102 26550 7736 21858 37 27 12 24  0
 5  1 214376  73572      0 1109848    0 1450 17566 12875 9583 28175 35 32 13 21  0
 3  1 217284  75756      0 1109448    0 1454 21836 24145 7126 21661 45 28 10 16  0
 3  4 217284  73448      0 1113812    0    0 13826 15114 10564 31377 25 23 17 36  0
 0  1 218452  74764      0 1114212    0  584 22306 23471 8824 27445 52 31  8  9  0
 5  7 231640  70220      0 1118532    0 6594 12618 22509 12664 33225 24 27 19 30  0
 9  0 231636  65788      0 1125964   16    0 24662  3210 7268 23697 49 31  6 14  0
 7  2 231636  64568      0 1127816    0    0  5744 34397 10842 29768 24 22 16 38  0

7、测试结果

运行5分钟之后，自动显示报告结果：

11:27:03,837 [Thread-11] INFO   jTPCC : Term-00, 
11:27:03,842 [Thread-11] INFO   jTPCC : Term-00, 
11:27:03,842 [Thread-11] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 6188.06
11:27:03,842 [Thread-11] INFO   jTPCC : Term-00, Measured tpmTOTAL = 13835.91
11:27:03,842 [Thread-11] INFO   jTPCC : Term-00, Session Start     = 2019-12-26 11:22:03
11:27:03,842 [Thread-11] INFO   jTPCC : Term-00, Session End       = 2019-12-26 11:27:03
11:27:03,842 [Thread-11] INFO   jTPCC : Term-00, Transaction Count = 69236

另外结果也会保存到csv文件，可用来生成相关报表：

[root@pr7 run]# tree my_result_2019-12-26_112202/my_result_2019-12-26_112202/
├── data
│   ├── result.csv
│   ├── runInfo.csv
│   └── sys_info.csv
└── run.properties

1 directory, 4 files

8、清理数据

[root@pr7 run]# ./runDatabaseDestroy.sh props.gauss

转自墨天轮