tpcc测试文档
1：上传软件并解压
扩容：
alter database resize logfile ‘/dm8/data/DAMENG/DAMENG01.log’ to 2048;
alter database resize logfile ‘/dm8/data/DAMENG/DAMENG02.log’ to 2048;
alter database add logfile ‘/dm8/data/DAMENG/DAMENG03.log’ size 2048;
alter database add logfile ‘/dm8/data/DAMENG/DAMENG04.log’ size 2048;
2：执行sql工具建表
修改prod.dm参数
重启数据库
在管理工具执行sql语句
Sql语句为：
/*
DROP USER “BENCHMARKSQL” CASCADE;
DROP TABLESPACE BENCHMARKSQL1;
*/
CREATE TABLESPACE BENCHMARKSQL1 DATAFILE ‘BENCHMARKSQL1.dbf’ SIZE 10000;
CREATE USER “BENCHMARKSQL” IDENTIFIED BY “123456789” DEFAULT TABLESPACE “BENCHMARKSQL1”;
GRANT DBA TO BENCHMARKSQL;
alter tablespace “ROLL” resize datafile ‘ROLL.DBF’ to 10000;
alter database resize logfile ‘DAMENG01.log’ to 10000;
alter database resize logfile ‘DAMENG02.log’ to 10000;
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 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.bmsql_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.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)
);
3：装载数据
[dmdba@dm1 run]$ ./runLoader.sh props.dm numWarehouses 10
4：执行测试工具
[dmdba@dm1 run]$ ./runBenchmark.sh props.dm
select count() from BENCHMARKSQL.bmsql_config;
select count () from BENCHMARKSQL.bmsql_warehouse;
select count（） from BENCHMARKSQL.bmsql_district;
select count（） from BENCHMARKSQL.bmsql_customer;
select count () from BENCHMARKSQL.bmsql_history;
select count () from BENCHMARKSQL.bmsql_oorder;
select count () from BENCHMARKSQL.bmsql_new_order;
select count () from BENCHMARKSQL.bmsql_order_line;
select count () from BENCHMARKSQL.bmsql_stock;
select count () from BENCHMARKSQL.bmsql_item;
验证方法
1、
(Select w_id, w_ytd from warehouse) except(select d_w_id, sum(d_ytd) from
district group by d_w_id);
2、
(Select d_w_id, d_id, D_NEXT_O_ID - 1 from district) except (select o_w_id,
o_d_id, max(o_id) from oorder group by o_w_id, o_d_id);
3、
(Select d_w_id, d_id, D_NEXT_O_ID - 1 from district) except (select no_w_id,
no_d_id, max(no_o_id) from new_order group by no_w_id, no_d_id);
4、
select * from (select (count(no_o_id)-(max(no_o_id)-min(no_o_id)+1)) as diff
from new_order group by no_w_id, no_d_id) where diff != 0;
5、
(select o_w_id, o_d_id, sum(o_ol_cnt) from oorder group by o_w_id, o_d_id)
except (select ol_w_id, ol_d_id, count(ol_o_id) from order_line group by ol_w_id,
ol_d_id);
6、
(select d_w_id, sum(d_ytd) from district group by d_w_id) except(Select
w_id, w_ytd from warehouse);
连接到数据库做以上查询，如果结果全为0行，则说明tpcc库是一致的，否则说明此库在运行过程中数
据出现混乱。
2、验证 bmsql_history 表新增的记录数
transaction count * 43% 约等于 bmsql_history 表增加的数据量，不能存在数量级
达梦云适配技术社区
https://eco.dameng.com/