学习使用TPC-C测试

一、前言

TPC－C是在线事务处理(OLTP)的基准程序，用于衡量数据库系统OLTP性能的指标。本次TPC－C将会使用到BenchmarkSQL工具

OLTP:On-Line Transaction Processing 联机事务处理,也称为面向交易的处理系统，其基本特征是顾客的原始数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果。这样做的最大特点是可以即时地处理输入的数据，及时地回答。所以也称为实时系统。

BenchmarkSQL工具介绍

该工具为开源软件，采用范围更广泛；

基于JAVA语言开发，跨平台运行；

要求JDK1.7及以上运行环境；

非标准TPC-C模型，纯压力测试

BenchmarkSQL使用介绍：

执行SQL工具建表

./runSQL.sh props.dm sqlTableCreates

执行数据装载工具装载数据

./runLoader.sh props.dm numWarehouses 10

执行测试工具

./runBenchmark.sh props.dm

为提高性能，通常采用后台运行的方式

nohup ./runBenchmark.sh props.dm > /dev/null 2>&1 &

测试报告打印在run/log文件下

Benchmarksql.log

C_ITEM	商品种类：固定值	100K
C_WAREHOUSE	仓库数	W
C_STOCK	库存数=仓库数*商品种类	W*100K
C_DISTRICT	分区数=仓库数*10	W*10
C_CUSTOMER	客户数=分区数*3000	W*30K
C_ORDER	订单数=客户数	W*30K+
C_HISTORY	订单数=客户数	W*30K+
C_NEW_ORDER	新订单数=订单数*900/3000	W*9K+
C_ORDER_LINE	订单行数=订单数*10*0.99...	W*300K+

二、环境准备

 操作系统：redhat 7.5 x86_64

服务器配置：2核4G

            磁盘50G

数据库版本：DM8开发版

benchmarksql版本：benchmarksql-5.0rc2-westone-v1.2

JAVA版本：18.0.1.1

三、正式安装与使用

1.将BenchmarkSQL工具传输到服务上

2.解压

cd /opt/tmp

tar -zxvf bms.tar.gz

3.使用sql语句建表

CREATE TABLESPACE BENCHMARKSQL1 DATAFILE 'BENCHMARKSQL1.dbf' SIZE 20000;

CREATE USER "BENCHMARKSQL" IDENTIFIED BY "123456789" DEFAULT TABLESPACE "BENCHMARKSQL1";

GRANT DBA TO BENCHMARKSQL;

alter tablespace "ROLL" resize datafile 'ROLL.DBF' to 1000;

alter database resize logfile 'DAMENG01.log' to 4000;

alter database resize logfile 'DAMENG02.log' to 4000;

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       decimal(22,2),

  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        decimal(22,2),

  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)

);

4.查看配置文件

vi props.dm

 

其中warehouses是被测仓库数

Loadworkers是数据装载并发数

Terminals是数据库连接并发数

runMins是测试时间（分钟）

因为是自己电脑上的虚拟机，所以选择的参数都偏小

Warehouses=10

Loadworkers=2

runMins=1

5. 执行数据装载工具装载数据

./runLoader.sh props.dm numWarehouses 10

 

装载数据完毕后查看下表内数据

select count(*) from BENCHMARKSQL.bmsql_config      union all

select count(*) from BENCHMARKSQL.bmsql_warehouse   union all

select count(*) from BENCHMARKSQL.bmsql_district    union all

select count(*) from BENCHMARKSQL.bmsql_customer    union all

select count(*) from BENCHMARKSQL.bmsql_history     union all

select count(*) from BENCHMARKSQL.bmsql_oorder      union all

select count(*) from BENCHMARKSQL.bmsql_new_order   union all

select count(*) from BENCHMARKSQL.bmsql_order_line  union all

select count(*) from BENCHMARKSQL.bmsql_stock       union all

select count(*) from BENCHMARKSQL.bmsql_item;

 

6.执行测试工具

./runBenchmark.sh props.dm

也可以采用后台的方式运行

nohup ./runBenchmark.sh props.dm > /dev/null 2>&1 &

7.建立索引

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);

8.查看bmsql_history表

select count(*) from BENCHMARKSQL.bmsql_history;

 

发现数据增加了16448条，transaction count * 43% 约等于 bmsql_history 表增加的数据量。

查看TPCC基准测试一致性验证语句

(Select w_id, w_ytd from BENCHMARKSQL.bmsql_warehouse) except(select d_w_id, sum(d_ytd) from BENCHMARKSQL.bmsql_district group by d_w_id);

(Select d_w_id, d_id, D_NEXT_O_ID - 1 from BENCHMARKSQL.bmsql_district)  except (select o_w_id, o_d_id, max(o_id) from BENCHMARKSQL.bmsql_oorder group by  o_w_id, o_d_id);

(Select d_w_id, d_id, D_NEXT_O_ID - 1 from BENCHMARKSQL.bmsql_district)  except (select no_w_id, no_d_id, max(no_o_id) from BENCHMARKSQL.bmsql_new_order group by no_w_id, no_d_id);

select * from (select (count(no_o_id)-(max(no_o_id)-min(no_o_id)+1)) as diff from BENCHMARKSQL.bmsql_new_order group by no_w_id, no_d_id) where diff != 0;

(select o_w_id, o_d_id, sum(o_ol_cnt) from BENCHMARKSQL.bmsql_oorder  group by o_w_id, o_d_id) except (select ol_w_id, ol_d_id, count(ol_o_id) from BENCHMARKSQL.bmsql_order_line group by ol_w_id, ol_d_id);

(select d_w_id, sum(d_ytd) from BENCHMARKSQL.bmsql_district group by d_w_id)  except(Select w_id, w_ytd from BENCHMARKSQL.bmsql_warehouse);

 

如果结果全为0行，则说明tpcc库是一致的，否则说明此库在运行过程中数据出现混乱。

四、后记

  可以将磁盘尽量扩大点，第一次因为磁盘太小导致在数据装载时失败，如果文中哪里有误，欢迎指出，大家共同交流进步！

社区地址：https://eco.dameng.com