DM8数据库TPCC测试

一、tpcc简介

        事务处理性能委员会（ Transaction Processing Performance Council ），简称TPC，它的功能是制定商务应用基准程序（Benchmark）的标准规范、性能和价格度量，并管理测试结果的发布。

        TPC－C是在线事务处理(OLTP)的基准程序，用于衡量数据库系统OLTP性能的指标。

二、数据库安装

2.1初始化数据库实例

        ./dminit  path=/data  PAGE_SIZE=32  EXTENT_SIZE=32

        根据测试经验页大小32k、簇大小32k的时候效果较好，其他参数采用默认值。查看磁盘中固态磁盘空间是否充足，如果现场环境允许优先将数据文件夹放在固态盘上，固态磁盘空间不充足时，将REDO放在固态盘上。

2.2启动数据库服务

        ./dmserver /data/DAMENG/dm.ini

三、测试软件

3.1 安装oracle JDK1.7

3.2 上传驱动文件到lib下

cp /dm8/drivers/jdbc/DmJdbcDriver18.jar 

3.3 编辑配置文件

3.4 创建用户表空间

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

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

GRANT DBA TO BENCHMARKSQL;

3.5 创建测试所需表

执行此脚本./runSQL.sh props.dm tableCreates

3.6 装载数据

./runLoader.sh props.dm

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

3.8 数据预加载

--测试前执行如下SQL：

--item表，8K的页，需要占用1300页

SP_SET_TAB_FAST_POOL_FLAG('BENCHMARKSQL', 'BMSQL_ITEM', 1);

SP_SET_TAB_FAST_POOL_FLAG('BENCHMARKSQL', 'BMSQL_WAREHOUSE', 1);

SP_SET_TAB_FAST_POOL_FLAG('BENCHMARKSQL','BMSQL_DISTRICT', 1);

--内存充足的情况下可以执行下面的语句，把数据预加载到内存。内存不足情况下，优先舍弃BMSQL_HISTORY（去掉下面语句中对BMSQL_HISTORY表的统计）

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;

3.9 开始测试

        完成以上步骤之后就可以开始进行测试了，测试之前先检查BENCHMARKSQL 工具的参数设置。打开top 和nmon观察dmserver CPU使用情况。

./runBenchmark.sh props.dm

4 SQL语句监控

4.1 检查redo的剩余大小

SELECT CAST(SYSDATE AS VARCHAR(20)) CHECK_TIME,

TOTAL_SPACE/1024/1024 TOTAL_SIZE_MB,

FREE_SPACE/1024/1024 FREE_SIZE_MB,

(TOTAL_SPACE-FREE_SPACE)/1024/1024 USED_SPACE_MB

FROM V$RLOG;

4.2 手工做全量检查点

SELECT CHECKPOINT(100);

4.3 统计表的空间占用

select sum(a) from (

select table_used_space('BENCHMARKSQL','BMSQL_CUSTOMER')*(page()/1024)/1024 a union all

select table_used_space('BENCHMARKSQL','BMSQL_DISTRICT')*(page()/1024)/1024 a union all

select table_used_space('BENCHMARKSQL','BMSQL_HISTORY')*(page()/1024)/1024 a union all

select table_used_space('BENCHMARKSQL','BMSQL_ITEM')*(page()/1024)/1024 a union all

select table_used_space('BENCHMARKSQL','BMSQL_NEW_ORDER')*(page()/1024)/1024 a union all

select table_used_space('BENCHMARKSQL','BMSQL_OORDER')*(page()/1024)/1024 a union all

select table_used_space('BENCHMARKSQL','BMSQL_ORDER_LINE')*(page()/1024)/1024 a union all

select table_used_space('BENCHMARKSQL','BMSQL_STOCK')*(page()/1024)/1024 a union all

select table_used_space('BENCHMARKSQL','BMSQL_WAREHOUSE')*(page()/1024)/1024 a ) b;

4.4 统计表的行数

select count(*) from "BENCHMARKSQL"."BMSQL_CONFIG" union all

select count(*) from "BENCHMARKSQL"."BMSQL_CUSTOMER" union all

select count(*) from "BENCHMARKSQL"."BMSQL_DISTRICT" union all

select count(*) from "BENCHMARKSQL"."BMSQL_HISTORY" union all

select count(*) from "BENCHMARKSQL"."BMSQL_ITEM" union all

select count(*) from "BENCHMARKSQL"."BMSQL_NEW_ORDER" union all

select count(*) from "BENCHMARKSQL"."BMSQL_OORDER" union all

select count(*) from "BENCHMARKSQL"."BMSQL_ORDER_LINE" union all

select count(*) from "BENCHMARKSQL"."BMSQL_STOCK" union all

select count(*) from "BENCHMARKSQL"."BMSQL_WAREHOUSE";

4.5 检验ACID一致性

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

上述6个SQL在TPCC测试前、中、后，结果均应该为0

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