Run the TPC-C benchmark test in OceanBase Database
The TPC-C benchmark test is one of the performance test standards defined by the Transaction Processing Performance Council (TPC). Since its release, the TPC-C benchmark test has become an important reference for performance tests in the database industry. Major database vendors around the world submit their test results to the TPC from time to time, expecting to rank higher in the TPC-C benchmark test. OceanBase Database set new world records in the TPC-C benchmark test twice in 2019 and 2020 as the first distributed relational database in the list.
This topic describes how to run the TPC-C benchmark test in OceanBase Database to experience its online transaction processing (OLTP) capability.
TPC-C Overview
Database model
The TPC-C benchmark test specifies the initial state of the database under test, which is the rules for generating data in the database. According to the rules, the ITEM table contains 100,000 types of commodities, and the number of warehouses can be adjusted. Assuming that the WAREHOUSE table contains W records, then:
- The STOCK table must contain W × 100,000 records, indicating that each warehouse corresponds to the stock data of 100,000 types of commodities.
- The DISTRICT table must contain W × 10 records, indicating that each warehouse serves 10 districts.
- The CUSTOMER table must contain W × 10 × 3,000 records, indicating that 3,000 customers are distributed in each district.
- The HISTORY table must contain W × 10 × 3,000 records, with one transaction history record for each customer.
- The ORDER table must contain W × 10 × 3,000 records, indicating that 3,000 orders are generated in each district. The last 900 orders generated will be added to the NEW-ORDER table, and 5 to 15 order lines will be randomly generated for each order.
During the test, a terminal is deployed in each district to simulate user services. Each terminal cyclically performs all types of transactions within its service life. After a terminal completes the cycle of one transaction, it enters the cycle of the next transaction.
After a customer places an order, an order containing several order lines is generated and added to the NEW-ORDER table.
A transaction history record is generated after a customer pays for an order. Each order contains 10 order lines on average, and 1% of the order lines are obtained from remote warehouses. These are the nine data tables in the TPC-C model.
Transaction types
The TPC-C benchmark test involves the following five types of transactions:
- The New-Order transaction: A New-Order transaction randomly selects 5 to 15 commodities from a warehouse to create an order. A fixed 1% of the transactions are chosen to simulate errors and rolled back. Generally, this type of transactions cannot exceed 45% of all types of transactions.
- The Payment transaction: A Payment transaction updates the balance of a customer to reflect the payment of the customer. This type of transactions account for 43% of all types of transactions.
- The Order-Status transaction: An Order-Status transaction randomly selects a customer to query the latest order of the customer and displays the status of each commodity in the order. This type of transactions account for 4% of all types of transactions.
- The Delivery transaction: A Delivery transaction batch-processes orders, updates the balances of the customers who placed the orders, and deletes the orders from the New-Order table. This type of transactions account for 4% of all types of transactions.
- The Stock-Level transaction: A Stock-Level transaction analyzes the stock level status of commodities. This type of transactions account for 4% of all types of transactions.
Environment preparations
Install BenchmarkSQL
The TPC defines strict and detailed test standards for the TPC-C benchmark test. Individual developers can use common open-source test tools to simulate TPC-C benchmark test scenarios. For example, BenchmarkSQL is used in this topic.
You can download BenchmarkSQL from the official website at https://sourceforge.net/projects/benchmarksql/.
To shorten the time for preparing the test environment, the OceanBase team modified the original program to improve the efficiency for loading test data. You can download the modified version from https://github.com/oceanbase/benchmarksql.
Note: A Java runtime environment not earlier than V1.8.0 is required.
Modify the configuration file
The props.ob configuration file is stored in the run/ directory of BenchmarkSQL.
db=oceanbase
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://127.0.0.1:2881/tpccdb?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true&allowMultiQueries=true
user=root@test
password=******
warehouses=10
loadWorkers=2
//fileLocation=/data/temp/
terminals=10
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=10
//Number of total transactions per minute
limitTxnsPerMin=0
//Set to true to run in 4.x compatible mode. Set to false to use the
//entire configured database evenly.
terminalWarehouseFixed=true
//The following five values must add up to 100
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
// Directory name to create for collecting detailed result data.
// Comment this out to suppress.
resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
osCollectorScript=./misc/os_collector_linux.py
osCollectorInterval=1
//osCollectorSSHAddr=user@dbhost
//osCollectorDevices=net_eth0 blk_sda
//LoadStartW=1
//LoadStopW=1000
Parameter description:
- db: the database type. Retain the setting in the template.
- driver: the driver file. Retain the setting in the template.
- conn: We recommend that you specify the IP address of the OBServer and enable port 2883 of the OBProxy for access. Retain the other settings in the template.
- user and password: the username, tenant name, and password specified in your environment. If multiple OceanBase clusters are created in your environment, we recommend that you set the user parameter in the {user_name}@{tenant_name}#{cluster_name} format.
- warehouses: the number of warehouses. This parameter determines the results of the performance test. If you want to test a multi-node OceanBase cluster, we recommend that you specify at least 1,000 warehouses. If the servers have limited configurations, you can specify 100 warehouses.
- loadWorkers: the number of concurrent worker threads for loading the warehouse data. If the servers have high configurations, you can set this parameter to a large value, for example, 100. If the servers have limited configurations, you can set this parameter to a small value, for example, 10. High concurrency increases memory consumption, which leads to errors or even failures in data loading.
- terminals: the number of concurrent users for stress testing. We recommend that you set this parameter to a value that is not greater than the number of warehouses × 10. Otherwise, unnecessary lock waits occur. In a production environment, we recommend that you set this parameter to at most 1000. In a test environment, we recommend that you first set this parameter to 100.
- runMins: the duration of the performance test in minutes. Longer duration poses higher challenges to the performance and stability of the database. We recommend that you set this parameter to at least 10 in a test environment and at least 60 in a production environment.
- LoadStartW and LoadStopW: the thresholds for starting and stopping the warehouse reloading. If the data import to a warehouse fails due to, for example, a large transaction timeout, you can re-import data to the warehouse.
Data preparations
Create a test database
In the test tenant, create a test database named tpccdb.
create database tpccdb;
Create tables
The table creation script is usually stored in the run/sql.common directory of BenchmarkSQL or a specified directory. You can use the following table creation script to create partitioned tables. Most tables are hash-partitioned by warehouse ID. The number of partitions depends on the amount of data and the number of servers under test.
If your cluster contains only one or three servers, you can set the number of partitions to 9. If 5,000 warehouses or a large number of servers in the cluster are under test, you can set the number of partitions to 99.
[root@obce-0000 run]# cat sql.common/tableCreates_parts.sql
create table bmsql_config (
cfg_name varchar(30) primary key,
cfg_value varchar(50)
);
-- drop tablegroup tpcc_group;
create tablegroup tpcc_group partition by hash partitions 9;
create table bmsql_warehouse (
w_id integer not null,
w_ytd decimal(12,2),
w_tax decimal(4,4),
w_name varchar(10),
w_street_1 varchar(20),
w_street_2 varchar(20),
w_city varchar(20),
w_state char(2),
w_zip char(9),
primary key(w_id)
)tablegroup='tpcc_group' partition by hash(w_id) partitions 9;
create table bmsql_district (
d_w_id integer not null,
d_id integer not null,
d_ytd decimal(12,2),
d_tax decimal(4,4),
d_next_o_id integer,
d_name varchar(10),
d_street_1 varchar(20),
d_street_2 varchar(20),
d_city varchar(20),
d_state char(2),
d_zip char(9),
PRIMARY KEY (d_w_id, d_id)
)tablegroup='tpcc_group' partition by hash(d_w_id) partitions 9;
create table bmsql_customer (
c_w_id integer not null,
c_d_id integer not null,
c_id integer not null,
c_discount decimal(4,4),
c_credit char(2),
c_last varchar(16),
c_first varchar(16),
c_credit_lim decimal(12,2),
c_balance decimal(12,2),
c_ytd_payment decimal(12,2),
c_payment_cnt integer,
c_delivery_cnt integer,
c_street_1 varchar(20),
c_street_2 varchar(20),
c_city varchar(20),
c_state char(2),
c_zip char(9),
c_phone char(16),
c_since timestamp,
c_middle char(2),
c_data varchar(500),
PRIMARY KEY (c_w_id, c_d_id, c_id)
)tablegroup='tpcc_group' partition by hash(c_w_id) partitions 9;
create table bmsql_history (
hist_id integer,
h_c_id integer,
h_c_d_id integer,
h_c_w_id integer,
h_d_id integer,
h_w_id integer,
h_date timestamp,
h_amount decimal(6,2),
h_data varchar(24)
)tablegroup='tpcc_group' partition by hash(h_w_id) partitions 9;
create table bmsql_new_order (
no_w_id integer not null ,
no_d_id integer not null,
no_o_id integer not null,
PRIMARY KEY (no_w_id, no_d_id, no_o_id)
)tablegroup='tpcc_group' partition by hash(no_w_id) partitions 9;
create table bmsql_oorder (
o_w_id integer not null,
o_d_id integer not null,
o_id integer not null,
o_c_id integer,
o_carrier_id integer,
o_ol_cnt integer,
o_all_local integer,
o_entry_d timestamp,
PRIMARY KEY (o_w_id, o_d_id, o_id)
)tablegroup='tpcc_group' partition by hash(o_w_id) partitions 9;
create table bmsql_order_line (
ol_w_id integer not null,
ol_d_id integer not null,
ol_o_id integer not null,
ol_number integer not null,
ol_i_id integer not null,
ol_delivery_d timestamp,
ol_amount decimal(6,2),
ol_supply_w_id integer,
ol_quantity integer,
ol_dist_info char(24),
PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number)
)tablegroup='tpcc_group' partition by hash(ol_w_id) partitions 9;
create table bmsql_item (
i_id integer not null,
i_name varchar(24),
i_price decimal(5,2),
i_data varchar(50),
i_im_id integer,
PRIMARY KEY (i_id)
) duplicate_scope='cluster';
create table bmsql_stock (
s_w_id integer not null,
s_i_id integer not null,
s_quantity integer,
s_ytd integer,
s_order_cnt integer,
s_remote_cnt integer,
s_data varchar(50),
s_dist_01 char(24),
s_dist_02 char(24),
s_dist_03 char(24),
s_dist_04 char(24),
s_dist_05 char(24),
s_dist_06 char(24),
s_dist_07 char(24),
s_dist_08 char(24),
s_dist_09 char(24),
s_dist_10 char(24),
PRIMARY KEY (s_w_id, s_i_id)
)tablegroup='tpcc_group' use_bloom_filter=true partition by hash(s_w_id) partitions 9;
Run the following command to create the tables:
./runSQL.sh props.ob sql.common/tableCreates_parts.sql
Load data
Data is initialized during loading. More warehouses lead to a longer loading time. Data loading for 1,000 warehouses takes about 1 hour, and that for 5,000 warehouses takes more than half a day. The specific performance depends on server configurations.
./runLoader.sh props.ob
The INSERT SQL statement for loading data uses the batch insert feature, which is specified in the JDBC URL field in the props.ob file. The write performance is significantly improved with this feature enabled.
Create indexes
After the data is initialized, log on to the test tenant and execute the following statement to create two indexes in the tpccdb database.
[root@obce-0000 run]# cat sql.common/indexCreates.sql
create index bmsql_customer_idx1
on bmsql_customer (c_w_id, c_d_id, c_last, c_first) local;
create index bmsql_oorder_idx1
on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local;
Start the test
Before you start the performance test, we recommend that you log on to the sys tenant as the administrator and perform a major freeze, to achieve better test results. You can run the following command to trigger a major freeze:
obclient -h127.0.0.1 -P2881 -uroot@sys -Doceanbase -A -p -c
obclient[oceanbase]> ALTER SYSTEM MAJOR FREEZE;
You can execute the following SQL statement to query the status of the major freeze. If the statement returns IDLE, the major freeze is completed.
MySQL [oceanbase]> select name,info from __all_zone where zone='' and name = 'merge_status';
+--------------+------+
| name | info |
+--------------+------+
| merge_status | IDLE |
+--------------+------+
1 row in set (0.001 sec)
After the major freeze is completed, run the following command to start the test:
./runBenchmark.sh props.ob
In the TPC-C benchmark test, the tpmC value (transactions per minute) measures the maximum effective throughput of the system. In fact, New-Order transactions are counted. Therefore, the tpmC value is the number of orders processed per minute.