DM8 TPCC性能测试
1.初始化数据库
cd /home/dmdba/dmdbms/bin
./dminit path=/dm8/data
2.启动DM8服务器
./dmserver /dm8/data/DAMENG/dm.ini
3.创建用户BENCHMARKSQL及其表空间
连接SQL, cd /home/dmdba/dmdbms/bin执行./disql
create user benchmarksql identified by 123456789;
grant dba to benchmarksql;
4.创建表及数据
create table benchmarksql.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.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.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.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.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.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.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.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.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)
);
commit;
5装载数据
驱动使用被压测数据库驱动，放在/dm8/benchmarksql-4.1.1/lib下
本身驱动在数据库/drivers/jdbc下面,可复制过去.
/home/dmdba/dmdbms/drivers/jdbc
在/dm8/benchmarksql-4.1.1/run下编辑props.dm脚本
props.dm脚本内容如下：
driver=dm.jdbc.driver.DmDriver
conn=jdbc:dm://127.0.0.1:5236
user= benchmarksql
password=123456789
warehouses=10
loadWorkers=10
terminals=10 （终端数和CPU个数差不多时，tpmC性能比较高）
runTxnsPerTerminal=0
runMins=5
limitTxnsPerMin=0
terminalWarehouseFixed=true
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
注：
warehouses=10表示待测的仓库数为10个，
terminals=10表示终端数为10个，
runMins=5表示测试时间为5分钟，
执行如下命令：
cd /dm8/benchmarksql-4.1.1/run
./runLoader.sh props.dm numWarehouses 10
6.创建索引和序列
create index ndx_customer_name on benchmarksql.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 history;
if(n != 1) then
select max(hist_id) + 1 into n from 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.history modify hist_id integer default (benchmarksql.hist_id_seq.nextval)
7.修改dm.ini参数
通过disql登录DM8服务器并执行如下SQL命令：
–注意BUFFER和MAX_BUFFER根据实际可用内存来决定
–如果是长时间测试，如测试8小时，应该把检查点设置密集些，如CKPT_INTERVAL=300
–MAX_SESSIONS根据实际测试时终端数决定
–MAX_CONCURRENT_TRX只在连接数超过300之后，才把默认值0修改为50到200之间试试。
SP_SET_PARA_VALUE (2,'MAX_OS_MEMORY',100);
SP_SET_PARA_VALUE (2,'MEMORY_POOL',300);
SP_SET_PARA_VALUE (2,'BUFFER',2000);
SP_SET_PARA_VALUE (2,'BUFFER_POOLS',14);
SP_SET_PARA_VALUE (2,'FAST_POOL_PAGES',10000);
SP_SET_PARA_VALUE (2,'FAST_ROLL_PAGES',8000);
SP_SET_PARA_VALUE (2,'RECYCLE',8);
SP_SET_PARA_VALUE (2,'MULTI_PAGE_GET_NUM',1);
SP_SET_PARA_VALUE (2,'MAX_BUFFER',14000);
SP_SET_PARA_VALUE (2,'WORKER_THREADS',16);
SP_SET_PARA_VALUE (2,'CKPT_RLOG_SIZE',0);
SP_SET_PARA_VALUE (2,'CKPT_DIRTY_PAGES',0);
SP_SET_PARA_VALUE (2,'FORCE_FLUSH_PAGES',0);
SP_SET_PARA_VALUE (2,'DIRECT_IO',0);
SP_SET_PARA_VALUE (2,'IO_THR_GROUPS',16);
SP_SET_PARA_VALUE (2,'BDTA_SIZE',20);
SP_SET_PARA_VALUE (2,'FAST_COMMIT',99);
SP_SET_PARA_VALUE (2,'ENABLE_IN_VALUE_LIST_OPT',1);
SP_SET_PARA_VALUE (2,'ENABLE_SPACELIMIT_CHECK',0);
SP_SET_PARA_VALUE (2,'RLOG_PARALLEL_ENABLE',1);
--SP_SET_PARA_VALUE (2,'RLOG_RESERVE_SIZE',0);
SP_SET_PARA_VALUE (2,'SESS_CHECK_INTERVAL',30);
SP_SET_PARA_VALUE (2,'FAST_RELEASE_SLOCK',0);
SP_SET_PARA_VALUE (2,'NOWAIT_WHEN_UNIQUE_CONFLICT',1);
SP_SET_PARA_VALUE (2,'UNDO_EXTENT_NUM',32);
SP_SET_PARA_DOUBLE_VALUE (2,'UNDO_RETENTION',0.08);
SP_SET_PARA_VALUE (2,'MAX_SESSIONS',1000);
SP_SET_PARA_VALUE (2,'MAX_CONCURRENT_TRX',0);
SP_SET_PARA_VALUE (2,'MAX_SESSION_STATEMENT',20000);
SF_SET_SYSTEM_PARA_VALUE('SUBQ_EXP_CVT_FLAG', 0, 0, 1);
SF_SET_SYSTEM_PARA_VALUE('PURGE_DEL_OPT', 2, 0, 1);
SP_SET_PARA_VALUE (2,'ENABLE_FREQROOTS',0);
SP_SET_PARA_VALUE (2,'CACHE_POOL_SIZE',200);
SP_SET_PARA_VALUE (2,'DICT_BUF_SIZE',100);
SP_SET_PARA_VALUE (2,'ENABLE_HASH_JOIN',0);
SP_SET_PARA_VALUE (2,'PHC_MODE_ENFORCE',11);
SP_SET_PARA_VALUE (2,'ADAPTIVE_NPLN_FLAG',1);
SP_SET_PARA_VALUE (2,'RLOG_CHECK_SPACE',0);
SP_SET_PARA_VALUE (2,'CKPT_INTERVAL',3600);
SP_SET_PARA_VALUE (2,'BATCH_PARAM_OPT',0);
SP_SET_PARA_VALUE (2,'VM_MEM_HEAP',1);
SP_SET_PARA_VALUE (2,'COMM_VALIDATE',0);
SP_SET_PARA_VALUE (2,'DECIMAL_FIX_STORAGE',1);
SP_SET_PARA_VALUE(2, 'PARALLEL_PURGE_FLAG', 1);
SP_SET_PARA_VALUE(2, 'ENABLE_HASH_JOIN', 0);
有部分参数可能不能修改
8.退出服务器
–手动修改dm.ini参数，以下参数对性能提升有很大帮助
CKPT_FLUSH_PAGES = 0
ENABLE_MONITOR = 0
RS_PRE_FETCH=0
FAST_RW_LOCK=2
--HASH_ACTIVE_TRX_VIEW=0 默认为0，且dm.ini中无该参数
BDTA_SIZE=16
FIRST_ROWS=16
RLOG_RESERVE_SIZE=0 最小值为2048，需要手工修改为0
其他内存参数， worker_threads等可以看情况调节,有部分参数可能不能修改
9.重启服务器
./dmserver /dm8/data/DAMENG/dm.ini
10.扩库以及日志文件、优化表
alter tablespace "ROLL" resize datafile 'ROLL.DBF' to 1024;
alter database resize logfile 'DAMENG01.log' to 1024;
alter database resize logfile 'DAMENG02.log' to 1024;
11.预加载数据
将热点数据预加载到内存中
SP_SET_TAB_FAST_POOL_FLAG('BENCHMARKSQL', 'WAREHOUSE', 1);
SP_SET_TAB_FAST_POOL_FLAG('BENCHMARKSQL', 'DISTRICT', 1);
SP_SET_TAB_FAST_POOL_FLAG('BENCHMARKSQL', 'ITEM', 1);
select count(*) from "BENCHMARKSQL"."CUSTOMER" union all
select count(*) from "BENCHMARKSQL"."DISTRICT" union all
select count(*) from "BENCHMARKSQL"."ITEM" union all
select count(*) from "BENCHMARKSQL"."NEW_ORDER" union all
select count(*) from "BENCHMARKSQL"."OORDER" union all
select count(*) from "BENCHMARKSQL"."ORDER_LINE" union all
select count(*) from "BENCHMARKSQL"."STOCK" union all
select count(*) from "BENCHMARKSQL"."WAREHOUSE" union all
select count(*) from "BENCHMARKSQL"."HISTORY" union all
select count("C_PAYMENT_CNT") from "BENCHMARKSQL"."CUSTOMER";
12.执行测试
执行下面命令
./runBenchmark.sh props.dameng
3321
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
