达梦TPCC

最新推荐文章于 2023-02-27 23:15:02 发布

bjdm

最新推荐文章于 2023-02-27 23:15:02 发布

阅读量159

点赞数

本文链接：https://blog.csdn.net/weixin_46708599/article/details/113554843

版权

达梦TPCC测试

1、上传压缩包并解压

[root@localhost tpcc]# ll
total 4768-rw-r--r-- 1 root root 4879862 Oct 30  2019 benchmarksql-4.1.1.zip
[root@localhost tpcc]# unzip -oq benchmarksql-4.1.1.zip

2、创建用户BENCHMARKSQL及其表空间

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;
ALTER TABLESPACE BENCHMARKSQL_DATA ADD DATAFILE 'BENCHMARKSQL_DATA04.dbf' SIZE 1024;
ALTER TABLESPACE BENCHMARKSQL_DATA ADD DATAFILE 'BENCHMARKSQL_DATA05.dbf' SIZE 1024;
CREATE TABLESPACE BENCHMARKSQL_IDX DATAFILE 'BENCHMARKSQL_IDX01.dbf' SIZE 2048;
ALTER TABLESPACE BENCHMARKSQL_IDX ADD DATAFILE 'BENCHMARKSQL_IDX02.dbf' SIZE 1024;
ALTER TABLESPACE BENCHMARKSQL_IDX ADD DATAFILE 'BENCHMARKSQL_IDX03.dbf' SIZE 1024;
ALTER TABLESPACE BENCHMARKSQL_IDX ADD DATAFILE 'BENCHMARKSQL_IDX04.dbf' SIZE 1024;
ALTER TABLESPACE BENCHMARKSQL_IDX ADD DATAFILE 'BENCHMARKSQL_IDX05.dbf' SIZE 1024;
CREATE USER "BENCHMARKSQL" IDENTIFIED BY "123456789" DEFAULT TABLESPACE "BENCHMARKSQL_DATA" default index tablespace "BENCHMARKSQL_IDX";
GRANT DBA TO BENCHMARKSQL;
/
create table benchmarksql.warehouse (
  w_id        integer   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 1);

create table benchmarksql.district (
  d_w_id       integer       not null,
  d_id         integer       not null,
  d_ytd        float,
  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.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.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.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.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.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.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.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)
);



commit;

3.装载数据

–执行如下命令：
cd /ssd/benchmarksql-4.1.1/run

./runLoader.sh props.dm numWarehouses 100

注：想装载多少库就写多少

执行成功后如下：

------------- LoadJDBC Statistics --------------------
     Start Time = Mon Jun 17 03:54:13 EDT 2019
       End Time = Mon Jun 17 04:26:42 EDT 2019
       Run Time = 1948 Seconds
    Rows Loaded = 50005708 Rows
Rows Per Second = 25670 Rows/Sec
------------------------------------------------------

4.创建索引和序列

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;
 stmt1 varchar(200);
 begin 
   select count(*)+1 into n from history;
   if(n != 1) then
      select max(hist_id) + 1 into n from 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.history modify hist_id integer default (benchmarksql.hist_id_seq.nextval)

5.修改dm.ini参数

通过disql登录DM7服务器并执行如下SQL命令：

SP_SET_PARA_VALUE (2,'MAX_OS_MEMORY',100);
SP_SET_PARA_VALUE (2,'MEMORY_POOL',300);
SP_SET_PARA_VALUE (2,'BUFFER',14000);
SP_SET_PARA_VALUE (2,'BUFFER_POOLS',97);
SP_SET_PARA_VALUE (2,'FAST_POOL_PAGES',10000);
SP_SET_PARA_VALUE (2,'FAST_ROLL_PAGES',8000);
SP_SET_PARA_VALUE (2,'RECYCLE',8);
SP_SET_PARA_VALUE (2,'MULTI_PAGE_GET_NUM',1);
SP_SET_PARA_VALUE (2,'MAX_BUFFER',14000);
SP_SET_PARA_VALUE (2,'WORKER_THREADS',16);
SP_SET_PARA_VALUE (2,'CKPT_RLOG_SIZE',0);
SP_SET_PARA_VALUE (2,'CKPT_DIRTY_PAGES',0);
SP_SET_PARA_VALUE (2,'FORCE_FLUSH_PAGES',0);
SP_SET_PARA_VALUE (2,'DIRECT_IO',0);
SP_SET_PARA_VALUE (2,'IO_THR_GROUPS',16);
SP_SET_PARA_VALUE (2,'BDTA_SIZE',20);
SP_SET_PARA_VALUE (2,'FAST_COMMIT',99);
SP_SET_PARA_VALUE (2,'ENABLE_IN_VALUE_LIST_OPT',1);
SP_SET_PARA_VALUE (2,'ENABLE_SPACELIMIT_CHECK',0);
SP_SET_PARA_VALUE (2,'RLOG_PARALLEL_ENABLE',1);
--SP_SET_PARA_VALUE (2,'RLOG_RESERVE_SIZE',0);
SP_SET_PARA_VALUE (2,'SESS_CHECK_INTERVAL',30);
SP_SET_PARA_VALUE (2,'FAST_RELEASE_SLOCK',0);
SP_SET_PARA_VALUE (2,'NOWAIT_WHEN_UNIQUE_CONFLICT',1);
SP_SET_PARA_VALUE (2,'UNDO_EXTENT_NUM',32);
SP_SET_PARA_DOUBLE_VALUE (2,'UNDO_RETENTION',0.08);
SP_SET_PARA_VALUE (2,'MAX_SESSIONS',1000);
SP_SET_PARA_VALUE (2,'MAX_CONCURRENT_TRX',0);
SP_SET_PARA_VALUE (2,'MAX_SESSION_STATEMENT',20000);
SF_SET_SYSTEM_PARA_VALUE('SUBQ_EXP_CVT_FLAG', 0, 0, 1);
SF_SET_SYSTEM_PARA_VALUE('PURGE_DEL_OPT', 2, 0, 1);
SP_SET_PARA_VALUE (2,'ENABLE_FREQROOTS',0);
SP_SET_PARA_VALUE (2,'CACHE_POOL_SIZE',200);
SP_SET_PARA_VALUE (2,'DICT_BUF_SIZE',100);
SP_SET_PARA_VALUE (2,'ENABLE_HASH_JOIN',0);
SP_SET_PARA_VALUE (2,'PHC_MODE_ENFORCE',11);
SP_SET_PARA_VALUE (2,'ADAPTIVE_NPLN_FLAG',1);	
SP_SET_PARA_VALUE (2,'RLOG_CHECK_SPACE',0);
SP_SET_PARA_VALUE (2,'CKPT_INTERVAL',3600);
SP_SET_PARA_VALUE (2,'BATCH_PARAM_OPT',0);
SP_SET_PARA_VALUE (2,'VM_MEM_HEAP',1);
SP_SET_PARA_VALUE (2,'COMM_VALIDATE',0);
SP_SET_PARA_VALUE (2,'DECIMAL_FIX_STORAGE',1);
SP_SET_PARA_VALUE(2, 'PARALLEL_PURGE_FLAG', 1);
SP_SET_PARA_VALUE(2, 'ENABLE_HASH_JOIN', 0);

6.重启服务器

./dmserver /ssd/tpcc/DAMENG/dm.ini

7.扩库以及日志文件、优化表

alter tablespace "ROLL" resize datafile 'ROLL.DBF' to 10000;
alter database resize logfile 'DAMENG01.log' to 20000;
alter database resize logfile 'DAMENG02.log' to 20000;

–测试前执行如下SQL：
–item表，8K的页，需要占用1300页

SP_SET_TAB_FAST_POOL_FLAG('BENCHMARKSQL', 'WAREHOUSE', 1);
SP_SET_TAB_FAST_POOL_FLAG('BENCHMARKSQL', 'DISTRICT', 1);
SP_SET_TAB_FAST_POOL_FLAG('BENCHMARKSQL', 'ITEM', 1);

如果是压力测试（数据都可以在内存放下），可以执行下面的语句，把数据预加载到内存：

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

8.执行测试

执行下面命令

./runBenchmark.sh props.dameng

bjdm

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
达梦TPCC

达梦TPCC测试1、上传压缩包并解压[root@localhost tpcc]# lltotal 4768-rw-r--r-- 1 root root 4879862 Oct 30 2019 benchmarksql-4.1.1.zip[root@localhost tpcc]# unzip -oq benchmarksql-4.1.1.zip 2、创建用户BENCHMARKSQL及其表空间CREATE TABLESPACE BENCHMARKSQL_DATA DATAFILE 'BENCH
复制链接

扫一扫