benchmarksql测试mysql_benchmarksql5.0测试达梦数据库

一、 简述

使用工具为benchmarksql5.0版本。

主要目的是使用 benchmarksql5.0工具进行 TPCC 性能测试。

上传文件：benchmarksql-5.0.zip、apache-ant-1.10.3-bin.zip

上传目录：/home/setup/tpcc/

二、 测试环境

操作系统：中标麒麟 release V7Update6/(Chromium)-aarch64

处理器：8核*2

内存：32G

硬盘：500G

处理器架构：arm

三、 测试指标描述

指标项 指标值 备注

warehouses 8 单实例情况

loadWorkers 8

terminals 32

runMins 5

四、 测试过程

1、准备环境

启动达梦数据库；

准备JDK1.8以上环境；

下载解压benchmarksql5.0；

上传达梦数据库jdbc驱动包：

达梦驱动包：DmJdbcDriver18.jar

上传目录：/home/setup/tpcc/benchmarksql-5.0/lib/

2、编译工具准备

配置apache-ant的环境变量：

[root@dbtest jdbc]# vi ~/.bash_profile

#添加以下内容

export APACH_HOME=/home/setup/tpcc/apache-ant-1.10.3

export PATH=P A T H : PATH:PATH:APACH_HOME/bin:

[root@dbtest jdbc]# source ~/.bash_profile

编译前如果没有安装jdk1.8的话，执行yum install java-1.8.0-openjdk* -y

本步骤是编译benchmarksql-5.0工具，生成jar文件：

/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar

[root@dbtest benchmarksql-5.0]# yum install java-1.8.0-openjdk* -y

[root@dbtest benchmarksql-5.0]# ant

3、编辑初始化配置

复制一个props.ora 该名为props.dm 并修改内容

#cp props.ora props.dm

#vi props.dm

db=oracle

driver=dm.jdbc.driver.DmDriver

conn=jdbc:dm://192.168.21.201:5236/DAMENG

user=BENCHMARKSQL

password=123456789

warehouses=8

loadWorkers=8

terminals=32

//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

//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec

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=myresult%tY-%tm-%td_%tH%tM%tS

osCollectorScript=./misc/os_collector_linux.py

osCollectorInterval=1

//osCollectorSSHAddr=user@dbhost

osCollectorDevices=net_enp62s0f0 blk_sda

进入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)

)storage(without counter);

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)

)storage(without counter);

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)

)storage(without counter);

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)

)storage(without counter);

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)

)storage(without counter);

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)

)storage(without counter);

create index ndx_customer_name

on benchmarksql.bmsql_customer (c_w_id, c_d_id, c_last, c_first);

修改indexCreates.sql脚本：

create index ndx_customer_name

on benchmarksql.customer (c_w_id, c_d_id, c_last, c_first);

4、装载数据

创建表和索引：

#./runSql props.dm sql.common/tableCreate.sql

#./runSql props.dm sql.common/indexCreate.sql

插入数据：

#./runLoader.sh props.dm

5、修改数据库参数

编辑dm.ini文件，修改以下参数：

MAX_OS_MEMORY = 100

MEMORY_POOL = 1000

BUFFER = 10000

BUFFER_POOLS = 10

FAST_POOL_PAGES = 90000

FAST_ROLL_PAGES = 80000

RECYCLE = 8

MULTI_PAGE_GET_NUM = 64

MAX_BUFFER = 100000

VM_MEM_HEAP = 0

WORKER_THREADS = 24

WORK_THRD_STACK_SIZE = 512

CKPT_RLOG_SIZE = 0

CKPT_INTERVAL = 3600

CKPT_DIRTY_PAGES = 0

FORCE_FLUSH_PAGES = 0

IO_THR_GROUPS =12

CHECK_DB_IS_ACTIVE = 0

BDTA_SIZE = 20

ENABLE_SPACELIMIT_CHECK = 0

RLOG_PARALLEL_ENABLE = 1

RLOG_RESERVE_SIZE = 0

SESS_CHECK_INTERVAL = 30

FAST_RELEASE_SLOCK = 0

NOWAIT_WHEN_UNIQUE_CONFLICT = 1

UNDO_EXTENT_NUM = 32

UNDO_RETENTION = 1

MAX_SESSIONS = 1000

SUBQ_EXP_CVT_FLAG = 0

PURGE_DEL_OPT = 1

ENABLE_FREQROOTS = 2

CACHE_POOL_SIZE = 100

DICT_BUF_SIZE = 20

ENABLE_MONITOR = 0

6、运行TPCC测试

./runBenchmark.sh props.dm

五、 测试结果

INFO Thread-19 Term-00, Measured tpmC (NewOrders) = 79719.07

INFO Thread-19 Term-00, Measured tpmTOTAL = 177314.72

INFO Thread-19 Term-00, Session Start = 2020-04-01 10:05:24

INFO Thread-19 Term-00, Session End = 2020-04-01 10:10:24

INFO Thread-19 Term-00, Transaction Count = 886614