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
  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

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
  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

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
  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!

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