Complete a TPC-C Test with MatrixOne

By walking through this tutorial, you’ll learn how to complete a TPC-C Test with MatrixOne.

TPC-C Overview

TPC-C is an industry-standard benchmark for OLTP databases. TPC-C models a warehouse-centric order processing application, and the database used in the TPC-C benchmark consists of nine tables, such as Warehouse, Customer, Order, Item, and so on (See the below ER diagram). Except for the item table, each record is populated on a per-warehouse basis, and the number of warehouses can be configurable as a scale factor.

TPCC diagram

TPC-C has five transaction types: NewOrder, Payment, OrderStatus, Delivery, and StockLevel. The request rate of each transaction is defined in the specification, and almost 90% of transactions are NewOrder and Payment, which are write-intensive. TPC-C transactions mostly access a single (local) warehouse, but about 10% of transactions interact with another (remote) warehouse.

Before you start

Make sure you have already Deployed standalone MatrixOne.

Clone mo-tpcc repository

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

Steps

Introduction

This section will teach you how to generate the TPCC data, create TPCC tables, load data to MatrixOne, and run TPCC.

Now you can execute commands step by step as the following descriptions.

1. Configure the props.mo file

After the mo-tpch repository is cloned, open the mo-tpcc directory, and modify the configuration items of the props.mo file. The number of warehouses can be configurable by the warehouse=XX row in this file.

  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

After the modifications are complete, save the props.mo file.

2. Create TPCC database and tables

Open a new terminal window, execute the following command:

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

Npte: If you get an error like java:command not found while running ./runSQL.sh props.mo tableCreates, install or reinstall Java and the JDK on your computer.

The above code means to enter the mo-tpch directory and create the TPCC database and table.

The following is an example of the command output:

  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. Generate TPCC data

To generate the TPCC data execute the following command:

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

The following is an example of the command output:

  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

You will find in your designated path 10 csv files. Each csv file maps to a table created in the second step.

  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. Load TPCC data to MatrixOne

Use MySQL client to connect to MatrixOne and execute the following statements to load the csv files into 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. Run TPCC test

To run the TPCC test, execute the following command:

  1. ./runBenchmark.sh props.mo

The following is an example of the command output:

  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!

The value of tpmC(transactions per minute) is given in the result.