- 目的及原理
TPC-C是在线事务处理(OLTP)的基准程序,用于衡量数据库系统OLTP性能的指标。
TPC-C测试用到的模型是一个大型的商品批发销售公司,它拥有若干个分布在不同区域的商品仓库。每个仓库负责为10个销售点供货,其中每个销售点为3000个客户提供服务,每个客户提交的订单中,平均每个订单有10项产品(5-15件),所有订单中约1%的产品在其直接所属的仓库中没有存货,必须由其他区域的仓库来供货。同时,每个仓库都要维护公司销售的100000种商品的库存记录。
- TPC-C最大的有效的吞吐量(MQTh)的度量是每分钟处理的新订单数量,单位是tpmC
- MQTh正常的范围为(active-warehouse*terminals-per-warehouse)的0.9倍至1.2倍之间
- 为了得到较高的MQTh,可以根据实际测试结果预估,来调整Active-warehouse再次测试
- 测试流程
- 初始化数据库
根据测试经验页大小32k、簇大小32k的时候效果较好,其他参数采用默认值。
./dminit path=/mnt/data PAGE_SIZE=32 EXTENT_SIZE=32
-
- 参数调整
- 修改动态参数
- 参数调整
--执行以下SQL语句修改动态参数
SP_SET_PARA_VALUE (2,'MAX_OS_MEMORY',100);
SP_SET_PARA_VALUE (2,'MEMORY_POOL',300);
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,'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,'BDTA_SIZE',16);
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,'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', 1, 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,'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);
SP_SET_PARA_VALUE(2, 'COMM_TRACE', 0);
SP_SET_PARA_VALUE(2, 'ENABLE_MONITOR', 0);
SP_SET_PARA_VALUE(2, 'RS_PRE_FETCH', 0);
SP_SET_PARA_VALUE(2, 'HASH_ACTIVE_TRX_VIEW', 0);
SP_SET_PARA_VALUE(2, 'FIRST_ROWS', 16);
SP_SET_PARA_VALUE(2, 'BTR_SPLIT_MODE',1);
SP_SET_PARA_VALUE(2, 'UPD_DEL_OPT',0);
declare
v_mem_mb int;
v_cpus int;
BUFFER int;
BUFFER_POOLS INT;
begin
SELECT TOP 1 N_CPU,TOTAL_PHY_SIZE/1024/1024 INTO v_cpus,v_mem_mb FROM V$SYSTEMINFO;
v_mem_mb=round(v_mem_mb,-3);
SP_SET_PARA_VALUE(2,'TASK_THREADS',v_cpus);
SP_SET_PARA_VALUE(2,'IO_THR_GROUPS',v_cpus/2);
IF v_mem_mb >= 64000 THEN
BUFFER_POOLS :=101;
ELSE
BUFFER_POOLS :=53;
END IF;
BUFFER := round(cast(v_mem_mb * 0.8 as int),-3);
SP_SET_PARA_VALUE(2,'BUFFER', BUFFER);
SP_SET_PARA_VALUE(2,'MAX_BUFFER', BUFFER);
SP_SET_PARA_VALUE(2,'BUFFER_POOLS', BUFFER_POOLS);
end;
/
注1:BUFFER估算公式为:单库不超过100M,BUFFER>= 100M *库个数。内存允许的情况下,可以放大,本文档中BUFFER直接设置物理内存的80%。
注2:WORKER_THREADS估算公式为:WORKER_THREADS = 系统逻辑cpu个数
注3:IO_THR_GROUPS估算公式为:IO_THR_GROUPS =系统逻辑cpu个数 / 2
注4:BDTA_SIZE=20和UNDO_RETENTION<=3必须同时设置
-
-
- 修改静态参数
-
完成上节动态参数修改后,停止数据库服务,手工修改dm.ini修改下列参数。
FAST_RW_LOCK=1
RLOG_RESERVE_SIZE=0
-
- 建表
创建用户、表空间及表结构、索引等,不能用benchmarksql5.0自带的表结构,并且一定要用DM8的JDBC驱动。benchmarksql5.0要求jdk版本不低于1.7,使用sun版本jdk。sqlTableCreates_DM脚本也可以在管理工具中执行。
--执行如下命令更新JDBC驱动创建表结构:
cd /mnt/wt/bms5/lib/dameng
cp -r DM8JdbcDriver.jar ./
cd /mnt/wt/bms5/run
./runSQL.sh props.dm sqlTableCreates_DM
--props.dm脚本内容如下:
driver=dm.jdbc.driver.DmDriver
conn=jdbc:dm://192.168.17.101:5236 --注:这里要填IP地址,不要填127.0.0.1
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脚本内容如下:
/*
DROP USER "BENCHMARKSQL" CASCADE;
DROP TABLESPACE BENCHMARKSQL1;
*/
CREATE TABLESPACE BENCHMARKSQL1 DATAFILE 'BENCHMARKSQL1.dbf' SIZE 10000;
CREATE USER "BENCHMARKSQL" IDENTIFIED BY "123456789" DEFAULT TABLESPACE "BENCHMARKSQL1";
GRANT DBA TO BENCHMARKSQL;
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)
);
注1:DECIMAL类型都改为FLOAT类型
注2:去掉外键
注3:主键强制聚集
注4:without counter表示关闭计数器
注5:Count时会把所有数据读入内存
注6:benchmarksql.bmsql_warehouse和benchmarksql.bmsql_district的storage(fillfactor 2,without counter);
注7:benchmarksql.bmsql_history改为list表,且storage(branch(32,32),without counter)
注8:其它表storage(without counter)
-- 扩大REDO、UNDO日志空间。
alter tablespace "ROLL" resize datafile 'ROLL.DBF' to 10000;
alter database resize logfile 'DAMENG01.log' to 20000;
alter database resize logfile 'DAMENG02.log' to 20000;
-
- 装载数据
运行以下命令装载100个仓库数据。
./runLoader.sh props.dm
注:要把props.dm中的user改为BENCHMARKSQL
-
- 创建索引和序列
调用以下脚本创建索引和序列,或者在管理工具中执行。
./runSQL.sh props.dm sqlIndexCreates_DM
-- sqlIndexCreates_DM脚本内容如下:
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);
-
- 数据预加载
上面装载完数据后并未重启,热点数据已进内存,这时直接测试效果最好,如果数据库重启后再开始测试,就需要执行以下语句将热点数据装入内存。
--测试前执行如下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);
--内存充足的情况下可以执行下面的语句,把数据预加载到内存。内存不足情况下,优先舍弃BMSQL_HISTORY(去掉下面语句中对BMSQL_HISTORY表的统计)
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";
-
- 测试
- 检查BENCHMARKSQL参数
- 测试
--修改log4j.xml
1.确保配置文件log4j.xml有如下配置,如果没有,手动添加(压力机硬件较差的情况下,会对测试造成巨大影响):
<param name="Threshold" value="info"/>
2.修改日志级别,trace改为info
<priority value="info"/>
--修改props.dm将用户名密码换成BENCHMARKSQL用户的
--props.dm脚本内容如下:
driver=dm.jdbc.driver.DmDriver
conn=jdbc:dm://192.168.17.101:5236
user=BENCHMARKSQL
password=123456789
warehouses=100
terminals=100
runMins=10
runTxnsPerTerminal=0
limitTxnsPerMin=0
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
-
-
- 开始测试
-
执行以下命令开始测试:
./runBenchmark.sh props.dm
Running Average tpmTOTAL:每分钟平均执行事务数(所有事务)
Memory Usage:客户端内存使用情况
Measured tpmC (NewOrders) :每分钟执行的事务数(只统计 NewOrders 事务)
Transaction Count:执行的交易总数量
- 结果分析
结果输出在run目录my_result_*****下,可以根据结果报告目录生成可视化 html页面,也会生成在这个目录下。(如果报错,提示找不到目录或文件(csv),是因为没有装R环境yum install R)
./generateReport.sh my_result_2022-03-29_150921/
重点:
- 要设置磁盘高度算法为deadline
- Props.dm中的conn连接串,要写实际的IP,不要写127.0.0.1
欢迎访问达梦社区:http://eco.dameng.com