DM8 TPCC性能测试

测试步骤

1 初始化数据库

在已经安装好数据库的前提下初始化示例
大小32k、簇大小32k的时候效果较好，其他参数采用默认值。
./dminit path=/data PAGE_SIZE=32 EXTENT_SIZE=32

2 启动服务

性能调优或短时间测试可以将数据库服务启动到前台，方便观察检查点等执行情况。长时间的稳定性测试要将服务启动到后台。
./dmserver /data/DAMENG/dm.ini

3 创建用户及表结构

创建用户、表空间及表结构、索引等，用DM8的JDBC驱动，当数据库为安全版时需要修改参数，否则会报SSL连接错误，SP_SET_PARA_VALUE(2,’ENABLE_ENCRYPT’,0);
props.dm脚本内容如下：
driver=dm.jdbc.driver.DmDriver
conn=jdbc:dm://192.168.0.101:5236
user=SYSDBA
password=SYSDBA
warehouses=100
terminals=100
runMins=10
runTxnsPerTerminal=0
limitTxnsPerMin=0
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
warehouses=100表示待测的仓库数为100个，terminals=100表示终端数为100个，runMins=10表示测试时间为10分钟。
– sqlTableCreates_DM脚本内容如下：
CREATE TABLESPACE BENCHMARKSQL1 DATAFILE ‘BENCHMARKSQL1.dbf’ SIZE 10000;
CREATE USER “BENCHMARKSQL” IDENTIFIED BY “123456789” DEFAULT TABLESPACE “BENCHMARKSQL1”;
GRANT DBA TO BENCHMARKSQL;

alter tablespace “ROLL” resize datafile ‘ROLL.DBF’ to 50000;
alter database resize logfile ‘DAMENG01.log’ to 30000;
alter database resize logfile ‘DAMENG02.log’ to 30000;

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 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 1);

create table BENCHMARKSQL.bmsql_district (
d_w_id integer not null,
d_id integer not null,
d_ytd float,
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)
);

4 装载数据
运行以下命令装载100个仓库数据。
./runLoader.sh props.dm numWarehouses 100

5 创建索引和序列

调用以下脚本创建索引和序列，或者在管理工具中执行。
create index ndx_customer_name on BENCHMARKSQL.bmsql_customer (c_w_id, c_d_id, c_last, c_first);
create or replace procedure BENCHMARKSQL.createsequence
as
n int;
stmt1 varchar(200);
begin
select count(*)+1 into n from BMSQL_history;
if(n != 1) then
select max(hist_id) + 1 into n from BMSQL_history;
end if;
PRINT n;
stmt1:=‘create sequence hist_id_seq start with ‘||n||’ MAXVALUE 9223372036854775807 CACHE 50000;’;
EXECUTE IMMEDIATE stmt1;
end;

call BENCHMARKSQL.createsequence;
alter table BENCHMARKSQL.bmsql_history modify hist_id integer default (BENCHMARKSQL.hist_id_seq.nextval);

6 修改dm.ini参数

#memory pool and buffer
MAX_OS_MEMORY = 100
MEMORY_POOL = 500
BUFFER = 52000
BUFFER_POOLS = 197
FAST_POOL_PAGES = 99999
FAST_ROLL_PAGES = 3000
DICT_BUF_SIZE = 50
RECYCLE = 8
VM_POOL_SIZE = 512
VM_POOL_TARGET = 32768
SESS_POOL_SIZE = 1024
SESS_POOL_TARGET = 32768
N_MEM_POOLS = 100
#query
ADAPTIVE_NPLN_FLAG = 0
HASH_PLL_OPT_FLAG = 2 #影响较大
REFED_EXISTS_OPT_FLAG = 0
PARTIAL_JOIN_EVALUATION_FLAG = 1
USE_FK_REMOVE_TABLES_FLAG = 0
SUBQ_EXP_CVT_FLAG = 0
REFED_SUBQ_CROSS_FLAG = 0
VIEW_FILTER_MERGING = 2
UPD_DEL_OPT = 2
LIKE_OPT_FLAG = 7
GROUP_OPT_FLAG = 0
HAGR_DISTINCT_OPT_FLAG = 0
CASE_WHEN_CVT_IFUN = 5
#database
MAX_SESSIONS = 10000
MAX_SESSION_STATEMENT = 20000
FAST_LOGIN = 1
BDTA_SIZE = 1000
JOIN_HASH_SIZE = 5000
CACHE_POOL_SIZE = 100
PHC_MODE_ENFORCE = 11
ENABLE_IN_VALUE_LIST_OPT = 2
ENHANCED_BEXP_TRANS_GEN = 0
ENABLE_SPACELIMIT_CHECK = 0
FAST_RELEASE_SLOCK = 0
SESS_CHECK_INTERVAL = 30
NOWAIT_WHEN_UNIQUE_CONFLICT = 1
MSG_COMPRESS_TYPE = 0
COMM_VALIDATE = 0
COMM_TRACE = 0
OPTIMIZER_AGGR_GROUPBY_ELIM = 1
ENABLE_IN_VALUE_LIST_OPT = 1
ENHANCED_BEXP_TRANS_GEN = 3
FIRST_ROWS = 16
BTR_SPLIT_MODE = 1
DECIMAL_FIX_STORAGE = 1
ENABLE_HUGE_SECIND = 0
RS_PRE_FETCH=0
ENABLE_HASH_JOIN = 0
#monitor
ENABLE_MONITOR = 0
MONITOR_TIME = 0
ENABLE_FREQROOTS = 1 #lxj
#checkpoint
CKPT_RLOG_SIZE = 0
CKPT_DIRTY_PAGES = 0
CKPT_INTERVAL = 1800
CKPT_FLUSH_RATE = 50.00
CKPT_FLUSH_PAGES = 1000
CKPT_WAIT_PAGES = 512
FORCE_FLUSH_PAGES = 0
#transaction
UNDO_EXTENT_NUM = 2
UNDO_RETENTION = 0.2
PARALLEL_PURGE_FLAG = 1
TRX_DICT_LOCK_NUM = 64 #LXJ
PURGE_WAIT_TIME=0
MSG_COMPRESS_TYPE =2
PSEG_RECV = 1
#thread
WORK_THRD_STACK_SIZE = 1024

WORKER_CPU_PERCENT = 100
#IO
DIRECT_IO = 1
IO_THR_GROUPS = 32
#important
VM_MEM_HEAP = 1 #长时间运行，需要改为0
FAST_COMMIT = 99 #影响较大
TRX_VIEW_MODE = 1
PURGE_DEL_OPT = 2
HASH_ACTIVE_TRX_VIEW=0
#MAX_TRX
MAX_CONCURRENT_TRX = 72 #180 #限流144
CONCURRENT_TRX_MODE = 0
CONCURRENT_DELAY = 12
#RLOG
RLOG_BUF_SIZE = 512
RLOG_POOL_SIZE = 128
RLOG_PARALLEL_ENABLE = 1 #影响较大
RLOG_SAFE_SPACE = 0
RLOG_CHECK_SPACE = 1
#redo redos
REDO_PWR_OPT = 1
RLOG_RESERVE_SIZE = 0
#主备
#REDOS_PRE_LOAD = 1
#REDOS_ENABLE_SELECT = 0
#REDOS_PARALLEL_NUM = 23
#RLOG_RESERVE_THRESHOLD = 512
#REDOS_BUF_SIZE = 1024
#REDOS_BUF_NUM = 1000
ENABLE_ENCRYPT=0
FAST_RW_LOCK = 2 # from 1
WORKER_THREADS = 4 # 64
TASK_THREADS = 4 # 16

7 启动数据库服务

调整完参数后启动数据库前，建议拷贝一份数据作为冷备份，以备后期重复使用，长时间稳定性测试建议后台启动。
./dmserver /mnt/data/DAMENG/dm.ini

8 扩REDO和UNDO日志
扩大REDO、UNDO日志空间。(根据现场情况扩)
alter tablespace “ROLL” resize datafile ‘ROLL.DBF’ to 50000
alter database resize logfile ‘DAMENG01.log’ to 50000
alter database resize logfile ‘DAMENG02.log’ to 50000

9 数据预加载

将热点数据预加载到内存中。
–测试前执行如下SQL：
–item表，8K的页，需要占用1300页
SP_SET_TAB_FAST_POOL_FLAG(‘BENCHMARKSQL’, ‘BMSQL_ITEM’, 1);
SP_SET_TAB_FAST_POOL_FLAG(‘BENCHMARKSQL’, ‘BMSQL_WAREHOUSE’, 1);
SP_SET_TAB_FAST_POOL_FLAG(‘BENCHMARKSQL’,‘BMSQL_DISTRICT’, 1);

select count() from “BENCHMARKSQL”.“BMSQL_CUSTOMER” union all
select count() from “BENCHMARKSQL”.“BMSQL_DISTRICT” union all
select count() from “BENCHMARKSQL”.“BMSQL_ITEM” union all
select count() from “BENCHMARKSQL”.“BMSQL_NEW_ORDER” union all
select count() from “BENCHMARKSQL”.“BMSQL_OORDER” union all
select count() from “BENCHMARKSQL”.“BMSQL_ORDER_LINE” union all
select count() from “BENCHMARKSQL”.“BMSQL_STOCK” union all
select count() from “BENCHMARKSQL”.“BMSQL_WAREHOUSE” union all
select count(*) from “BENCHMARKSQL”.“BMSQL_HISTORY” union all
select count(“C_PAYMENT_CNT”) from “BENCHMARKSQL”.“BMSQL_CUSTOMER”;
待执行完毕后 可进行测试。

10 执行测试

完成以上步骤之后就可以开始进行测试了
–执行命令如下：
./runBenchmark.sh props.dm