DM8 TPCC测试

最新推荐文章于 2024-05-07 08:30:22 发布

weixin_34938478

最新推荐文章于 2024-05-07 08:30:22 发布

阅读量1.9k

点赞数 1

文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/weixin_34938478/article/details/122065637

版权

达梦数据库 - 新一代大型通用关系型数据库 | 达梦云适配中心

1.使用benchwarksql进行tpcc测试上传所需软件包并解压bms.tar

bms5_index.sql bms5_table.sql bms.tar

2.检查驱动版本

../benchmarksql/lib/dm 下面是否是最新的驱动，如果没有直接到数据库下拷贝/opt/dmdbms/drivers/jdbc/DmJdbcDriver18.jar

3.配置prop.dm文件来规划预创建表的数据量

[root@DM02 run]$ vi props.dm

db=dm

driver=dm.jdbc.driver.DmDriver

conn=jdbc:dm://10.15.1.12:5236

user=SYSDBA

password=SYSDBA

warehouses=100

loadWorkers=48

terminals=10

runMins=5

runTxnsPerTerminal=0

limitTxnsPerMin=0

newOrderWeight=45

paymentWeight=43

orderStatusWeight=4

deliveryWeight=4

stockLevelWeight=4

warehouse：被测仓库数，决定测试表的数据量注意分区表目前仅支持100,200,300,400,500,600,800,1000,2000仓。

loadworkers：数据装载的并发数

terminals：数据库连接并发数

runMins：测试时间，单位为分钟

4.脚本建表

[root@D02 run]# ./runSQL.sh props.dm ./sql.dameng/tableCreates.sql

[root@D02 run]# ./runSQL.sh props.dm ./sql.dameng/tableCreates.sql
# ------------------------------------------------------------
# Loading SQL file ./sql.dameng/tableCreates.sql
# ------------------------------------------------------------
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_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.装载数据

装载数据执行需编辑文件vi props.dm 更改user和password为新创建的用户及密码

[root@DM02 run]# ./runLoader.sh props.dm numWarehouses 10
Starting BenchmarkSQL LoadData

driver=dm.jdbc.driver.DmDriver
conn=jdbc:dm://10.15.1.12:5236
user=BENCHMARKSQL
password=***********
warehouses=100
loadWorkers=48
fileLocation (not defined)
csvNullValue (not defined - using default 'NULL')

Worker 000: Loading ITEM
Worker 001: Loading Warehouse      1
Worker 002: Loading Warehouse      2
Worker 003: Loading Warehouse      3
Worker 004: Loading Warehouse      4
Worker 005: Loading Warehouse      5
Worker 006: Loading Warehouse      6
Worker 007: Loading Warehouse      7
Worker 008: Loading Warehouse      8
Worker 009: Loading Warehouse      9
Worker 010: Loading Warehouse     10
Worker 011: Loading Warehouse     11
Worker 012: Loading Warehouse     12
Worker 013: Loading Warehouse     13
Worker 014: Loading Warehouse     14

6.执行测试脚本开始测试

前台执行：

./runBenchmark.sh props.dm

后台执行：

nohup ./runBenchmark.sh props.dm > /dev/null 2>&1 &

[root@DM02 run]# ./runBenchmark.sh props.dm
15:21:35,098 [main] INFO   jTPCC : Term-00, 
15:21:35,100 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
15:21:35,100 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0rc2
15:21:35,101 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
15:21:35,101 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa
15:21:35,101 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier
15:21:35,103 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck
15:21:35,103 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
15:21:35,103 [main] INFO   jTPCC : Term-00, 
15:21:35,103 [main] INFO   jTPCC : Term-00, db=dm
15:21:35,103 [main] INFO   jTPCC : Term-00, driver=dm.jdbc.driver.DmDriver
15:21:35,103 [main] INFO   jTPCC : Term-00, conn=jdbc:dm://10.15.1.12:5236
15:21:35,103 [main] INFO   jTPCC : Term-00, user=BENCHMARKSQL
15:21:35,103 [main] INFO   jTPCC : Term-00, 
15:21:35,103 [main] INFO   jTPCC : Term-00, warehouses=100
15:21:35,103 [main] INFO   jTPCC : Term-00, terminals=10
15:21:35,105 [main] INFO   jTPCC : Term-00, runMins=5
15:21:35,105 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=0
15:21:35,105 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=null
15:21:35,105 [main] INFO   jTPCC : Term-00, 
15:21:35,105 [main] INFO   jTPCC : Term-00, newOrderWeight=45
15:21:35,105 [main] INFO   jTPCC : Term-00, paymentWeight=43
15:21:35,105 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
15:21:35,105 [main] INFO   jTPCC : Term-00, deliveryWeight=4
15:21:35,105 [main] INFO   jTPCC : Term-00, stockLevelWeight=4
15:21:35,105 [main] INFO   jTPCC : Term-00, 
15:21:35,105 [main] INFO   jTPCC : Term-00, resultDirectory=null
15:21:35,106 [main] INFO   jTPCC : Term-00, osCollectorScript=null
15:21:35,106 [main] INFO   jTPCC : Term-00, 
15:21:35,289 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 254
15:21:35,289 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    140
15:21:35,289 [main] INFO   jTPCC : Term-00, 
Term-00, Running Average tpmTOTAL: 31068.69    Current tpmTOTAL: 1028148    Memory Usage: 236MB / 1698MB          
15:26:35,413 [Thread-2] INFO   jTPCC : Term-00, 
15:26:35,413 [Thread-2] INFO   jTPCC : Term-00, 
15:26:35,414 [Thread-2] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 13978.54
15:26:35,414 [Thread-2] INFO   jTPCC : Term-00, Measured tpmTOTAL = 31069.15
15:26:35,414 [Thread-2] INFO   jTPCC : Term-00, Session Start     = 2021-12-15 15:21:35
15:26:35,414 [Thread-2] INFO   jTPCC : Term-00, Session End       = 2021-12-15 15:26:35
15:26:35,414 [Thread-2] INFO   jTPCC : Term-00, Transaction Count = 155352

7.验证测试结果

(1) TPCC基准测试一致性验证语句

(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 O_W_ID, O_D_ID);

(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 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 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 BMSQL_OORDER GROUP BY O_W_ID, O_D_ID) EXCEPT (SELECT OL_W_ID, OL_D_ID, COUNT(OL_O_ID) FROM BMSQL_ORDER_LINE GROUP BY OL_W_ID,OL_D_ID);

(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 表新增的记录数

(3) transaction count * 43% 约等于 bmsql_history 表增加的数据量，不能存在数量级的偏差

(4) 以下图为例9107164*0.43约等于bmsql_history 表增加的数据量。

15:38:23,801 [Thread-577] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 409558.4

15:38:23,802 [Thread-577] INFO jTPCC : Term-00, Measured tpmTOTAL = 910367.52

15:38:23,802 [Thread-577] INFO jTPCC : Term-00, Session Start = 2020-12-30 15:28:23

15:38:23,802 [Thread-577] INFO jTPCC : Term-00, Session End = 2020-12-30 15:38:23

15:38:23,802 [Thread-577] INFO jTPCC : Term-00, Transaction Count = 9107164

错误提示：

执行测试脚本./runBenchmark.sh props.dm时报错：

15:05:12,112 [main] ERROR jTPCC : Term-00, This session ended with errors!

该问题为terminals参数设置为100 和dm.ini文件中MAX_SESSIONS参数为100发生冲突导致。

达梦数据库 - 新一代大型通用关系型数据库 | 达梦云适配中心

weixin_34938478

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
2
评论
DM8 TPCC测试

1.使用benchwarksql进行tpcc测试上传所需软件包并解压bms.tarbms5_index.sql bms5_table.sql bms.tar2.检查驱动版本../benchmarksql/lib/dm 下面是否是最新的驱动，如果没有直接到数据库下拷贝/opt/dmdbms/drivers/jdbc/DmJdbcDriver18.jar3.配置prop.dm文件来规划预创建表的数据量[root@DM02 run]$ vi props.dmdb=dmdri...
复制链接

扫一扫