DM8达梦数据库tpcc测试（benchmarksql）
关于达梦数据库产品的更多问题，⼤家可以流浪云适配技术社区。
此篇技术博客只介绍了如何使⽤benchmarksql在达梦数据库上测试，关于达梦数据库的参数性能优化，还请查阅其他资料，或在社区中查找相关内容。
1.准备benchmarksql⼯具
准备benchwarksql⼯具⽤来TPCC测试
2.检查驱动版本
我的benchmarksql⼯具的解压⽬录为/ceshi/tpcc/benchmarksql
查看下⾯⽬录下是否有达梦JDK，如果没有就去/home/dmdba/dmdbms/drivers/jdbc拷贝
ll /ceshi/tpcc/benchmarksql/lib/dm
3.配置props.dm⽂件
配置prop.dm⽂件来规划预创建表的数据量
vi /ceshi/tpcc/benchmarksql/run/props.dm
以上更改为以下释义：
warehou：被测仓库数，决定测试表的数据量
loadworkers：数据装载的并发数
terminals：数据库连接并发数
runMins：测试时间，单位为分钟
4.执⾏sql脚本创建表
可以⽤以下命令在操作系统执⾏
cd /ceshi/tpcc/benchmarksql/run
./runSQL.sh props.dm ./sql.dameng /tableCreates.sql
也可以将/ceshi/tpcc/benchmarksql/run/sql.dameng/tableCreates.sql中的语句复制出来在管理⼯具或者disql中执⾏
CREATE TABLESPACE TPCC DATAFILE 'TPCC01.dbf' SIZE 20480;
CREATE USER "BENCHMARKSQL" IDENTIFIED BY "123456789" DEFAULT TABLESPACE "TPCC";
GRANT DBA TO "BENCHMARKSQL";
CREATE SEQUENCE "BENCHMARKSQL"."BMSQL_HIST_ID_SEQ" INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 MINVALUE 1;
CREATE TABLE "BENCHMARKSQL"."BMSQL_CONFIG"
(
"CFG_NAME" VARCHAR(30) NOT NULL,
"CFG_VALUE" VARCHAR(50),
CLUSTER PRIMARY KEY("CFG_NAME")) STORAGE(ON "TPCC", CLUSTERBTR) ;
CREATE TABLE "BENCHMARKSQL"."BMSQL_CUSTOMER"
(
"C_W_ID" INTEGER NOT NULL,
"C_D_ID" INTEGER NOT NULL,
"C_ID" INTEGER NOT NULL,
"C_DISCOUNT" DECIMAL(4,4),
"C_CREDIT" CHAR(2),
"C_LAST" VARCHAR(16),
"C_FIRST" VARCHAR(16),
"C_CREDIT_LIM" DECIMAL(12,2),
"C_BALANCE" DECIMAL(12,2),
"C_YTD_PAYMENT" DECIMAL(12,2),
"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(6),
"C_MIDDLE" CHAR(2),
"C_DATA" VARCHAR(500),
CLUSTER PRIMARY KEY("C_W_ID", "C_D_ID", "C_ID")) STORAGE(ON "TPCC", CLUSTERBTR, WITHOUT COUNTER) ;
CREATE TABLE "BENCHMARKSQL"."BMSQL_DISTRICT"
(
"D_W_ID" INTEGER NOT NULL,
"D_ID" INTEGER NOT NULL,
"D_YTD" DECIMAL(12,2),
"D_TAX" DECIMAL(4,4),
"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 2, ON "TPCC", CLUSTERBTR, WITHOUT COUNTER) ;
CREATE TABLE "BENCHMARKSQL"."BMSQL_HISTORY"
(
"HIST_ID" INTEGER DEFAULT BENCHMARKSQL.BMSQL_HIST_ID_SEQ.NEXTVAL,
"H_C_ID" INTEGER,
"H_C_D_ID" INTEGER,
"H_C_D_ID" INTEGER,
"H_C_W_ID" INTEGER,
"H_D_ID" INTEGER,
"H_W_ID" INTEGER,
"H_DATE" TIMESTAMP(6),
"H_AMOUNT" DECIMAL(6,2),
"H_DATA" VARCHAR(24)) STORAGE(ON "TPCC", BRANCH(32, 32), WITHOUT COUNTER) ;
CREATE TABLE "BENCHMARKSQL"."BMSQL_ITEM"
(
"I_ID" INTEGER NOT NULL,
"I_NAME" VARCHAR(24),
"I_PRICE" DECIMAL(5,2),
"I_DATA" VARCHAR(50),
"I_IM_ID" INTEGER,
CLUSTER PRIMARY KEY("I_ID")) STORAGE(ON "TPCC", CLUSTERBTR, 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(ON "TPCC", CLUSTERBTR, 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" INTEGER,
"O_ALL_LOCAL" INTEGER,
"O_ENTRY_D" TIMESTAMP(6),
CLUSTER PRIMARY KEY("O_W_ID", "O_D_ID", "O_ID")) STORAGE(ON "TPCC", CLUSTERBTR, 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(6),
"OL_AMOUNT" DECIMAL(6,2),
"OL_SUPPLY_W_ID" INTEGER,
"OL_QUANTITY" INTEGER,
"OL_DIST_INFO" CHAR(24),
CLUSTER PRIMARY KEY("OL_W_ID", "OL_D_ID", "OL_O_ID", "OL_NUMBER")) STORAGE(ON "TPCC", CLUSTERBTR, WITHOUT COUNTER) ;
有苦说不出
CREATE TABLE "BENCHMARKSQL"."BMSQL_STOCK"
(
"S_W_ID" INTEGER NOT NULL,
"S_I_ID" INTEGER NOT NULL,
"S_QUANTITY" INTEGER,
"S_YTD" INTEGER,
"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")) STORAGE(ON "TPCC", CLUSTERBTR, WITHOUT COUNTER) ;
CREATE TABLE "BENCHMARKSQL"."BMSQL_WAREHOUSE"
(
"W_ID" INTEGER NOT NULL,
"W_YTD" DECIMAL(12,2),
"W_TAX" DECIMAL(4,4),
"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, ON "TPCC", CLUSTERBTR, WITHOUT COUNTER) ;
5.装载数据
可以执⾏以下命令来装载数据
cd /ceshi/tpcc/benchmarksql/run
./runLoader.sh props.dm numWarehous 10
6.执⾏测试脚本
执⾏以下命令启动测试脚本
cd /ceshi/tpcc/benchmarksql/run
前台：
./runBenchmark.sh props.dm
后台：（效率更⾼）
nohup ./runBenchmark.sh props.dm > /dev/null 2>&1 &
7.Benchmarksql测试结果验证
1.语句验证
书法集字
缓慢的近义词丧句子(SELECT W_ID, W_YTD FROM BMSQL_WAREHOUSE) EXCEPT(SELECT D_W_ID, SUM(D_YTD) FROM BMSQL_DISTRICT GROUP BY D_W_ID); (SELECT D_W_ID, D_ID, D_NEXT
_O_ID - 1 FROM BMSQL_DISTRICT) EXCEPT (SELECT O_W_ID,O_D_ID, MAX(O_ID) FROM BMSQL_OORDER GROUP BY (SELECT D_W_ID, D_ID, D_NEXT_O_ID - 1 FROM BMSQL_DISTRICT) EXCEPT (SELECT NO_W_ID,NO_D_ID, MAX(NO_O_ID) FROM BMSQL_NEW_ORDER SELECT * FROM (SELECT (COUNT(NO_O_ID)-(MAX(NO_O_ID)-MIN(NO_O_ID)+1)) AS DIFF FROM BMSQL_NEW_ORDER GROUP BY NO_W_ID, NO_D_ID) (SELECT O_W_ID, O_D_ID, SUM(O_OL_CNT) FROM BMSQL_OORDER GROUP BY O_W_ID, O_D_ID) EXCEPT (SELECT OL_W_ID, OL_D_ID, COUNT(OL_O (SELECT D_W_ID, SUM(D_YTD) FROM BMSQL_DISTRICT GROUP BY D_W_ID) EXCEPT (SELECT W_ID, W_YTD FROM BMSQL_WAREHOUSE);
连接到数据库做以上查询，如果结果全为0⾏，则说明tpcc库是⼀致的，否则说明此库在运⾏过程中数
据出现混乱。
2、验证 bmsql_history 表新增的记录数
transaction count * 43% 约等于 bmsql_history 表增加的数据量
不能存在数量级的偏差
