BMSQL 5.0测试国产数据库TPCC使用方法
以前也使用过Benchmarksql4.0做个测试，国庆用 Benchmarksql5.0也试了一下，BM5.0 和4.1 不同的地方在于他设置了数据库类型。如果没有对应的数据库类型就会报不支持索引需要修改java 包和SH 脚本增加一个DM 的类型
一、 准备环境
1. 已经启动的一个DM 数据库
2. JAVA 1.7 以上的环境
3. 下载解压BMSQL5.0
4. 将DMJDBC-1.8 放到bmsqsl 的lib 下
二、BMSQL 脚本新修改
1. 复制一个props.ora 该名为props.dm 并修改内容
# cp props.ora props.dm
# vi props.dm
db=dm
driver=dm.jdbc.driver.DmDriver
conn=jdbc:dm://LOCALHOST:5236
user=BMSQL
password=123456789
2. 进入/opt/benchmarksql-5.0/run/sql.common 修改tableCreate.sql
create table benchmarksql.bmsql_config (
cfg_name varchar(30) primary key,
cfg_value varchar(50)
);
create table benchmarksql.bmsql_warehouse (
w_id int not null,
w_ytd float,
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 2,without counter);
create table benchmarksql.bmsql_district (
d_w_id int not null,
d_id int not null,
d_ytd float,
d_tax float,
d_next_o_id int,
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 2,without counter);
create table benchmarksql.bmsql_customer (
c_w_id int not null,
c_d_id int not null,
c_id int 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 int,
c_delivery_cnt int,
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 sequence benchmarksql.bmsql_hist_id_seq;
create table benchmarksql.bmsql_history (
hist_id int default benchmarksql.bmsql_hist_id_seq.NEXTVAL,
h_c_id int,
h_c_d_id int,
h_c_w_id int,
h_d_id int,
h_w_id int,
h_date timestamp,
h_amount float,
h_data varchar(24)
)storage(branch(32,32),without counter);
create table benchmarksql.bmsql_oorder (
o_w_id int not null,
o_d_id int not null,
o_id int not null,
o_c_id int,
o_carrier_id int,
o_ol_cnt float,
o_all_local float,
o_entry_d timestamp,
cluster primary key(o_w_id,o_d_id,o_id)
);
create table benchmarksql.bmsql_new_order (
no_w_id int not null,
no_d_id int not null,
no_o_id int not null,
cluster primary key(no_w_id,no_d_id,no_o_id)
);
create table benchmarksql.bmsql_order_line (
ol_w_id int not null,
ol_d_id int not null,
ol_o_id int not null,
ol_number int not null,
ol_i_id int not null,
ol_delivery_d timestamp,
ol_amount float,
ol_supply_w_id int,
ol_quantity float,
ol_dist_info char(24),
cluster primary key(ol_w_id,ol_d_id,ol_o_id,ol_number)
);
create table benchmarksql.bmsql_stock (
s_w_id int not null,
s_i_id int not null,
s_quantity float,
s_ytd float,
s_order_cnt int,
s_remote_cnt int,
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_i_id,s_w_id)
);
create table benchmarksql.bmsql_item (
i_id int not null,
i_name varchar(24),
i_price float,
i_data varchar(50),
i_im_id int,
cluster primary key(i_id)
);
注1 ：DECIMAL 类型都改为FLOAT 类型
注2 ：去掉外键
注3 ：主键强制聚集
修改indexCreate.sql 脚本
create index ndx_customer_name
on benchmarksql.customer (c_w_id, c_d_id, c_last, c_first);
目前只需要这一个索引
三、 修改JAVA 包和sh 脚本添加DM7
# cd /opt/benchmarksql-5.0/src/client/jTPCC.java
# vi 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("dm7"))
dbType = DB_DM;
else
{
log.error("unknown database type '" + iDB + "'");
return;
}
if(Integer.parseInt(limPerMin) !=0){
limPerMin_Terminal = Integer.parseInt(limPerMin)/Integer.parseInt(iTerminals);
}
else{
limPerMin_Terminal = -1;
}
标红处为手动添加的数据库类型
#cd /opt/benchmarksql-5.0/run
#vi funcs.sh
function setCP()
{
case "$(getProp db)" in
firebird)
cp="../lib/firebird/*:../lib/*"
;;
oracle)
cp="../lib/oracle/*"
if [ ! -z "${ORACLE_HOME}" -a -d ${ORACLE_HOME}/lib ] ; then
cp="${cp}:${ORACLE_HOME}/lib/*"
fi
cp="${cp}:../lib/*"
;;
postgres)
cp="../lib/postgres/*:../lib/*"
;;
dm7)
cp="../lib/dm7/*:../lib/*"
;;
esac
myCP=".:${cp}:../dist/*"
export myCP
}
省略。。。。。。。。。。。
case "$(getProp db)" in
firebird|oracle|postgres| dm7)
;;
"") echo "ERROR: missing db= config option in ${PROPS}" >&2
exit 1
;;
*) echo "ERROR: unsupported database type 'db=$(getProp db)' in ${PROPS}" >&2
exit 1
;;
esac
标红处为添加部分
四、 执行数据创建和装载
1. 创建表和索引
#./runSql props.dm sql.common/tableCreate.sql
#./runSql props.dm sql.common/indexCreate.sql
2. 装载数据
#./runLoader.sh props.dm warehourse 1
五、 运行TPC-C
./runBenchmark.sh props.dm
执行结束
