达梦8-TPCC压力测试

m0_71649906

于 2024-09-10 17:06:07 发布

阅读量450

点赞数 2

文章标签：数据库

本文链接：https://blog.csdn.net/m0_71649906/article/details/142100711

版权

1、系统环境

操作系统版本	银河麒麟Linux kylin10 4.19.90-24.4.v2101.ky10.x86_64
数据库版本	DM Database Server 64 V8

2、软件环境

压测工具	BenchmarkSQL-5.0
JDK	Jdk 1.8.0_272
编译工具	Apache-ant-1.9.15

DM数据库安装目录	/dm8
压测工具所在目录	/opt/TPCC/benchmarksql-5.0

因为BenchmarkSQL-5.0默认不支持达梦数据库，需要手动修改相应的配置文件，并安装相关驱动。

3、修改配置文件

3.1 修改jTPCC.java文件

文件所在目录路径：/opt/TPCC/benchmarksql-5.0/src/client，在 jTPCC.java添加如下信息：

3.2 修改funcs.sh文件

文件所在目录路径：/opt/TPCC/benchmarksql-5.0/run，添加如下信息

3.3 修改runDatabaseBuild.sh文件

文件所在目录路径：/opt/TPCC/benchmarksql-5.0/run，删除extraHistID，如下信息：

3.4 编译

进入压测工具benchmarksql5的目录执行命令

[root@DMDB01 benchmarksql-5.0]# pwd
/opt/TPCC/benchmarksql-5.0
[root@DMDB01 benchmarksql-5.0]# ant
Buildfile: /opt/TPCC/benchmarksql-5.0/build.xml

init:
    [mkdir] Created dir: /opt/TPCC/benchmarksql-5.0/build

compile:
    [javac] Compiling 11 source files to /opt/TPCC/benchmarksql-5.0/build

dist:
    [mkdir] Created dir: /opt/TPCC/benchmarksql-5.0/dist
      [jar] Building jar: /opt/TPCC/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar

BUILD SUCCESSFUL
Total time: 0 seconds
[root@DMDB01 benchmarksql-5.0]#

3.5 安装驱动

达梦数据库的驱动目录：/dm8/drivers/jdbc，将目录下的DmJdbcDriver18.jar拷贝到benchmarksql5的lib目录下。

（1）需要先在/opt/TPCC/benchmarksql-5.0/lib下创建“dameng”目录

（2）将达梦数据库的驱动拷贝到/opt/TPCC/benchmarksql-5.0/lib/dameng目录下

因为我的系统中jdk版本是1.8，所以只需要拷贝DmJdbcDriver18.jar

[root@DMDB01 lib]# cp /dm8/drivers/jdbc/DmJdbcDriver18.jar /opt/TPCC/benchmarksql-5.0/lib/dameng

3.6 修改props.dm文件

需要先创建props.dm配置文件，根据/opt/TPCC/benchmarksql-5.0/run目录下的props.ora文件创建props.dm。

[root@DMDB01 run]# vi props.dm 
db=dameng  #数据库类型，与funcs.sh配置文件一致
driver=dm.jdbc.driver.DmDriver  
conn=jdbc:dm://localhost:5236
user=BENCHMARKSQL  #数据库用户
password=123456789  #数据库用户密码

warehouses=10  #仓库数量
loadWorkers=4  #初始仓库中数据的并行数

terminals=10  #并发数（最大仓库数10倍）最好是与仓库数量一致
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0 #事务数模式（与runMins互斥）
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=5  #测试时间（分钟）
//Number of total transactions per minute
limitTxnsPerMin=0  #限制每分钟全局事务数（一般为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=my_result_%tY-%tm-%td_%tH%tM%tS
osCollectorScript=./misc/os_collector_linux.py
osCollectorInterval=1
//osCollectorSSHAddr=user@dbhost
osCollectorDevices=net_eth0 blk_sda
~

4、压力测试

4.1 创建表空间、用户

创建压力测试所需的表空间、用户

#创建表空间 BENCHMARKSQL_DATA
CREATE TABLESPACE BENCHMARKSQL_DATA DATAFILE 'BENCHMARKSQL_DATA01.dbf' SIZE 1024;
ALTER TABLESPACE BENCHMARKSQL_DATA ADD DATAFILE 'BENCHMARKSQL_DATA02.dbf' SIZE 1024;
ALTER TABLESPACE BENCHMARKSQL_DATA ADD DATAFILE 'BENCHMARKSQL_DATA03.dbf' SIZE 1024;

#创建索引表空间
CREATE TABLESPACE BENCHMARKSQL_IDX DATAFILE 'BENCHMARKSQL_IDX01.dbf' SIZE 1024;
ALTER TABLESPACE BENCHMARKSQL_IDX ADD DATAFILE 'BENCHMARKSQL_IDX02.dbf' SIZE 1024;
ALTER TABLESPACE BENCHMARKSQL_IDX ADD DATAFILE 'BENCHMARKSQL_IDX03.dbf' SIZE 1024;


#创建用户
CREATE USER "BENCHMARKSQL" IDENTIFIED BY "123456789" DEFAULT TABLESPACE "BENCHMARKSQL_DATA" default index tablespace "BENCHMARKSQL_IDX";

#授权
GRANT DBA TO BENCHMARKSQL;

4.2 创建表

在/opt/TPCC/benchmarksql-5.0/run/sql.common目录下有tableCreates.sql文件，创建相应的表

create table BENCHMARKSQL.bmsql_config (
  cfg_name    varchar(30) primary key,
  cfg_value   varchar(50)
);

create table BENCHMARKSQL.bmsql_warehouse (
  w_id        integer   not null,
  w_ytd       decimal(12,2),
  w_tax       decimal(4,4),
  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)
);

create table BENCHMARKSQL.bmsql_district (
  d_w_id       integer       not null,
  d_id         integer       not null,
  d_ytd        decimal(12,2),
  d_tax        decimal(4,4),
  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)
);

create table BENCHMARKSQL.bmsql_customer (
  c_w_id         integer        not null,
  c_d_id         integer        not null,
  c_id           integer        not null,
  c_discount     decimal(4,4),
  c_credit       char(2),
  c_last         varchar(16),
  c_first        varchar(16),
  c_credit_lim   decimal(12,2),
  c_balance      decimal(12,2),
  c_ytd_payment  decimal(12,2),
  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)
);

create sequence BENCHMARKSQL.bmsql_hist_id_seq;

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 decimal(6,2),
  h_data   varchar(24)
);

create table BENCHMARKSQL.bmsql_new_order (
  no_w_id  integer   not null,
  no_d_id  integer   not null,
  no_o_id  integer   not null
);

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     integer,
  o_all_local  integer,
  o_entry_d    timestamp
);

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       decimal(6,2),
  ol_supply_w_id  integer,
  ol_quantity     integer,
  ol_dist_info    char(24)
);

create table BENCHMARKSQL.bmsql_item (
  i_id     integer      not null,
  i_name   varchar(24),
  i_price  decimal(5,2),
  i_data   varchar(50),
  i_im_id  integer
);

create table BENCHMARKSQL.bmsql_stock (
  s_w_id       integer       not null,
  s_i_id       integer       not null,
  s_quantity   integer,
  s_ytd        integer,
  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)
);

4.3 装载数据

[root@DMDB01 run]# ./runLoader.sh props.dm numWarehouses 10
Starting BenchmarkSQL LoadData

driver=dm.jdbc.driver.DmDriver
conn=jdbc:dm://localhost:5236
user=BENCHMARKSQL
password=***********
warehouses=10
loadWorkers=4
fileLocation (not defined)
csvNullValue (not defined - using default 'NULL')

Worker 000: Loading ITEM
Worker 001: Loading Warehouse      1
Worker 002: Loading Warehouse      2
Worker 003: Loading Warehouse      3
Worker 000: Loading ITEM done
Worker 000: Loading Warehouse      4
Worker 003: Loading Warehouse      3 done
Worker 003: Loading Warehouse      5
Worker 002: Loading Warehouse      2 done
Worker 002: Loading Warehouse      6
Worker 001: Loading Warehouse      1 done
Worker 001: Loading Warehouse      7
Worker 000: Loading Warehouse      4 done
Worker 000: Loading Warehouse      8
Worker 003: Loading Warehouse      5 done
Worker 003: Loading Warehouse      9
Worker 001: Loading Warehouse      7 done
Worker 001: Loading Warehouse     10
Worker 002: Loading Warehouse      6 done
Worker 000: Loading Warehouse      8 done
Worker 003: Loading Warehouse      9 done
Worker 001: Loading Warehouse     10 done

4.3 创建索引、约束

参照/opt/TPCC/benchmarksql-5.0/run/sql.common/indexCreates.sql

alter table BENCHMARKSQL.bmsql_warehouse add constraint bmsql_warehouse_pkey
  primary key (w_id);

alter table BENCHMARKSQL.bmsql_district add constraint bmsql_district_pkey
  primary key (d_w_id, d_id);

alter table BENCHMARKSQL.bmsql_customer add constraint bmsql_customer_pkey
  primary key (c_w_id, c_d_id, c_id);

create index bmsql_customer_idx1
  on  BENCHMARKSQL.bmsql_customer (c_w_id, c_d_id, c_last, c_first);

alter table BENCHMARKSQL.bmsql_oorder add constraint bmsql_oorder_pkey
  primary key (o_w_id, o_d_id, o_id);

create unique index bmsql_oorder_idx1
  on  BENCHMARKSQL.bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);

alter table BENCHMARKSQL.bmsql_new_order add constraint bmsql_new_order_pkey
  primary key (no_w_id, no_d_id, no_o_id);

alter table BENCHMARKSQL.bmsql_order_line add constraint bmsql_order_line_pkey
  primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);

alter table BENCHMARKSQL.bmsql_stock add constraint bmsql_stock_pkey
  primary key (s_w_id, s_i_id);

alter table BENCHMARKSQL.bmsql_item add constraint bmsql_item_pkey
  primary key (i_id);

4.4 收集统计信息

--收集指定用户下所有表所有列的统计信息：
DBMS_STATS.GATHER_SCHEMA_STATS('BENCHMARKSQL',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

--收集指定用户下所有索引的统计信息：
DBMS_STATS.GATHER_SCHEMA_STATS('BENCHMARKSQL',1.0,TRUE);

4.5 压测

[root@DMDB01 run]# ./runBenchmark.sh props.dm 
16:52:14,692 [main] INFO   jTPCC : Term-00, 
16:52:14,693 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
16:52:14,693 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0
16:52:14,693 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
16:52:14,693 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa
16:52:14,693 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier
16:52:14,695 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck
16:52:14,695 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
16:52:14,695 [main] INFO   jTPCC : Term-00, 
16:52:14,695 [main] INFO   jTPCC : Term-00, db=dameng
16:52:14,695 [main] INFO   jTPCC : Term-00, driver=dm.jdbc.driver.DmDriver
16:52:14,695 [main] INFO   jTPCC : Term-00, conn=jdbc:dm://localhost:5236
16:52:14,695 [main] INFO   jTPCC : Term-00, user=BENCHMARKSQL
16:52:14,695 [main] INFO   jTPCC : Term-00, 
16:52:14,695 [main] INFO   jTPCC : Term-00, warehouses=10
16:52:14,696 [main] INFO   jTPCC : Term-00, terminals=10
16:52:14,696 [main] INFO   jTPCC : Term-00, runMins=5
16:52:14,696 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=0
16:52:14,696 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=true
16:52:14,696 [main] INFO   jTPCC : Term-00, 
16:52:14,696 [main] INFO   jTPCC : Term-00, newOrderWeight=45
16:52:14,696 [main] INFO   jTPCC : Term-00, paymentWeight=43
16:52:14,696 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
16:52:14,697 [main] INFO   jTPCC : Term-00, deliveryWeight=4
16:52:14,697 [main] INFO   jTPCC : Term-00, stockLevelWeight=4
16:52:14,697 [main] INFO   jTPCC : Term-00, 
16:52:14,697 [main] INFO   jTPCC : Term-00, resultDirectory=dameng_result_%tY-%tm-%td_%tH%tM%tS
16:52:14,697 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
16:52:14,697 [main] INFO   jTPCC : Term-00, 
16:52:14,709 [main] INFO   jTPCC : Term-00, copied props.dm to dameng_result_2024-09-11_165214/run.properties
16:52:14,709 [main] INFO   jTPCC : Term-00, created dameng_result_2024-09-11_165214/data/runInfo.csv for runID 6
16:52:14,709 [main] INFO   jTPCC : Term-00, writing per transaction results to dameng_result_2024-09-11_165214/data/result.csv
16:52:14,709 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
16:52:14,709 [main] INFO   jTPCC : Term-00, osCollectorInterval=1
16:52:14,709 [main] INFO   jTPCC : Term-00, osCollectorSSHAddr=null
16:52:14,709 [main] INFO   jTPCC : Term-00, osCollectorDevices=net_ens33 blk_sda
16:52:14,745 [main] INFO   jTPCC : Term-00,
16:52:14,860 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 30
16:52:14,860 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    144
16:52:14,860 [main] INFO   jTPCC : Term-00, 
Term-00, Running Average tpmTOTAL: 41883.08    Current tpmTOTAL: 1386072    Memory Usage: 35MB / 60MB          
16:57:15,067 [Thread-11] INFO   jTPCC : Term-00, 
16:57:15,069 [Thread-11] INFO   jTPCC : Term-00, 
16:57:15,069 [Thread-11] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 18858.98
16:57:15,069 [Thread-11] INFO   jTPCC : Term-00, Measured tpmTOTAL = 41869.13
16:57:15,069 [Thread-11] INFO   jTPCC : Term-00, Session Start     = 2024-09-11 16:52:14
16:57:15,069 [Thread-11] INFO   jTPCC : Term-00, Session End       = 2024-09-11 16:57:15
16:57:15,069 [Thread-11] INFO   jTPCC : Term-00, Transaction Count = 209427
[root@DMDB01 run]#

测试结果

Measured tpmC (NewOrders) = 18858.98 #每分钟新订单数
Measured tpmTOTAL = 41869.13 #每分钟处理的总数
Session Start = 2024-09-11 16:52:14
Session End = 2024-09-11 16:57:15
Transaction Count = 209427 #5分钟处理的总数