完成 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
  6. //the number of warehouse
  7. warehouses=10
  8. loadWorkers=4
  9. //the num of terminals that will simultaneously run
  10. //must be less than warehouses*10
  11. terminals=1
  12. //To run specified transactions per terminal- runMins must equal zero
  13. runTxnsPerTerminal=0
  14. //To run for specified minutes- runTxnsPerTerminal must equal zero
  15. runMins=1
  16. //Number of total transactions per minute
  17. 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
  2. props.mo
  3. driver=com.mysql.cj.jdbc.Driver
  4. 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
  5. user=root
  6. password=***********
  7. warehouses=10
  8. loadWorkers=4
  9. fileLocation (not defined)
  10. csvNullValue (not defined - using default '')
  11. Worker 000: Loading ITEM
  12. Worker 001: Loading Warehouse 1
  13. Worker 002: Loading Warehouse 2
  14. Worker 003: Loading Warehouse 3
  15. Worker 000: Loading ITEM done
  16. Worker 000: Loading Warehouse 4
  17. Worker 003: Loading Warehouse 3 done
  18. Worker 003: Loading Warehouse 5
  19. Worker 001: Loading Warehouse 1 done
  20. Worker 001: Loading Warehouse 6
  21. Worker 002: Loading Warehouse 2 done
  22. Worker 002: Loading Warehouse 7
  23. Worker 000: Loading Warehouse 4 done
  24. Worker 000: Loading Warehouse 8
  25. Worker 003: Loading Warehouse 5 done
  26. Worker 003: Loading Warehouse 9
  27. Worker 000: Loading Warehouse 8 done
  28. Worker 000: Loading Warehouse 10
  29. Worker 002: Loading Warehouse 7 done
  30. Worker 001: Loading Warehouse 6 done
  31. Worker 000: Loading Warehouse 10 done
  32. 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
  68. 2022-12-22 21:16:42 INFO jTPCC:694 - Term-00,
  69. 2022-12-22 21:16:42 INFO jTPCC:695 - Term-00,
  70. 2022-12-22 21:16:42 INFO jTPCC:696 - Term-00, Measured tpmC (NewOrders) = 2.74
  71. 2022-12-22 21:16:42 INFO jTPCC:697 - Term-00, Measured tpmTOTAL = 3.66
  72. 2022-12-22 21:16:42 INFO jTPCC:698 - Term-00, Measured tpmE (ErrorCount) = 0.0
  73. 2022-12-22 21:16:42 INFO jTPCC:699 - Term-00, Session Start = 2022-12-22 21:15:36
  74. 2022-12-22 21:16:42 INFO jTPCC:700 - Term-00, Session End = 2022-12-22 21:16:42
  75. 2022-12-22 21:16:42 INFO jTPCC:701 - Term-00, Transaction Count = 3
  76. 2022-12-22 21:16:42 INFO jTPCC:702 - Term-00, Transaction Error = 0
  77. 2022-12-22 21:16:42 INFO jTPCC:703 - Term-00, Transaction NewOrders = 3
  78. 2022-12-22 21:16:42 INFO jTPCC:710 - Term-00, Session finished!

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