DM数据库简单的TPCC测试

测试步骤一：启动数据库创建相关测试表

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

CREATE USER "BENCHMARKSQL" IDENTIFIED BY "T123456789" DEFAULT TABLESPACE "BENCHMARKSQL1";
GRANT DBA TO BENCHMARKSQL;

alter tablespace "ROLL" resize datafile 'ROLL.DBF' to 1000;
alter database resize logfile 'DMDB01.log' to 4000;
alter database resize logfile 'DMDB02.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)
);

测试步骤二：上传tpcc测试工具包到服务器，并解压

 测试步骤三：修改工具包中的参数文件props.dm

warehouses表示待测的仓库数

terminals表示终端数

runMins表示测试时间为5分钟

终端数不能大于仓库数的10倍

测试步骤四：拷贝数据库驱动到bms5工具的lib下，驱动选择一般是根据jdk环境进行选择，如虚拟机jdk环境是1.8就需要拷贝达梦数据库1.8的JDK驱动。

 测试步骤五：加载基准数据，装载10个仓数据

 测试步骤六：创建序列和索引

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

测试步骤七：预加载数据，避免重复去磁盘上读取数据

 测试步骤八：运行测试，此处测试时间是5分钟

 测试结果

推荐使用达梦的云适配中心网站了解更多使用内容：https://eco.dameng.com，或者到云适配中心的社区去提问哦！