TPC-C工具测试达梦数据库性能_tpcc测试

一、TPC-C 介绍

TPC-C 是一种衡量 OLTP 系统性能和可伸缩性的基准测试项目。它由一系列的 OLTP 工作流组成，包括查询，更新及队列式小批量事务在内的广泛数据库功能。它模拟了一个典型的 OLTP 应用环境中的活动，这些活动由一系列复杂的事务组成。TPC-C 工作流应该具备以下特性：

• 适当复杂的 OLTP 事务

• 在线和延迟事务执行模型

• 多用户

• 适当的系统和应用执行时间

• 大量的磁盘输入和输出

• 事务完整性（ACID）

• 随机的数据访问

• 数据库由各种大小，属性和关系的表组成。

二、模型介绍

TPC-C 测试用到的模型是一个大型的商品批发销售公司，它拥有若干个分布在不同区域的商品仓库。每个仓库负责为10个销售点供货，其中每个销售点为3000个客户提供服务，每个客户提交的订单中，平均每个订单有10项产品（5-15件），所有订单中约1%的产品在其直接所属的仓库中没有存货，必须由其他区域的仓库来供货。同时，每个仓库都要维护公司销售的100000种商品的库存记录。

TPC-C 有 9 张表和 5 种事务。

TPC-C 最大的有效的吞吐量（MQTh）的度量是每分钟处理的新订单数量，单位是 tpmC 。

MQTh 正常的范围为（active-warehouse*terminals-per-warehouse）的0.9倍至1.2倍之间。

为了得到较高的 MQTh ，可以根据实际测试结果预估，来调整 Active-warehouse 再次测试。

三、测试环境介绍

本次测试环境为vm虚拟机+DM8数据库，演示环境仅供学习使用。

服务器规格：

四、测试流程介绍

(1) 初始化数据库

(2) 建表、装载数据

(3) 扩库及日志和创建索引

(4) 数据冷备份

(5) 性能调优

(6) 配置TPCC工具进行测试

五、测试目

在硬件条件许可的情况下,模拟复杂OLTP应用环境中的活动，针对订单录入与销售环境测试每分钟商业事务吞吐量（tpmc），平均事务响应时间要求达到指标。在指定硬件环境下各数据库所能达到的最高水平。测试过程应满足TPC官方制定的TPC-C标准。测试结束时应对测试数据进行事务ACID检查。

六、测试指标

TPC-C使用三种性能和价格度量，其中性能由tpmC（transactionsper minute，tpm）衡量，Cpm）衡量，C指TPC中的C基准程序。它的定义是每分钟内系统处理的新订单个数。TPC-C还经常以系统性能价格比的方式体现，单位是$/tpmC，即以系统的总价格（单位是美元）/tpmC数值得出。因此tpmC分值越高，代表数据库性能越好。

七、测试步骤

1. 部署配置BenchmarkSQL 5.0工具

2. 执行SQL工具建表

[dmdba@dca run]$ ./runSQL.sh props.dm bms5_table.sql

# ------------------------------------------------------------

# Loading SQL file bms5_table.sql

# ------------------------------------------------------------

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 decimal(22,2),

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 decimal(22,2),

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. 执行数据装载工具装载数据

./runLoader.sh props.dm

[dmdba@dca run]$ ./runLoader.sh props.dm

Starting BenchmarkSQL LoadData

driver=dm.jdbc.driver.DmDriver

conn=jdbc:dm://127.0.0.1:5238

user=benchmarksql

password=***********

warehouses=10

loadWorkers=10

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 000: Loading ITEM done

Worker 000: Loading Warehouse 10

Worker 004: Loading Warehouse 4 done

Worker 000: Loading Warehouse 10 done

Worker 007: Loading Warehouse 7 done

Worker 001: Loading Warehouse 1 done

Worker 009: Loading Warehouse 9 done

Worker 002: Loading Warehouse 2 done

Worker 008: Loading Warehouse 8 done

Worker 006: Loading Warehouse 6 done

Worker 003: Loading Warehouse 3 done

Worker 005: Loading Warehouse 5 done

4. 执行测试

./runBenchmark.sh props.dm

[dmdba@dca run]$ ./runBenchmark.sh props.dm

12:18:08,615 [main] INFO jTPCC : Term-00,

12:18:08,618 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+

12:18:08,618 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0rc2

12:18:08,618 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+

12:18:08,618 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa

12:18:08,618 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier

12:18:08,621 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck

12:18:08,621 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+

12:18:08,621 [main] INFO jTPCC : Term-00,

12:18:08,621 [main] INFO jTPCC : Term-00, db=dm

12:18:08,621 [main] INFO jTPCC : Term-00, driver=dm.jdbc.driver.DmDriver

12:18:08,621 [main] INFO jTPCC : Term-00, conn=jdbc:dm://127.0.0.1:5238

12:18:08,621 [main] INFO jTPCC : Term-00, user=benchmarksql

12:18:08,621 [main] INFO jTPCC : Term-00,

12:18:08,621 [main] INFO jTPCC : Term-00, warehouses=10

12:18:08,621 [main] INFO jTPCC : Term-00, terminals=10

12:18:08,623 [main] INFO jTPCC : Term-00, runMins=2

12:18:08,623 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0

12:18:08,623 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true

12:18:08,623 [main] INFO jTPCC : Term-00,

12:18:08,623 [main] INFO jTPCC : Term-00, newOrderWeight=45

12:18:08,623 [main] INFO jTPCC : Term-00, paymentWeight=43

12:18:08,623 [main] INFO jTPCC : Term-00, orderStatusWeight=4

12:18:08,623 [main] INFO jTPCC : Term-00, deliveryWeight=4

12:18:08,623 [main] INFO jTPCC : Term-00, stockLevelWeight=4

12:18:08,623 [main] INFO jTPCC : Term-00,

12:18:08,624 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS

12:18:08,624 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py

12:18:08,624 [main] INFO jTPCC : Term-00,

12:18:08,636 [main] INFO jTPCC : Term-00, copied props.dm to my_result_2023-02-25_121808/run.properties

12:18:08,636 [main] INFO jTPCC : Term-00, created my_result_2023-02-25_121808/data/runInfo.csv for runID 132

12:18:08,637 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2023-02-25_121808/data/result.csv

12:18:08,637 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py

12:18:08,638 [main] INFO jTPCC : Term-00, osCollectorInterval=1

12:18:08,638 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null

12:18:08,638 [main] INFO jTPCC : Term-00, osCollectorDevices=net_eth0 blk_sda

12:18:08,706 [main] INFO jTPCC : Term-00,

Traceback (most recent call last):

File "<stdin>", line 299, in <module>

File "<stdin>", line 90, in main

File "<stdin>", line 269, in initNetDevice

IOError: [Errno 2] No such file or directory: '/sys/class/net/eth0/statistics/rx_packets'

12:18:08,771 [Thread-0] ERROR OSCollector$CollectData : OSCollector, unexpected EOF while reading from external helper process

12:18:08,826 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 131

12:18:08,826 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 63

12:18:08,827 [main] INFO jTPCC : Term-00,

Term-00, Running Average tpmTOTAL: 49164.72 Current tpmTOTAL: 655680 Memory Usage: 23MB / 93MB

12:20:09,027 [Thread-10] INFO jTPCC : Term-00,

12:20:09,028 [Thread-10] INFO jTPCC : Term-00,

12:20:09,028 [Thread-10] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 22032.99

12:20:09,028 [Thread-10] INFO jTPCC : Term-00, Measured tpmTOTAL = 49157.71

12:20:09,028 [Thread-10] INFO jTPCC : Term-00, Session Start = 2023-02-25 12:18:08

12:20:09,029 [Thread-10] INFO jTPCC : Term-00, Session End = 2023-02-25 12:20:09

12:20:09,029 [Thread-10] INFO jTPCC : Term-00, Transaction Count = 98339

达梦学习社区：https://eco.dameng.com