达梦数据库TPCC测试

1.介绍
TPC-C是专门针对联机交易处理系统（OLTP系统）的规范，一般情况下我们也把这类系统称为业务处理系统。

2.解压

unzip benchmarksql-4.1.1.zip

3.拷贝数据库驱动到lib文件夹下，配置文件

vi /home/dmdba/benchmarksql-4.1.1/run/props.dm7

4创建数据库测试用户

create user benchmarksql identified by 123456789;
grant dba to benchmarksql;

5.定义表

create table benchmarksql.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.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 3,without counter);

create table benchmarksql.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.hist_id_seq;


create table benchmarksql.history (
  hist_id  int  default benchmarksql.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(16,16),without counter);

create table benchmarksql.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.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.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.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.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);

6.装载数据库

cd benchmarksql-4.1.1\run
./runLoader.sh props.dm7 numWAREHOUSES 10

7.创建索引和存储过程

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

create or replace procedure benchmarksql.createsequence 
as
 n int;
 stmt varchar(100);
 begin 
   stmt:='drop sequence benchmarksql.hist_id_seq';
   EXECUTE IMMEDIATE stmt;
   select max(hist_id) + 1 into n from benchmarksql.history ;
   stmt:='create sequence benchmarksql.hist_id_seq start with '||n;
   EXECUTE IMMEDIATE stmt;
end;
call createsequence;

8.预读表数据

select count(*) from "BENCHMARKSQL"."CUSTOMER" union all
select count(*) from "BENCHMARKSQL"."DISTRICT" union all
select count(*) from "BENCHMARKSQL"."ITEM" union all
select count(*) from "BENCHMARKSQL"."NEW_ORDER" union all
select count(*) from "BENCHMARKSQL"."OORDER" union all
select count(*) from "BENCHMARKSQL"."ORDER_LINE" union all
select count(*) from "BENCHMARKSQL"."STOCK" union all
select count(*) from "BENCHMARKSQL"."WAREHOUSE" union all
select count(*) from "BENCHMARKSQL"."HISTORY"  union all
select count("C_PAYMENT_CNT") from "BENCHMARKSQL"."CUSTOMER";
commit;

9.开始测试

[dmdba@localhost run]$ ./runBenchmark.sh props.dm7