The TPC-C benchmark is one of the performance benchmarks launched by the Transaction Processing Performance Council (TPC). Since its launch, the TPC-C benchmark has become an important reference for performance tests in the database industry. Major database vendors around the world run the TPC-C benchmark and submit their results to TPC from time to time, in the hope of topping the TPC-C benchmark chart. OceanBase Database set new world records in running the TPC-C benchmark twice in 2019 and 2020 as the first distributed relational database in the list.
This article describes how to run the TPC-C benchmark against OceanBase Database to experience its online transaction processing (OLTP) capability.
TPC-C overview
Database model
The TPC-C benchmark specifies the initial state of the database and 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 involves the following five transaction types:
- NewOrder transaction: A NewOrder 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, transactions of this type cannot exceed 45% of all types of transactions.
- Payment transaction: A payment transaction updates the balance of a customer to reflect the payment of the customer. Transactions of this type account for 43% of all types of transactions.
- OrderStatus transaction: An OrderStatus transaction randomly selects a customer to query the latest order of the customer and displays the status of each commodity in the order. Transactions of this type account for 4% of all types of transactions.
- Delivery transaction: A delivery transaction batch-processes orders, updates the balances of the customers who placed the orders, and deletes the orders from the NewOrder table. Transactions of this type account for 4% of all types of transactions.
- Stock-Level transaction: A StockLevel transaction analyzes the stock-level status of commodities. Transactions of this type account for 4% of all types of transactions.
Environment preparations
OceanBase cluster
You can observe the performance of OceanBase Database in different ways based on the type of your OceanBase cluster. If you have deployed a single-node OceanBase cluster, you can observe the performance of OceanBase Database in standalone deployment mode. For more information about how to create an OceanBase cluster, see Quick Start. To experience the scalable OLTP capability of the distributed architecture of OceanBase Database, we recommend that you deploy an OceanBase cluster with at least three nodes. In this example, the tenant mode is MySQL and the tenant name is test. You can create a tenant. For more information, see Experience the multitenancy feature.
Install BenchmarkSQL
TPC defines strict and detailed test standards for the TPC-C benchmark. Individual developers can use general open-source test tools to simulate TPC-C benchmark scenarios. For example, BenchmarkSQL is used in this topic. You can download BenchmarkSQL from the official website at https://sourceforge.net/projects/benchmarksql/.
Notice
A Java runtime environment not earlier than V1.8.0 is required.
Adapt BenchmarkSQL 5.0
BenchmarkSQL 5.0 does not support running the TPC-C benchmark by using OceanBase Database. This section describes how to solve the problem by adapting the source code of BenchmarkSQL 5.0.
- Add OceanBase Database-related information to the
benchmarksql-5.0/src/client/jTPCC.javafile.
if (iDB.equals("firebird"))
dbType = DB_FIREBIRD;
else if (iDB.equals("oracle"))
dbType = DB_ORACLE;
else if (iDB.equals("postgres"))
dbType = DB_POSTGRES;
else if (iDB.equals("oceanbase"))
dbType = DB_OCEANBASE;
else
{
log.error("unknown database type '" + iDB + "'");
return;
}
Add OceanBase Database as another database type to the benchmarksql-5.0/src/client/jTPCCConfig.java file.
public final static int
DB_UNKNOWN = 0,
DB_FIREBIRD = 1,
DB_ORACLE = 2,
DB_POSTGRES = 3,
DB_OCEANBASE = 4;
- Add the alias "AS L" to the SQL subquery in the
benchmarksql-5.0/src/client/jTPCCConnection.javafile.
default:
stmtStockLevelSelectLow = dbConn.prepareStatement(
"SELECT count(*) AS low_stock FROM (" +
" SELECT s_w_id, s_i_id, s_quantity " +
" FROM bmsql_stock " +
" WHERE s_w_id = ? AND s_quantity < ? AND s_i_id IN (" +
" SELECT ol_i_id " +
" FROM bmsql_district " +
" JOIN bmsql_order_line ON ol_w_id = d_w_id " +
" AND ol_d_id = d_id " +
" AND ol_o_id >= d_next_o_id - 20 " +
" AND ol_o_id < d_next_o_id " +
" WHERE d_w_id = ? AND d_id = ? " +
" ) " +
" )AS L");
break;
- Recompile the modified source code.
[oceanbase@testdrier test]# cd benchmarksql-5.0
[oceanbase@testdrier benchmarksql-5.0]# ant
- Add OceanBase Database as another database type to the
benchmarksql-5.0/run/funcs.shfile.
function setCP()
{
case "$(getProp db)" in
firebird)
cp="../lib/firebird/*:../lib/*"
;;
oracle)
cp="../lib/oracle/*"
if [ ! -z "${ORACLE_HOME}" -a -d ${ORACLE_HOME}/lib ] ; then
cp="${cp}:${ORACLE_HOME}/lib/*"
fi
cp="${cp}:../lib/*"
;;
postgres)
cp="../lib/postgres/*:../lib/*"
;;
oceanbase)
cp="../lib/oceanbase/*:../lib/*"
;;
esac
myCP=".:${cp}:../dist/*"
export myCP
}
... Omitted
case "$(getProp db)" in
firebird|oracle|postgres|oceanbase)
;;
"") echo "ERROR: missing db= config option in ${PROPS}" >&2
exit 1
;;
*) echo "ERROR: unsupported database type 'db=$(getProp db)' in ${PROPS}" >&2
exit 1
;;
esac
- Modify the
benchmarksql-5.0/run/runDatabaseBuild.shfile.
AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"
Modified code:
AFTER_LOAD="indexCreates buildFinish"
Modify the configuration file
The props.ob configuration file is stored in the run/ directory of BenchmarkSQL.
db=oceanbase
driver=com.alipay.oceanbase.jdbc.Driver
conn=jdbc:oceanbase://127.0.0.1:2881/tpccdb?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true&allowMultiQueries=true
user=root@test
password=root
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
Note
- db: the database type. Retain the setting in the template.
- driver: the driver file. We recommend that you use the JDBC driver oceanbase-client of OceanBase Database. If you want to use this driver, contact Technical Support.
- conn: the connection information. We recommend that you set the IP address and port to that of the OBServer, and retain other settings in the template.
- user and password: the username, tenant name, and password specified in the 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 1000 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. We recommend that you set this parameter to 1000 in the production environment, and to 100 in the test environment.
- 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.
Generate data
Create a test database
In the test tenant, create a test database named tpccdb.
create database tpccdb;
Create a table
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, 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 binding true 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)
);
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
Loading data means data initialization. The data loading speed depends on the server configuration. A higher server configuration indicates a higher data loading speed.
./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 an index
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 tenant and perform a major compaction, to achieve better test results. You can execute the following SQL statement to trigger a major compaction. This procedure is not a must.
obclient[oceanbase]> ALTER SYSTEM MAJOR FREEZE;
Execute the following SQL statement to query the status of the major compaction. If the statement returns IDLE, the major compaction is completed.
MySQL [oceanbase]> SELECT * FROM oceanbase.CDB_OB_ZONE_MAJOR_COMPACTION;
+-----------+-------+---------------------+---------------------+----------------------------+----------------------------+--------+
| TENANT_ID | ZONE | BROADCAST_SCN | LAST_SCN | LAST_FINISH_TIME | START_TIME | STATUS |
+-----------+-------+---------------------+---------------------+----------------------------+----------------------------+--------+
| 1 | zone1 | 1664503499339325817 | 1664503499339325817 | 2022-09-30 10:05:19.442115 | 2022-09-30 10:04:59.369976 | IDLE |
| 1002 | zone1 | 1 | 1 | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 | IDLE |
+-----------+-------+---------------------+---------------------+----------------------------+----------------------------+--------+
2 rows in set (0.003 sec)
After the major compaction is completed, run the following command to start the test:
./runBenchmark.sh props.ob
In the TPC-C benchmark, the tpmC value (transactions per minute) measures the maximum effective throughput of the system. New-Order transactions are counted. Therefore, the tpmC value is the number of orders processed per minute.
Experience the OLTP capabilities of an OceanBase cluster in scalable mode
Regardless of whether you use a single-node or multi-node cluster in the preceding test, only the zone where the leaders of the tenant reside participates in the transaction processing. This is because, by default, the leaders of the tenant are distributed to zones by priority. As a distributed database, OceanBase Database allows you to shuffle leader partitions of a tenant to multiple zones, to enable linear scaling of computing and processing capabilities across servers. If your cluster contains three or more nodes, you only need to run the following command as the administrator and then start the test again.
alter tenant test set primary_zone='RANDOM';