完成 TPC-C 测试

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

TPC-C 概述

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

TPCC diagram

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

开始前准备

安装并启动 MatrixOne

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

克隆 mo-tpcc 仓库到本地

  1. git clone https://github.com/matrixorigin/mo-tpcc.git

步骤

步骤简介

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

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

1. 配置 props.mo 文件

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

  1. db=mo
  2. driver=com.mysql.cj.jdbc.Driver
  3. 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
  4. user=root
  5. password=111
  7. //the number of warehouse
  8. warehouses=10
  9. loadWorkers=4
  11. //the num of terminals that will simultaneously run
  12. //must be less than warehouses*10
  13. terminals=1
  14. //To run specified transactions per terminal- runMins must equal zero
  15. runTxnsPerTerminal=0
  16. //To run for specified minutes- runTxnsPerTerminal must equal zero
  17. runMins=1
  18. //Number of total transactions per minute
  19. limitTxnsPerMin=0

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

2. 创建 TPCC 数据库和表

打开一个新的终端，执行下面的代码：

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

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

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

执行完成后，输出结果示例如下：

  1. # ------------------------------------------------------------
  2. # Loading SQL file ./sql/tableCreates.sql
  3. # ------------------------------------------------------------
  4. drop database if exists tpcc;
  5. create database if not exists tpcc;
  6. use tpcc;
  7. create table bmsql_config (
  8. cfg_name    varchar(30) primary key,
  9. cfg_value   varchar(50)
  10. );
  11. create table bmsql_warehouse (
  12. w_id        integer   not null,
  13. w_ytd       decimal(12,2),
  14. w_tax       decimal(4,4),
  15. w_name      varchar(10),
  16. w_street_1  varchar(20),
  17. w_street_2  varchar(20),
  18. w_city      varchar(20),
  19. w_state     char(2),
  20. w_zip       char(9),
  21. primary key (w_id)
  22. ) PARTITION BY KEY(w_id);
  23. create table bmsql_district (
  24. d_w_id       integer       not null,
  25. d_id         integer       not null,
  26. d_ytd        decimal(12,2),
  27. d_tax        decimal(4,4),
  28. d_next_o_id  integer,
  29. d_name       varchar(10),
  30. d_street_1   varchar(20),
  31. d_street_2   varchar(20),
  32. d_city       varchar(20),
  33. d_state      char(2),
  34. d_zip        char(9),
  35. primary key (d_w_id, d_id)
  36. ) PARTITION BY KEY(d_w_id);
  37. create table bmsql_customer (
  38. c_w_id         integer        not null,
  39. c_d_id         integer        not null,
  40. c_id           integer        not null,
  41. c_discount     decimal(4,4),
  42. c_credit       char(2),
  43. c_last         varchar(16),
  44. c_first        varchar(16),
  45. c_credit_lim   decimal(12,2),
  46. c_balance      decimal(12,2),
  47. c_ytd_payment  decimal(12,2),
  48. c_payment_cnt  integer,
  49. c_delivery_cnt integer,
  50. c_street_1     varchar(20),
  51. c_street_2     varchar(20),
  52. c_city         varchar(20),
  53. c_state        char(2),
  54. c_zip          char(9),
  55. c_phone        char(16),
  56. c_since        timestamp,
  57. c_middle       char(2),
  58. c_data         varchar(500),
  59. primary key (c_w_id, c_d_id, c_id)
  60. ) PARTITION BY KEY(c_w_id);
  61. create table bmsql_history (
  62. hist_id  integer auto_increment,
  63. h_c_id   integer,
  64. h_c_d_id integer,
  65. h_c_w_id integer,
  66. h_d_id   integer,
  67. h_w_id   integer,
  68. h_date   timestamp,
  69. h_amount decimal(6,2),
  70. h_data   varchar(24),
  71. primary key (hist_id)
  72. );
  73. create table bmsql_new_order (
  74. no_w_id  integer   not null,
  75. no_d_id  integer   not null,
  76. no_o_id  integer   not null,
  77. primary key (no_w_id, no_d_id, no_o_id)
  78. ) PARTITION BY KEY(no_w_id);
  79. create table bmsql_oorder (
  80. o_w_id       integer      not null,
  81. o_d_id       integer      not null,
  82. o_id         integer      not null,
  83. o_c_id       integer,
  84. o_carrier_id integer,
  85. o_ol_cnt     integer,
  86. o_all_local  integer,
  87. o_entry_d    timestamp,
  88. primary key (o_w_id, o_d_id, o_id)
  89. ) PARTITION BY KEY(o_w_id);
  90. create table bmsql_order_line (
  91. ol_w_id         integer   not null,
  92. ol_d_id         integer   not null,
  93. ol_o_id         integer   not null,
  94. ol_number       integer   not null,
  95. ol_i_id         integer   not null,
  96. ol_delivery_d   timestamp,
  97. ol_amount       decimal(6,2),
  98. ol_supply_w_id  integer,
  99. ol_quantity     integer,
  100. ol_dist_info    char(24),
  101. primary key (ol_w_id, ol_d_id, ol_o_id, ol_number)
  102. ) PARTITION BY KEY(ol_w_id);
  103. create table bmsql_item (
  104. i_id     integer      not null,
  105. i_name   varchar(24),
  106. i_price  decimal(5,2),
  107. i_data   varchar(50),
  108. i_im_id  integer,
  109. primary key (i_id)
  110. ) PARTITION BY KEY(i_id);
  111. create table bmsql_stock (
  112. s_w_id       integer       not null,
  113. s_i_id       integer       not null,
  114. s_quantity   integer,
  115. s_ytd        integer,
  116. s_order_cnt  integer,
  117. s_remote_cnt integer,
  118. s_data       varchar(50),
  119. s_dist_01    char(24),
  120. s_dist_02    char(24),
  121. s_dist_03    char(24),
  122. s_dist_04    char(24),
  123. s_dist_05    char(24),
  124. s_dist_06    char(24),
  125. s_dist_07    char(24),
  126. s_dist_08    char(24),
  127. s_dist_09    char(24),
  128. s_dist_10    char(24),
  129. primary key (s_w_id, s_i_id)
  130. ) PARTITION BY KEY(s_w_id);

3. 生成 TPCC 数据集

执行下面的代码，生成 TPCC 数据集：

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

执行完成后，输出结果示例如下：

  1. Starting BenchmarkSQL LoadData
  3. props.mo
  4. driver=com.mysql.cj.jdbc.Driver
  5. 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
  6. user=root
  7. password=***********
  8. warehouses=10
  9. loadWorkers=4
  10. fileLocation (not defined)
  11. csvNullValue (not defined - using default '')
  13. Worker 000: Loading ITEM
  14. Worker 001: Loading Warehouse      1
  15. Worker 002: Loading Warehouse      2
  16. Worker 003: Loading Warehouse      3
  17. Worker 000: Loading ITEM done
  18. Worker 000: Loading Warehouse      4
  19. Worker 003: Loading Warehouse      3 done
  20. Worker 003: Loading Warehouse      5
  21. Worker 001: Loading Warehouse      1 done
  22. Worker 001: Loading Warehouse      6
  23. Worker 002: Loading Warehouse      2 done
  24. Worker 002: Loading Warehouse      7
  25. Worker 000: Loading Warehouse      4 done
  26. Worker 000: Loading Warehouse      8
  27. Worker 003: Loading Warehouse      5 done
  28. Worker 003: Loading Warehouse      9
  29. Worker 000: Loading Warehouse      8 done
  30. Worker 000: Loading Warehouse     10
  31. Worker 002: Loading Warehouse      7 done
  32. Worker 001: Loading Warehouse      6 done
  33. Worker 000: Loading Warehouse     10 done
  34. Worker 003: Loading Warehouse      9 done

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

  1. config.csv
  2. cust-hist.csv
  3. customer.csv
  4. district.csv
  5. item.csv
  6. new-order.csv
  7. order-line.csv
  8. order.csv
  9. stock.csv
  10. warehouse.csv

4. 将 TPCC 数据加载到 MatrixOne

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

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

5. 运行 TPCC 测试

执行下面的代码，运行 TPCC 测试：

  1. ./runBenchmark.sh props.mo

执行完成后，输出结果示例如下：

  1. .:./lib/*
  2. 2022-12-22 21:15:35 INFO  jTPCC:78 - Term-00,
  3. 2022-12-22 21:15:35 INFO  jTPCC:79 - Term-00, +-------------------------------------------------------------+
  4. 2022-12-22 21:15:35 INFO  jTPCC:80 - Term-00,      BenchmarkSQL v5.0
  5. 2022-12-22 21:15:35 INFO  jTPCC:81 - Term-00, +-------------------------------------------------------------+
  6. 2022-12-22 21:15:35 INFO  jTPCC:82 - Term-00,  (c) 2003, Raul Barbosa
  7. 2022-12-22 21:15:35 INFO  jTPCC:83 - Term-00,  (c) 2004-2016, Denis Lussier
  8. 2022-12-22 21:15:35 INFO  jTPCC:84 - Term-00,  (c) 2016, Jan Wieck
  9. 2022-12-22 21:15:35 INFO  jTPCC:85 - Term-00, +-------------------------------------------------------------+
  10. 2022-12-22 21:15:35 INFO  jTPCC:86 - Term-00,
  11. 2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, db=mo
  12. 2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, driver=com.mysql.cj.jdbc.Driver
  13. 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
  14. 2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, user=root
  15. 2022-12-22 21:15:35 INFO  jTPCC:93 - Term-00,
  16. 2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, warehouses=10
  17. 2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, terminals=1
  18. 2022-12-22 21:15:35 INFO  jTPCC:100 - Term-00, runMins=1
  19. 2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, limitTxnsPerMin=0
  20. 2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, terminalWarehouseFixed=false
  21. 2022-12-22 21:15:35 INFO  jTPCC:108 - Term-00,
  22. 2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, newOrderWeight=45
  23. 2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, paymentWeight=43
  24. 2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, orderStatusWeight=4
  25. 2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, deliveryWeight=4
  26. 2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, stockLevelWeight=4
  27. 2022-12-22 21:15:35 INFO  jTPCC:115 - Term-00,
  28. 2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
  29. 2022-12-22 21:15:35 INFO  jTPCC:63 - Term-00, osCollectorScript=null
  30. 2022-12-22 21:15:35 INFO  jTPCC:119 - Term-00,
  31. 2022-12-22 21:15:35 INFO  jTPCC:710 - Term-00, Loading database driver: 'com.mysql.cj.jdbc.Driver'...
  32. 2022-12-22 21:15:35 INFO  jTPCC:219 - Term-00, copied props.mo to my_result_2022-12-22_211535/run.properties
  33. 2022-12-22 21:15:35 INFO  jTPCC:239 - Term-00, created my_result_2022-12-22_211535/data/runInfo.csv for runID 1
  34. 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
  35. 2022-12-22 21:15:35 INFO  jTPCC:268 - Term-00,
  36. 2022-12-22 21:15:36 INFO  jTPCC:324 - Term-00, C value for C_LAST during load: 28
  37. 2022-12-22 21:15:36 INFO  jTPCC:325 - Term-00, C value for C_LAST this run:    132
  38. 2022-12-22 21:15:36 INFO  jTPCC:326 - Term-00,
  39. 2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Session started!   Memory Usage: 17MB / 245MB          
  40. 2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Creating 1 terminal(s) with -1 transaction(s) per terminal...
  41. 2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Terminal Warehouse is NOT fixed
  42. 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
  43. 2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Number of Terminals      1
  44. 2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Creating database connection for Term-01...
  45. 2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Term-01  7
  46. 2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Transaction      Weight
  47. 2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, % New-Order      45
  48. 2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, % Payment        43
  49. 2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, % Order-Status   4
  50. 2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, % Delivery       4
  51. 2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, % Stock-Level    4
  52. 2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Transaction Number       Terminal        Type    Execution Time (ms)             Comment
  53. 2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Created 1 terminal(s) successfully!
  54. 2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, Starting all terminals...
  55. 2022-12-22 21:15:36 INFO  jTPCC:710 - Term-00, All terminals started executing 2022-12-22 21:15:36
  56. 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...
  57. 2022-12-22 21:16:42 INFO  jTPCC:710 - Term-00, The time limit has been reached.: 21MB / 245MB          
  58. 2022-12-22 21:16:42 INFO  jTPCC:710 - Term-00, Signalling all terminals to stop...
  59. 2022-12-22 21:16:42 INFO  jTPCCTerminal:350 - Term-01,
  60. 2022-12-22 21:16:42 INFO  jTPCCTerminal:350 - Term-01, Terminal received stop signal!
  61. 2022-12-22 21:16:42 INFO  jTPCCTerminal:350 - Term-01, Finishing current transaction before exit...
  62. 2022-12-22 21:16:42 INFO  jTPCC:710 - Term-00, Waiting for all active transactions to end...
  63. 2022-12-22 21:16:42 INFO  jTPCCTerminal:350 - Term-01, OTAL: 24    Memory Usage: 22MB / 245MB          
  64. 2022-12-22 21:16:42 INFO  jTPCCTerminal:350 - Term-01, Closing statement and connection...
  65. 2022-12-22 21:16:42 INFO  jTPCCTerminal:350 - Term-01,
  66. 2022-12-22 21:16:42 INFO  jTPCCTerminal:350 - Term-01, Terminal 'Term-01' finished after 0 transaction(s).
  67. 2022-12-22 21:16:42 INFO  jTPCC:710 - Term-00, All terminals finished executing 2022-12-22 21:16:42
  69. 2022-12-22 21:16:42 INFO  jTPCC:694 - Term-00,
  70. 2022-12-22 21:16:42 INFO  jTPCC:695 - Term-00,
  71. 2022-12-22 21:16:42 INFO  jTPCC:696 - Term-00, Measured tpmC (NewOrders) = 2.74
  72. 2022-12-22 21:16:42 INFO  jTPCC:697 - Term-00, Measured tpmTOTAL = 3.66
  73. 2022-12-22 21:16:42 INFO  jTPCC:698 - Term-00, Measured tpmE (ErrorCount) = 0.0
  74. 2022-12-22 21:16:42 INFO  jTPCC:699 - Term-00, Session Start     = 2022-12-22 21:15:36
  75. 2022-12-22 21:16:42 INFO  jTPCC:700 - Term-00, Session End       = 2022-12-22 21:16:42
  76. 2022-12-22 21:16:42 INFO  jTPCC:701 - Term-00, Transaction Count = 3
  77. 2022-12-22 21:16:42 INFO  jTPCC:702 - Term-00, Transaction Error = 0
  78. 2022-12-22 21:16:42 INFO  jTPCC:703 - Term-00, Transaction NewOrders = 3
  79. 2022-12-22 21:16:42 INFO  jTPCC:710 - Term-00, Session finished!

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