BenchmarkSQL v5.0测试达梦数据库
1、安装jdk7及以上版本，并配置环境变量
（本文使用版本：1.8.0_181）
用vim编辑器来编辑/etc/profile文件，在文件末尾添加一下内容（按“i”进入编辑）：
export JAVA_HOME=/home/software/jdk1.8.0_251
export JRE_HOME=${JAVA_HOME}/jre
export CLASSPATH=.:${JAVA_HOME}/lib:${JRE_HOME}/lib:$CLASSPATH
export JAVA_PATH=${JAVA_HOME}/bin:${JRE_HOME}/bin
export PATH=$PATH:${JAVA_PATH}
通过命令source /etc/profile让profile 文件立即生效
2、官网下载apache-ant
https://ant.apache.org/bindownload.cgi下载zip包，解压
（本文使用版本：apache-ant-1.9.15-bin.zip）
将bin目录添加进环境变量PATH中：
vim ~/.bashrc
最后一行加入 export PATH=/xxx/apache-ant-1.9.15/bin:$PATH
保存退出，重新加载环境变量 source ~/.bashrc
3、官网下载BenchmarkSQL
https://sourceforge.net/projects/benchmarksql/
（本文使用版本：BenchmarkSQL v5.0）
4、修改源码，使其支持达梦数据库
进入解压后的 benchmarksql-5.0 目录，执行 ant 即可。
【报错】
db=dameng driver=dm.jdbc.driver.DmDriver conn=jdbc:dm://localhost:5236 user=benchmarksql password=123456789 warehouses=100 //表示要装载100个仓库，1个仓库约100M loadWorkers=4 //表示线程数 terminals=144 //To run specified transactions per terminal- runMins must equal zero runTxnsPerTerminal=0 //To run for specified minutes- runTxnsPerTerminal must equal zero runMins=5 //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=true //The following five values must add up to 100 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=dameng_result_%tY-%tm-%td_%tH%tM%tS osCollectorScript=./misc/os_collector_linux.py osCollectorInterval=1 //osCollectorSSHAddr=user@dbhost osCollectorDevices=net_enp5s0f1 blk_sda
7、修改funcs.sh文件，添加dameng数据库
按下图两处红框处添加：
8、在lib库中添加dameng目录，放置driver包
9、修改benchmarksql-5.0/run/runDatabaseBuild.sh，去掉AFTER_LOAD中的extraHistID
10、初始化数据库
./dminit path=/mnt/lun2/tpcc
11、启动服务器
./dmserver /mnt/lun2/tpcc/DAMENG/dm.ini
12、建用户建表
#删除benchmarksql用户 drop user benchmarksql cascade; #创建benchmarksql用户，密码123456789 CREATE USER benchmarksql IDENTIFIED BY 123456789; #授权benchmarksql用户DBA管理员权限 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) );
13、装载数据
cd benchmarksql-5.0/run
./runLoader.sh props.dm
可以看到MAIN.DBF文件变大
14、创建索引和序列
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 benchmarksql.bmsql_history; if(n != 1) then select max(hist_id) + 1 into n from benchmarksql.bmsql_history; end if; PRINT n; stmt1:='create sequence benchmarksql.bmsql_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.bmsql_hist_id_seq.nextval);
15、修改dm.ini参数
通过disql登录DM8服务器并执行如下SQL命令：
SP_SET_PARA_VALUE (2,'MAX_OS_MEMORY',100); SP_SET_PARA_VALUE (2,'MEMORY_POOL',300); SP_SET_PARA_VALUE (2,'BUFFER',14000); SP_SET_PARA_VALUE (2,'BUFFER_POOLS',97); 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',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);
16、退出服务器
手动修改dm.ini参数，以下参数对性能提升有很大帮助
CKPT_FLUSH_PAGES = 0 #Minimum number of flushed pages for checkpoints，默认1000 ENABLE_MONITOR = 0 #Whether to enable monitor，默认1 FAST_RW_LOCK=2 #Fast Read Write Lock flag，默认1 FIRST_ROWS=16 #maximum number of rows when first returned to clients，默认100
其他内存参数， worker_threads等可以看情况调节
17、重启服务器
./dmserver /mnt/lun2/tpcc/DAMENG/dm.ini
18、扩库以及日志文件、优化表
alter tablespace "ROLL" resize datafile 'ROLL.DBF' to 10000;
alter database resize logfile 'DAMENG01.log' to 50000;
alter database resize logfile 'DAMENG02.log' to 50000;
--测试前执行如下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";
19、执行测试
./runBenchmark.sh props.dm
执行结果如下：
jTPCC : Term-00, C value for C_LTerm-00, Running Average tpmTOTAL: 66755.57
jTPCC : Term-00, Measured tpmC (NewOrders) = 30082.27
jTPCC : Term-00, Measured tpmTOTAL = 66751.79
jTPCC : Term-00, Session Start = 2020-08-20 15:44:30
jTPCC : Term-00, Session End = 2020-08-20 15:49:30
jTPCC : Term-00, Transaction Count = 333877
测试结果说明
Measured tpmC (NewOrders) = 30082.27，每分钟新订单数为30082.27
Measured tpmTOTAL = 66751.79，每分钟处理的总数为66751.79
Transaction Count = 333877，5分钟处理的总数为333877
posted on 2020-11-06 18:00 seabiscuit0922 阅读(2957) 评论(0) 编辑 收藏 举报
