摘要

本文介绍使用BenchMarkSQL 5.0测试GBase 8s数据库的方法。

测试环境

测试机
172.16.17.127 ： CentOS 7.3 (64 Bit)
数据库
GBase 8s V8.8
使用说明
本说明手册是在CentOS 7.3 (64 Bit)，jdk-1.8环境下编译过的，如果直接使用该文件进行测试，请直接跳到“使用benchmarksql5.0进行测试”。

软件下载与安装

安装benchmarkSQL5.0
下载地址：https://sourceforge.net/projects/benchmarksql/
安装：直接解压，假设解压路径 /data/benchmarksql-5.0

安装jdk1.8
yum install jdk-7u79-linux-x64.rpm

安装Apache ant
用于编译benchmarkSQL, 5.0版本是需要编译的。

下载地址：https://www.baidu.com/#ie=utf-8&f=8&rsv_bp=1&rsv_idx=1&tn=baidu&wd=txkbps&oq=rxpktsps&rsv_pq=fda53d7e00006c93&rsv_t=95daqeDHwjUCPg%2F53yzOfJJcalfg0MQo0jTBW3kJTzUaRK2hjG0RqTOhbpI&rqlang=cn&rsv_enter=0&inputT=1380&rsv_n=2&rsv_sug3=12&rsv_sug4=1380&rsv_sug=2
也可以使用
yum install ant
安装：直接解压apache-ant-1.9.1-bin.tar.gz包即可。编译benchmarksql时在环境变量PATH中添加上解压包的bin路径即可。

增加GBase数据库支持

由于benchmarksql5.0不支持gbase，所以需要在benchmarksql的lib目录下创建gbase目录，并将ifxjdbc.jar包放入该目录。
然后修改benchmarksql5.0源码，增加对gbase的支持。

修改funcs.sh脚本

vim run/funcs.sh

…
function setCP()
{
…
postgres)
cp="…/lib/postgres/:…/lib/"
;;
gbase)
cp="…/lib/gbase/:…/lib/"
;;
…
}
…
case “$(getProp db)” in
firebird|oracle|postgres|gbase)
…

修改jTPCC.java

$ vim src/client/jTPCC.java
…
if (iDB.equals(“firebird”))
dbType = DB_FIREBIRD;
else if (iDB.equals(“oracle”))
dbType = DB_ORACLE;
else if (iDB.equals(“postgres”))
dbType = DB_POSTGRES;
else if (iDB.equals(“gbase”))
dbType = DB_UNKNOWN;
else
{
log.error(“unknown database type '” + iDB + “’”);
return;
}

修改jTPCCConnection.java

$ vim src/client/jTPCCConnection.java
…
// PreparedStatements for STOCK_LEVEL
switch (dbType)
{
case jTPCCConfig.DB_POSTGRES:
case jTPCCConfig.DB_UNKNOWN:
stmtStockLevelSelectLow = dbConn.prepareStatement(
“SELECT count(*) AS low_stock FROM (” +
" SELECT s_w_id, s_i_id, s_quantity " +
" FROM bmsql_stock " +
" WHERE s_w_id = ? AND s_quantity < ? 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 = ? AND d_id = ? " +
" ) " +
" ) AS L");
break;

default:
stmtStockLevelSelectLow = dbConn.prepareStatement(
    "SELECT count(*) AS low_stock FROM (" +
    "    SELECT s_w_id, s_i_id, s_quantity " +
    "        FROM bmsql_stock " +
    "        WHERE s_w_id = ? AND s_quantity < ? 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 = ? AND d_id = ? " +
    "        ) " +
    "    ) AS aliasA");  /* mysql的子查询必须添加一个别名 */
break;

}
…

编译benchmarksql5.0

$ cd benchmarksql5.0
$ ant
如果没有ant，需要安装，如果服务器上有ant的安装包，执行执行
$ yum install ant
即可，如果没有，则需要下载ant安装包。

使用benchmarksql5.0进行测试

以下步骤以gbase数据库为例。

1）在master上创建测试用户和数据库，与props.gbase下面保持一致即可，库需使用日志模式。

2）回到测试机的benchmarksql目录，根据pg的例子配置文件prop.gbase
$ cp props.pg props.gbase
$ vi props.gbase
db=gbase
driver=com.gbasedbt.jdbc.IfxDriver
conn=jdbc:gbasedbt-sqli://172.16.17.127:19088/testdb:gbasedbtserver=gbaseserver;ifx_lock_mode_wait=1;ifx_lock_mode_wait=60;
user=gbasedbt
password=gbasedbt

warehouses=1 //测试因子，本次取值1，100，500
loadWorkers=4

terminals=1 //测试因子，本次取值1,10,30,100,300,500,1000
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=10
//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=false

//The following five values must add up to 100
//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec
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

osCollectorDevices=net_ens33 blk_vda blk_vdb //这里的块设备（如vda、vdb）应该在 /sys/block/ 目录下存在，需要改成本地数据目录所在的块名称

修改run/sql.common下的sql语句，修改timestamp字段为datetime year to second，修改varchar（500）为char（500）,主键和外键创建语句也需要修改。

建立测试库，并加载数据

$ ./runDatabaseBuild.sh props.gbase
4）备份数据目录，这样以后测试时就不需要删除表再重新加载数据了。
5）跑测试，并生成结果
$ ./runBenchmark.sh props.gbase
6）多次测试时，为了测试的准确性，最好删除当前数据目录，然后从4）中备份的数据目录中拷贝一份用于新的测试。或者执行以下步骤，清空数据库，再重建数据库（这样比较耗时间，500个warehouses的库可能需要4个小时以上，因此不推荐）。
执行实例如下所示：
[gbasedbt@cos7 run]$ ./runDatabaseDestroy.sh props.gbase

drop table bmsql_config;
The specified table (bmsql_config) is not in the database.
drop table bmsql_new_order;
The specified table (bmsql_new_order) is not in the database.
drop table bmsql_order_line;
The specified table (bmsql_order_line) is not in the database.
drop table bmsql_oorder;
The specified table (bmsql_oorder) is not in the database.
drop table bmsql_history;
The specified table (bmsql_history) is not in the database.
drop table bmsql_customer;
The specified table (bmsql_customer) is not in the database.
drop table bmsql_stock;
The specified table (bmsql_stock) is not in the database.
drop table bmsql_item;
The specified table (bmsql_item) is not in the database.
drop table bmsql_district;
The specified table (bmsql_district) is not in the database.
drop table bmsql_warehouse;
The specified table (bmsql_warehouse) is not in the database.
drop sequence bmsql_hist_id_seq;
The specified sequence object (bmsql_hist_id_seq) is not in the database.

[gbasedbt@cos7 run]$ ./runDatabaseBuild.sh props.gbase

create table bmsql_config (
cfg_name varchar(30),
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 datetime year to second,
c_middle char(2),
c_data char(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 datetime year to second,
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 datetime year to second
);
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 datetime year to second,
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

即可开始Benchmark测试。

生成html以及图形汇总

1） 安装R语言
下载安装R需要依赖的rpm包，本次缺少2个
ftp://fr2.rpmfind.net/linux/centos/6.8/os/x86_64/Packages/texinfo-tex-4.13a-8.el6.x86_64.rpm
http://mirror.ox.ac.uk/sites/mirror.centos.org/6/os/x86_64/Packages/libjpeg-turbo-1.2.1-3.el6_5.x86_64.rpm

安装
yum install texinfo-tex-4.13a-8.el6.x86_64.rpm
yum install libjpeg-turbo-1.2.1-3.el6_5.x86_64.rpm
yum install R

2） 生成html汇总结果（需在图形界面下完成）
$ ./ generateGraphs.sh my_result_2020-04-26_233807（runBenchmark.sh测试结果的路径,测试后自动生成）
$ ./generateReport.sh my_result_2020-04-26_233807（runBenchmark.sh测试结果的路径）
会产生一个report.html