Skip to content

完成 TPC-C 测试

通过阅读本教程,你将学习如何使用 MatrixOne 完成 TPC-C 测试。

TPC-C 概述

TPC-C 是一种衡量在线事务处理(OLTP)系统性能和可伸缩性的基准测试规范。TPC-C 模拟了一个比较有代表意义的 OLTP 应用环境:在线订单处理系统。TPC-C 基准测试中使用的数据库由 Warehouse、Customer、Order、Item 等九个表组成(参见下图)。除 Item 表外,每条记录都以每个 Warehouse 为基础进行填充,并且仓库的数量按比例配置。

TPC-C 需要处理的交易事务有五种:NewOrder、Payment、OrderStatus、Delivery 和 StockLevel。TPC-C 定义了每种事务的请求率,其中几乎 90% 请求率的事务为写密集型的 NewOrder 和 Payment。TPC-C 事务主要访问单个(本地)仓库,但大约 10% 的事务与另一个(远程)仓库交互。

开始前准备

安装并启动 MatrixOne

确保你已经完成了单机部署 MatrixOne

克隆 mo-tpcc 仓库到本地

git clone https://github.com/matrixorigin/mo-tpcc.git
或者
git clone https://githubfast.com/matrixorigin/mo-tpcc.git

步骤

步骤简介

本节将介绍如何生成 TPCC 数据、创建 TPCC 表,并将数据加载到 MatrixOne 以及运行 TPCC 测试。

现在你可以按照以下描述逐步执行命令。

1. 配置 props.mo 文件

克隆 mo-tpcc 仓库到本地后,在本地打开 mo-tpcc 文件夹,按照下面的配置项,修改文件夹中的 props.mo 文件。数据仓库的数量可以通过该文件中的 warehouse=XX 行进行配置。

db=mo
driver=com.mysql.cj.jdbc.Driver
conn=jdbc:mysql://127.0.0.1:6001/tpcc?characterSetResults=utf8&continueBatchOnError=false&useServerPrepStmts=true&alwaysSendSetIsolation=false&useLocalSessionState=true&zeroDateTimeBehavior=CONVERT_TO_NULL&failoverReadOnly=false&serverTimezone=Asia/Shanghai&enabledTLSProtocols=TLSv1.2&useSSL=false
user=root
password=111

//the number of warehouse
warehouses=10
loadWorkers=4

//the num of terminals that will simultaneously run
//must be less than warehouses*10
terminals=1
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=1
//Number of total transactions per minute
limitTxnsPerMin=0

修改完成后,保存 props.mo 文件。

2. 创建 TPCC 数据库和表

打开一个新的终端,执行下面的代码:

cd mo-tpcc
./runSQL.sh props.mo tableCreates

Note: 如果在运行 ./runSQL.sh props.mo tableCreates 时产生 java:command not found 报错,那么你需要在你计算机上安装或重新安装 Java 和 JDK。

上面的代码表示,进入到 mo-tpcc 文件夹目录,执行代码创建完成 TPCC 数据库和表。

执行完成后,输出结果示例如下:

# ------------------------------------------------------------
# Loading SQL file ./sql/tableCreates.sql
# ------------------------------------------------------------
drop database if exists tpcc;
create database if not exists tpcc;
use tpcc;
create table bmsql_config (
cfg_name    varchar(30) primary key,
cfg_value   varchar(50)
);
create table 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),
primary key (w_id)
) PARTITION BY KEY(w_id);
create table 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),
primary key (d_w_id, d_id)
) PARTITION BY KEY(d_w_id);
create table 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,
c_middle       char(2),
c_data         varchar(500),
primary key (c_w_id, c_d_id, c_id)
) PARTITION BY KEY(c_w_id);
create table bmsql_history (
hist_id  integer auto_increment,
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 decimal(6,2),
h_data   varchar(24),
primary key (hist_id)
);
create table bmsql_new_order (
no_w_id  integer   not null,
no_d_id  integer   not null,
no_o_id  integer   not null,
primary key (no_w_id, no_d_id, no_o_id)
) PARTITION BY KEY(no_w_id);
create table 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,
primary key (o_w_id, o_d_id, o_id)
) PARTITION BY KEY(o_w_id);
create table 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       decimal(6,2),
ol_supply_w_id  integer,
ol_quantity     integer,
ol_dist_info    char(24),
primary key (ol_w_id, ol_d_id, ol_o_id, ol_number)
) PARTITION BY KEY(ol_w_id);
create table bmsql_item (
i_id     integer      not null,
i_name   varchar(24),
i_price  decimal(5,2),
i_data   varchar(50),
i_im_id  integer,
primary key (i_id)
) PARTITION BY KEY(i_id);
create table 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),
primary key (s_w_id, s_i_id)
) PARTITION BY KEY(s_w_id);

3. 生成 TPCC 数据集

执行下面的代码,生成 TPCC 数据集:

./runLoader.sh props.mo filelocation /yourpath/

执行完成后,输出结果示例如下:

Starting BenchmarkSQL LoadData

props.mo
driver=com.mysql.cj.jdbc.Driver
conn=jdbc:mysql://127.0.0.1:6001/tpcc?characterSetResults=utf8&continueBatchOnError=false&useServerPrepStmts=true&alwaysSendSetIsolation=false&useLocalSessionState=true&zeroDateTimeBehavior=CONVERT_TO_NULL&failoverReadOnly=false&serverTimezone=Asia/Shanghai&enabledTLSProtocols=TLSv1.2&useSSL=false
user=root
password=***********
warehouses=10
loadWorkers=4
fileLocation (not defined)
csvNullValue (not defined - using default '')

Worker 000: Loading ITEM
Worker 001: Loading Warehouse      1
Worker 002: Loading Warehouse      2
Worker 003: Loading Warehouse      3
Worker 000: Loading ITEM done
Worker 000: Loading Warehouse      4
Worker 003: Loading Warehouse      3 done
Worker 003: Loading Warehouse      5
Worker 001: Loading Warehouse      1 done
Worker 001: Loading Warehouse      6
Worker 002: Loading Warehouse      2 done
Worker 002: Loading Warehouse      7
Worker 000: Loading Warehouse      4 done
Worker 000: Loading Warehouse      8
Worker 003: Loading Warehouse      5 done
Worker 003: Loading Warehouse      9
Worker 000: Loading Warehouse      8 done
Worker 000: Loading Warehouse     10
Worker 002: Loading Warehouse      7 done
Worker 001: Loading Warehouse      6 done
Worker 000: Loading Warehouse     10 done
Worker 003: Loading Warehouse      9 done

你会在你所指定路径中找到 10 个 csv 文件,每个 csv 文件都会映射到第 2 步中创建的表中。

config.csv
cust-hist.csv
customer.csv
district.csv
item.csv
new-order.csv
order-line.csv
order.csv
stock.csv
warehouse.csv

4. 将 TPCC 数据加载到 MatrixOne

使用 MySQL 客户端连接到 MatrixOne 并执行以下语句将 csv 文件加载到 MatrixOne 中。

mysql> load data infile '/yourpath/config.csv' INTO TABLE bmsql_config FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n";
load data infile '/yourpath/cust-hist.csv' INTO TABLE bmsql_history FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n";
load data infile '/yourpath/data/customer.csv' INTO TABLE bmsql_customer FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n";
load data infile '/yourpath/data/district.csv' INTO TABLE bmsql_district FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n";
load data infile '/yourpath/data/warehouse.csv' INTO TABLE bmsql_warehouse FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n";
load data infile '/yourpath/item.csv' INTO TABLE bmsql_item FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n";
load data infile '/yourpath/new-order.csv' INTO TABLE bmsql_new_order FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n";
load data infile '/yourpath/order-line.csv' INTO TABLE bmsql_order_line FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n";
load data infile '/yourpath/stock.csv' INTO TABLE bmsql_stock FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n";
load data infile '/yourpath/order.csv' INTO TABLE bmsql_oorder FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n";

5. 运行 TPCC 测试

执行下面的代码,运行 TPCC 测试:

./runBenchmark.sh props.mo

执行完成后,输出结果示例如下:

.:./lib/*
2022-12-22 21:15:35 INFO  jTPCC:78 - Term-00,
2022-12-22 21:15:35 INFO  jTPCC:79 - Term-00, +-------------------------------------------------------------+
2022-12-22 21:15:35 INFO  jTPCC:80 - Term-00,      BenchmarkSQL v5.0
2022-12-22 21:15:35 INFO  jTPCC:81 - Term-00, +-------------------------------------------------------------+
2022-12-22 21:15:35 INFO  jTPCC:82 - Term-00,  (c) 2003, Raul Barbosa
2022-12-22 21:15:35 INFO  jTPCC:83 - Term-00,  (c) 2004-2016, Denis Lussier
2022-12-22 21:15:35 INFO  jTPCC:84 - Term-00,  (c) 2016, Jan Wieck
2022-12-22 21:15:35 INFO  jTPCC:85 - Term-00, +-------------------------------------------------------------+
2022-12-22 21:15:35 INFO  jTPCC:86 - Term-00,
2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, db=mo
2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, driver=com.mysql.cj.jdbc.Driver
2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, conn=jdbc:mysql://127.0.0.1:6001/tpcc?characterSetResults=utf8&continueBatchOnError=false&useServerPrepStmts=true&alwaysSendSetIsolation=false&useLocalSessionState=true&zeroDateTimeBehavior=CONVERT_TO_NULL&failoverReadOnly=false&serverTimezone=Asia/Shanghai&enabledTLSProtocols=TLSv1.2&useSSL=false
2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, user=root
2022-12-22 21:15:35 INFO  jTPCC:93 - Term-00,
2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, warehouses=10
2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, terminals=1
2022-12-22 21:15:35 INFO  jTPCC:100 - Term-00, runMins=1
2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, limitTxnsPerMin=0
2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, terminalWarehouseFixed=false
2022-12-22 21:15:35 INFO  jTPCC:108 - Term-00,
2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, newOrderWeight=45
2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, paymentWeight=43
2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, orderStatusWeight=4
2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, deliveryWeight=4
2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, stockLevelWeight=4
2022-12-22 21:15:35 INFO  jTPCC:115 - Term-00,
2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, osCollectorScript=null
2022-12-22 21:15:35 INFO  jTPCC:119 - Term-00,
2022-12-22 21:15:35 INFO  jTPCC:710 - Term-00, Loading database driver: 'com.mysql.cj.jdbc.Driver'...
2022-12-22 21:15:35 INFO  jTPCC:219 - Term-00, copied props.mo to my_result_2022-12-22_211535/run.properties
2022-12-22 21:15:35 INFO  jTPCC:239 - Term-00, created my_result_2022-12-22_211535/data/runInfo.csv for runID 1
2022-12-22 21:15:35 INFO  jTPCC:255 - Term-00, writing per transaction results to my_result_2022-12-22_211535/data/result.csv
2022-12-22 21:15:35 INFO  jTPCC:268 - Term-00,
2022-12-22 21:15:36 INFO  jTPCC:324 - Term-00, C value for C_LAST during load: 28
2022-12-22 21:15:36 INFO  jTPCC:325 - Term-00, C value for C_LAST this run:    132
2022-12-22 21:15:36 INFO  jTPCC:326 - Term-00,
2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Session started!   Memory Usage: 17MB / 245MB          
2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Creating 1 terminal(s) with -1 transaction(s) per terminal...
2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Terminal Warehouse is NOT fixed
2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Transaction Weights: 45% New-Order, 43% Payment, 4% Order-Status, 4% Delivery, 4% Stock-Level
2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Number of Terminals      1
2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Creating database connection for Term-01...
2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Term-01  7
2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Transaction      Weight
2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, % New-Order      45
2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, % Payment        43
2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, % Order-Status   4
2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, % Delivery       4
2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, % Stock-Level    4
2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Transaction Number       Terminal        Type    Execution Time (ms)             Comment
2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Created 1 terminal(s) successfully!
2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Starting all terminals...
2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, All terminals started executing 2022-12-22 21:15:36
Term-00, Running Average tpmTOTAL: 60000.00    Current tpmTOTAL: 12    Memory Usage: 19MB / 245MB   2022-12-22 21:15:36 INFO  jTPCCTerminal:350 - Term-01, Executing for a limited time...
2022-12-22 21:16:42 INFO  jTPCC:710 - Term-00, The time limit has been reached.: 21MB / 245MB          
2022-12-22 21:16:42 INFO  jTPCC:710 - Term-00, Signalling all terminals to stop...
2022-12-22 21:16:42 INFO  jTPCCTerminal:350 - Term-01,
2022-12-22 21:16:42 INFO  jTPCCTerminal:350 - Term-01, Terminal received stop signal!
2022-12-22 21:16:42 INFO  jTPCCTerminal:350 - Term-01, Finishing current transaction before exit...
2022-12-22 21:16:42 INFO  jTPCC:710 - Term-00, Waiting for all active transactions to end...
2022-12-22 21:16:42 INFO  jTPCCTerminal:350 - Term-01, OTAL: 24    Memory Usage: 22MB / 245MB          
2022-12-22 21:16:42 INFO  jTPCCTerminal:350 - Term-01, Closing statement and connection...
2022-12-22 21:16:42 INFO  jTPCCTerminal:350 - Term-01,
2022-12-22 21:16:42 INFO  jTPCCTerminal:350 - Term-01, Terminal 'Term-01' finished after 0 transaction(s).
2022-12-22 21:16:42 INFO  jTPCC:710 - Term-00, All terminals finished executing 2022-12-22 21:16:42

2022-12-22 21:16:42 INFO  jTPCC:694 - Term-00,
2022-12-22 21:16:42 INFO  jTPCC:695 - Term-00,
2022-12-22 21:16:42 INFO  jTPCC:696 - Term-00, Measured tpmC (NewOrders) = 2.74
2022-12-22 21:16:42 INFO  jTPCC:697 - Term-00, Measured tpmTOTAL = 3.66
2022-12-22 21:16:42 INFO  jTPCC:698 - Term-00, Measured tpmE (ErrorCount) = 0.0
2022-12-22 21:16:42 INFO  jTPCC:699 - Term-00, Session Start     = 2022-12-22 21:15:36
2022-12-22 21:16:42 INFO  jTPCC:700 - Term-00, Session End       = 2022-12-22 21:16:42
2022-12-22 21:16:42 INFO  jTPCC:701 - Term-00, Transaction Count = 3
2022-12-22 21:16:42 INFO  jTPCC:702 - Term-00, Transaction Error = 0
2022-12-22 21:16:42 INFO  jTPCC:703 - Term-00, Transaction NewOrders = 3
2022-12-22 21:16:42 INFO  jTPCC:710 - Term-00, Session finished!

由上面的结果可以得到 tpmC(每分钟事务数)的值。