我的书签
添加书签
移除书签完成 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 仓库到本地
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（每分钟事务数）的值。
