GaussDB T 1.0.2单机环境TPC-C之BenchmarkSQL性能测试

原文链接： https://www.modb.pro/db/22513?03=

1、概述

TPC是一系列事务处理和数据库基准测试的规范。其中TPC-C是针对OLTP的基准测试模型，一方面可以衡量数据库的性能，另一方面可以衡量硬件性价比，也是广泛应用并关注的一种测试模型。TPC-C通过TPM去衡量性价比，即每分钟的执行事务量。

本文介绍TPC-C之BenchmarkSQL对GaussDB T单机数据库进行性能压测。

对于分布式集群下的BenchmarkSQL测试，GaussDB T需要做适配，因为分布式下创建表必须要指定分片键的，默认的脚本是不指定的。

2、 BenchmarkSQL性能测试

2.1 安装JDK

BenchmarkSQL工具要求JDK7以上。检查系统JDK版本：

# java -versionopenjdk 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)#

2.2 安装依赖包

# yum -y install ant

2.3 上传BenchmarkSQL工具包并解压

这里使用benchmarksql-5.0.zip工具包。

# 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总用量 2212drwxr-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.xmldrwxr-xr-x 3 root root   17 5月  26 2016 doc-rwxr-xr-x 1 root root 6376 5月  26 2016 HOW-TO-RUN.txtdrwxr-xr-x 5 root root  129 5月  26 2016 lib-rwxr-xr-x 1 root root 5318 5月  26 2016 README.mddrwxr-xr-x 7 root root 4096 5月  26 2016 rundrwxr-xr-x 6 root root   67 5月  26 2016 src#

2.4 编译BenchmarkSQL

使用ANT编译BenchMark的包，需要用到BenchMark的build包，进入到BenchMark的目录，执行ant进行编译。

# cd /software/benchmark/benchmarksql-5.0/# antBuildfile: /software/benchmark/benchmarksql-5.0/build.xmlinit:    [mkdir] Created dir: /software/benchmark/benchmarksql-5.0/buildcompile:    [javac] Compiling 11 source files to /software/benchmark/benchmarksql-5.0/builddist:    [mkdir] Created dir: /software/benchmark/benchmarksql-5.0/dist      [jar] Building jar: /software/benchmark/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jarBUILD SUCCESSFULTotal time: 4 seconds# ls -l总用量 28drwxr-xr-x 2 root root 4096 3月  10 13:52 build-rwxr-xr-x 1 root root 1130 5月  26 2016 build.xmldrwxr-xr-x 2 root root   34 3月  10 13:52 distdrwxr-xr-x 3 root root   17 5月  26 2016 doc-rwxr-xr-x 1 root root 6376 5月  26 2016 HOW-TO-RUN.txtdrwxr-xr-x 5 root root  129 5月  26 2016 lib-rwxr-xr-x 1 root root 5318 5月  26 2016 README.mddrwxr-xr-x 7 root root 4096 5月  26 2016 rundrwxr-xr-x 6 root root   67 5月  26 2016 src#

2.5 拷贝GaussDB T的JDBC驱动到lib目录

拷贝GaussDB T的JDBC驱动到BenchmarkSQL的lib目录，jdbc驱动在安装包GaussDB_T_1.0.2-CLIENT-JDBC.tar.gz里。

# 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.jardrwxr-xr-x 2 root root     60 5月  26 2016 firebird-rwxr-xr-x 1 root root 489883 5月  26 2016 log4j-1.2.17.jardrwxr-xr-x 2 root root     42 5月  26 2016 oracledrwxr-xr-x 2 root root     44 5月  26 2016 postgres#

2.6 创建测试数据库用户并授权

Zsql连接数据库，创建测试用户并授权：

$ zsql omm/yhadmin_123@192.168.179.123:1888 -qconnected.SQL> create user aps2 identified by aps2_123;Succeed.SQL> grant connect,resource to aps2;Succeed.SQL> exit$

2.7 创建benchmark配置文件

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

# pwd/software/benchmark/benchmarksql-5.0/run# cp props.ora props.gaussdb# vi props.gaussdbdb=oracledriver=com.huawei.gauss.jdbc.ZenithDriverconn=jdbc:zenith:@192.168.179.123:1888user=aps2password=aps2_123warehouses=10loadWorkers=4terminals=10//To run specified transactions per terminal- runMins must equal zerorunTxnsPerTerminal=0//To run for specified minutes- runTxnsPerTerminal must equal zerorunMins=5//Number of total transactions per minutelimitTxnsPerMin=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 100newOrderWeight=45paymentWeight=43orderStatusWeight=4deliveryWeight=4stockLevelWeight=4// Directory name to create for collecting detailed result data.// Comment this out to suppress.resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tSosCollectorScript=./misc/os_collector_linux.pyosCollectorInterval=1//osCollectorSSHAddr=user@dbhostosCollectorDevices=net_eth0 blk_sda#

参数说明：
配置文件中，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脚本，初始化测试数据。

# 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 LoadDatadriver=com.huawei.gauss.jdbc.ZenithDriverconn=jdbc:zenith:@127.0.0.1:1888user=aps2password=***********warehouses=10loadWorkers=4fileLocation (not defined)csvNullValue (not defined - using default 'NULL')Worker 000: Loading ITEMWorker 001: Loading Warehouse      1Worker 002: Loading Warehouse      2Worker 003: Loading Warehouse      3Worker 000: Loading ITEM doneWorker 000: Loading Warehouse      4Worker 002: Loading Warehouse      2 doneWorker 002: Loading Warehouse      5Worker 001: Loading Warehouse      1 doneWorker 001: Loading Warehouse      6Worker 003: Loading Warehouse      3 doneWorker 003: Loading Warehouse      7Worker 000: Loading Warehouse      4 doneWorker 000: Loading Warehouse      8Worker 001: Loading Warehouse      6 doneWorker 001: Loading Warehouse      9Worker 002: Loading Warehouse      5 doneWorker 002: Loading Warehouse     10Worker 003: Loading Warehouse      7 doneWorker 000: Loading Warehouse      8 doneWorker 001: Loading Warehouse      9 doneWorker 002: Loading Warehouse     10 done# ------------------------------------------------------------# Loading SQL file ./sql.common/indexCreates.sql# ------------------------------------------------------------alter table bmsql_warehouse add constraint bmsql_warehouse_pkeyprimary key (w_id);alter table bmsql_district add constraint bmsql_district_pkeyprimary key (d_w_id, d_id);alter table bmsql_customer add constraint bmsql_customer_pkeyprimary key (c_w_id, c_d_id, c_id);create index bmsql_customer_idx1on  bmsql_customer (c_w_id, c_d_id, c_last, c_first);alter table bmsql_oorder add constraint bmsql_oorder_pkeyprimary key (o_w_id, o_d_id, o_id);create unique index bmsql_oorder_idx1on  bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);alter table bmsql_new_order add constraint bmsql_new_order_pkeyprimary key (no_w_id, no_d_id, no_o_id);alter table bmsql_order_line add constraint bmsql_order_line_pkeyprimary key (ol_w_id, ol_d_id, ol_o_id, ol_number);alter table bmsql_stock add constraint bmsql_stock_pkeyprimary key (s_w_id, s_i_id);alter table bmsql_item add constraint bmsql_item_pkeyprimary key (i_id);# ------------------------------------------------------------# Loading SQL file ./sql.common/foreignKeys.sql# ------------------------------------------------------------alter table bmsql_district add constraint d_warehouse_fkeyforeign key (d_w_id)references bmsql_warehouse (w_id);alter table bmsql_customer add constraint c_district_fkeyforeign key (c_w_id, c_d_id)references bmsql_district (d_w_id, d_id);alter table bmsql_history add constraint h_customer_fkeyforeign 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_fkeyforeign key (h_w_id, h_d_id)references bmsql_district (d_w_id, d_id);alter table bmsql_new_order add constraint no_order_fkeyforeign 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_fkeyforeign 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_fkeyforeign 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_fkeyforeign 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_fkeyforeign key (s_w_id)references bmsql_warehouse (w_id);alter table bmsql_stock add constraint s_item_fkeyforeign 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;declaren integer;i integer;dummy integer;beginselect max(hist_id) into n from bmsql_history;i := 0;while i <= n loopselect 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.nextvalcreate trigger bmsql_history_before_insertbefore insert on bmsql_historyfor each rowbeginif :new.hist_id is null thenselect 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.-- ----#

2.9 开始测试

进入benchmarksql的run目录下，运行./runBenchmark.sh props.gaussdb脚本，开始性能测试。

# pwd/software/benchmark/benchmarksql-5.0/run # ./runBenchmark.sh props.gaussdb15: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.015:55:35,444 [main] INFO   jTPCC : Term-00, +-------------------------------------------------+15:55:35,444 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa15:55:35,444 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier15:55:35,448 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck15: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=oracle15:55:35,449 [main] INFO   jTPCC : Term-00, driver=com.huawei.gauss.jdbc.ZenithDriver15:55:35,449 [main] INFO   jTPCC : Term-00, conn=jdbc:zenith:@127.0.0.1:188815:55:35,449 [main] INFO   jTPCC : Term-00, user=aps215:55:35,449 [main] INFO   jTPCC : Term-00, 15:55:35,449 [main] INFO   jTPCC : Term-00, warehouses=1015:55:35,450 [main] INFO   jTPCC : Term-00, terminals=1015:55:35,452 [main] INFO   jTPCC : Term-00, runMins=515:55:35,452 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=30015:55:35,452 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=true15:55:35,452 [main] INFO   jTPCC : Term-00, 15:55:35,453 [main] INFO   jTPCC : Term-00, newOrderWeight=4515:55:35,453 [main] INFO   jTPCC : Term-00, paymentWeight=4315:55:35,453 [main] INFO   jTPCC : Term-00, orderStatusWeight=415:55:35,453 [main] INFO   jTPCC : Term-00, deliveryWeight=415:55:35,453 [main] INFO   jTPCC : Term-00, stockLevelWeight=415: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%tS15:55:35,454 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py15: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.properties15:55:35,552 [main] INFO   jTPCC : Term-00, created my_result_2020-03-10_155535/data/runInfo.csv for runID 215:55:35,552 [main] INFO   jTPCC : Term-00, writing per transaction results to my_result_2020-03-10_155535/data/result.csv15:55:35,554 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py15:55:35,554 [main] INFO   jTPCC : Term-00, osCollectorInterval=115:55:35,554 [main] INFO   jTPCC : Term-00, osCollectorSSHAddr=null15:55:35,554 [main] INFO   jTPCC : Term-00, osCollectorDevices=net_eth0 blk_sda15:55:35,679 [main] INFO   jTPCC : Term-00,15:55:36,214 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 15615:55:36,215 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    59

2.10 测试结果

运行5分钟之后，自动显示运行结果，如下：

16:00:38,727 [Thread-3] INFO   jTPCC : Term-00, 16:00:38,727 [Thread-3] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 132.8116:00:38,727 [Thread-3] INFO   jTPCC : Term-00, Measured tpmTOTAL = 299.9716:00:38,728 [Thread-3] INFO   jTPCC : Term-00, Session Start     = 2020-03-10 15:55:3616:00:38,728 [Thread-3] INFO   jTPCC : Term-00, Session End       = 2020-03-10 16:00:3816:00:38,728 [Thread-3] INFO   jTPCC : Term-00, Transaction Count = 1510

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

# tree my_result_2020-03-10_155535my_result_2020-03-10_155535├── data│   ├── blk_sda.csv│   ├── net_eth0.csv│   ├── result.csv│   ├── runInfo.csv│   └── sys_info.csv└── run.properties1 directory, 6 files#

2.11 清理数据

# ./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;#