TCPP-DM搭建完整指南

1.1TPCC是什么？

技术原理：tpccTransaction Processing Performance Council - C）是事务处理性能委员会制定的在线事务处理（OLTP）基准测试标准
模拟真实商业场景：订单处理系统，包含5种事务类型：
New-Order（新订单）：占比45%，核心指标tpmC的来源
Payment（支付）：占比43%
Order-Status（订单查询）：占比4%
Delivery（发货）：占比4%
Stock-Level（库存查询）：占比4%

2.环境准备

2.1jdk安装

Benchmark-SQL是Java编写的测试工具，需要Java运行环境（JRE）
JDK 1.8是兼容性最好的版本，支持后续所有组件
yum install -y wget unzip tar gzip vim net-tools#安装wget、unzip等工具，已安装的可升级

wget https://repo.huaweicloud.com/java/jdk/8u202-b08/jdk-8u202-linux-x64.tar.gz
#下载jdk

解压：
tar -zxvf jdk-8u202-linux-x64.tar.gz -C /home/ #解压
mv /home/jdk1.8.0_202 /home/jdk1.8.0_161 #移动

配置环境变量：

使配置生效：
source ~/.bash_profile
验证安装：java -version

2.2安装Benchmark-SQL

下载Benchmark-SQL
wget https://sourceforge.net/projects/benchmarksql/files/benchmarksql-5.0.zip#官方原始地址


解压：unzip benchmarksql-5.0.zip

解压后查看目录：

2.3安装Apache-Ant

下载Ant:
wget https://mirrors.huaweicloud.com/apache/ant/binaries/apache-ant-1.9.16-bin.tar.gz

解压:
gzip -dc apache-ant-1.9.16-bin.tar.gz | tar xvf -

验证:
/home/apache-ant-1.9.16/bin/ant -version

2.4准备达梦JDBC驱动

2.4.1准备工作

进入benchmarksql目录cd /home/benchmarksql-5.0
创建lib目录和dameng子目录mkdir -p lib/dameng
复制JDBC驱动到benchmarksql目录

2.4.2修改配置

Benchmarksql-5.0源码修改
修改jTPCC.java
cd /home/bnechmarksql-5.0
vi src/client/jTPCC.java
入以下部分，以兼容驱动的数据库类型
else if (iDB.equals(“dameng”))
dbType = DB_UNKNOWN;

修改funcs.sh:
vi run/funcs.sh

加入以下部分，对应到JDBC驱动位置，使其能够正确找到class

dameng)
cp="…/lib/dameng/:…/lib/"

再找到数据库类型检查:
irebird|oracle|postgres)
修改为：
firebird|oracle|postgres|dameng)

Benchmarksql–5.0源码编译
cd /home/benchmarksql-5.0
使用Ant编译
/home/apache-ant-1.9.16/bin/ant

编写配置文件props.dm
一般标准的tpcc测试要求基准数据是100仓，但这里以10仓的数据来模拟，实际情况读者可以根据用户要求进行基准数据装载。
cd /home/benchmarksql-5.0/run

[root@dmtest01 run]# cp props.ora props.dm
[root@dmtest01 run]# vi props.dm
db=dameng
driver=dm.jdbc.driver.DmDriver
conn=jdbc:dm://dmdw ##切换为你的IP地址
user=benchmarksql ##用户名
password=123456789 ##密码
warehouses=10 ##仓库数量
terminals=12 ##并发终端数

//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
//The following five values must add up to 100
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4

3测试

3.1连接DM8建立测试空间

su - dmdba
disqi SYSBDA/Dmdba123!

– 创建测试表空间–CREATE TABLESPACE “BENCHMARKSQL1” DATAFILE ‘tpcc.dbf’ SIZE 1024 CACHE = “NORMAL”;
3.2创建测试用户
–创建测试用户–CREATE USER “BENCHMARKSQL” IDENTIFIED BY “Dmdba123！” DEFAULT TABLESPACE “BENCHMARKSQL1”;

–授予DBA权限–GRANT DBA TO BENCHMARKSQL;

用测试用户登录：
./disql BENCHMARKSQL/Dmdba123!
3.3创建测试表：
create
table BENCHMARKSQL.bmsql_config
(
cfg_name varchar(30) cluster primary key,
cfg_value varchar(50)
);
create
table BENCHMARKSQL.bmsql_warehouse
(
w_id integer not null,
w_ytd decimal(22, 2) ,
w_tax float ,
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) ,
cluster primary key(w_id)
)
STORAGE
(
FILLFACTOR 1
);
create
table BENCHMARKSQL.bmsql_district
(
d_w_id integer not null,
d_id integer not null,
d_ytd decimal(22, 2) ,
d_tax float ,
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) ,
cluster primary key(d_w_id, d_id)
)
STORAGE
(
FILLFACTOR 1
);
create
table BENCHMARKSQL.bmsql_customer
(
c_w_id integer not null ,
c_d_id integer not null ,
c_id integer not null ,
c_discount float ,
c_credit char(2) ,
c_last varchar(16) ,
c_first varchar(16) ,
c_credit_lim float ,
c_balance float ,
c_ytd_payment float ,
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) ,
cluster primary key(c_w_id, c_d_id, c_id)
);
create
table BENCHMARKSQL.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 float ,
h_data varchar(24)
)
storage
(
branch(32, 32),
without counter
);
create
table BENCHMARKSQL.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 float ,
o_all_local float ,
o_entry_d timestamp ,
cluster primary key(o_w_id, o_d_id, o_id)
)
storage
(
without counter
);
create
table BENCHMARKSQL.bmsql_new_order
(
no_w_id integer not null,
no_d_id integer not null,
no_o_id integer not null,
cluster primary key(no_w_id, no_d_id, no_o_id)
)
storage
(
without counter
);
create
table BENCHMARKSQL.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 float ,
ol_supply_w_id integer ,
ol_quantity float ,
ol_dist_info char(24) ,
cluster primary key(ol_w_id, ol_d_id, ol_o_id, ol_number)
)
storage
(
without counter
);
create
table BENCHMARKSQL.bmsql_stock
(
s_w_id integer not null,
s_i_id integer not null,
s_quantity float ,
s_ytd float ,
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) ,
cluster primary key(s_w_id, s_i_id)
);
create
table BENCHMARKSQL.bmsql_item
(
i_id integer not null,
i_name varchar(24) ,
i_price float ,
i_data varchar(50) ,
i_im_id integer ,
cluster primary key(i_id));

3.4装载数据

回到Bnechmarksql目录，执行装载数据脚本：
cd /home/benchmarksql-5.0/run
执行装载数据：
./runLoader.sh props.dm numWarehouses 3（此处做示例，仅装载3个仓数据，可根据需求自行调整）

3.5执行测试：

./runBenchmark.sh props.dm


4.Benchmarksql测试结果验证
TPCC基准测试一致性验证语句，连接到数据库做以上查询，结果全为0行，说明tpcc库是一致的。
– 验证1：仓库余额和地区余额是否一致
(select w_id, w_ytd from benchmarksql.bmsql_warehouse)
except
(select d_w_id, sum(d_ytd) from benchmarksql.bmsql_district group by d_w_id);

– 验证2：订单ID是否连续
(select d_w_id, d_id, D_NEXT_O_ID - 1 from benchmarksql.bmsql_district)
except
(select o_w_id, o_d_id, max(o_id) from benchmarksql.bmsql_oorder group by o_w_id, o_d_id);

– 验证3：新订单表是否有空洞
(select d_w_id, d_id, D_NEXT_O_ID - 1 from benchmarksql.bmsql_district)
except
(select no_w_id, no_d_id, max(no_o_id) from benchmarksql.bmsql_new_order group by no_w_id, no_d_id);

– 验证4：新订单ID连续性检查
(select * from (select (count(no_o_id)-(max(no_o_id)-min(no_o_id)+1)) as diff
from benchmarksql.bmsql_new_order group by no_w_id, no_d_id) where diff != 0);

– 验证5：订单行数统计
(select o_w_id, o_d_id, sum(o_ol_cnt) from benchmarksql.bmsql_oorder group by o_w_id, o_d_id)
except
(select ol_w_id, ol_d_id, count(ol_o_id) from benchmarksql.bmsql_order_line group by ol_w_id, ol_d_id);

– 验证6：地区余额与仓库余额一致性
(select d_w_id, sum(d_ytd) from benchmarksql.bmsql_district group by d_w_id)
except
(select w_id, w_ytd from benchmarksql.bmsql_warehouse);

如图可见，返回无数据，说明tpcc库是一致的，至此，TCPP-DM的搭建流程与测试已经全部完成。