OLTP performance test: TPC-C benchmark|OceanBase Cloud

OLTP performance test: TPC-C benchmark

Last Updated：2026-04-28 02:03:33 Updated

This topic describes how to use BenchmarkSQL 5.0 (a Java-based implementation of the TPC-C benchmark) to test the OLTP performance of OceanBase Cloud.

Note

OceanBase Database has been optimized extensively since V4.0.0 to improve user experience and ease of use, ensuring developers achieve good performance when using the database. This performance testing method is based on basic parameters for optimization, providing a good database performance experience, but it does not fully comply with the TPC-C standard.

Introduction to TPC-C

TPC-C (Transaction Processing Performance Council Benchmark C) has different operational modes for testing database stress tools, simulating e-commerce business processes. The main modules tested include order creation, inventory query, shipping, and payment.

Prepare the environment

Prepare a pressure test ECS

Please prepare an ECS, and it is recommended to use the same specifications as the OBServer to avoid the pressure machine becoming a bottleneck. Please note that the pressure machine ECS must be in the same VPC network as the OceanBase tenant, and the OceanBase cluster must allow access from the pressure machine ECS.
Create an OceanBase instance
1. Create an OceanBase instance. For more information, see Create an instance.
2. Create a tenant and set the tenant specifications based on the instance resources. For more information, see Create a tenant.
3. Create a database. In this example, a database named tpcctest is created. For more information, see Create a database.
Install the Benchmark SQL tool: Download from Benchmark SQL

Optimize the environment

Test scenario:

To better experience the features of OceanBase Database, it is recommended to enable the database proxy. For more information, see Manage database proxies.
By default, the database proxy and OBserve are deployed on the same ECS instance. Enabling the database proxy can have a positive impact on performance.

Execute the test

Install Ant

Follow these steps to install Ant on the pressure test ECS:

Download Ant.

wget "http://archive.apache.org/dist/ant/binaries/apache-ant-1.10.6-bin.zip"

Install Ant
```
unzip apache-ant-1.10.6-bin.zip
```

Set the environment variables.

[admin@obtest004 ~]$ sudo vim /etc/profile

#ant
export ANT_HOME=xx/apache-ant-1.10.6
export PATH=xx/apache-ant-1.10.6/bin:$PATH

[admin@obtest004 ~]$ source /etc/profile

Check whether the installation is successful.
```
[admin@obtest004 ~]$ ant -version
```
If the following information is returned, the installation is successful.
```
Apache Ant(TM) version 1.10.6 compiled on May 2 2019
```

Install Benchmark SQL

Follow these steps to install Benchmark SQL on the pressure test ECS:

Download Benchmark SQL.

For more information, see Benchmark SQL download address.
Decompress Benchmark SQL.
```
unzip ./benchmarksql-5.0.zip
```
Compile Benchmark SQL.

Go to the directory where Benchmark SQL is decompressed and use Ant to compile Benchmark SQL:
```
[admin@obtest004 ~] $ cd benchmarksql-5.0
[admin@obtest004 benchmarksql-5.0] $ ant
```

Modify BenchmarkSQL 5.0 to support OceanBase Database

Since BenchmarkSQL 5.0 does not support TPC-C testing for OceanBase Database, this section will detail how to modify the source code of BenchmarkSQL 5.0 to support OceanBase Database.

Modify the benchmarksql-5.0/src/client/jTPCC.java file to add support for OceanBase Database.

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"))   //Add support for OceanBase Database
            dbType = DB_OCEANBASE;
        else
        {
            log.error("unknown database type '" + iDB + "'");
            return;
        }

Modify the benchmarksql-5.0/src/client/jTPCCConfig.java file to add the OceanBase Database type.

public final static int
DB_UNKNOWN = 0,
DB_FIREBIRD = 1,
DB_ORACLE = 2,
DB_POSTGRES = 3,
DB_OCEANBASE = 4;

Modify the benchmarksql-5.0/src/client/jTPCCConnection.java file to add the "AS L" alias in the SQL subquery.

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");    //Add the "AS L" alias
              break;

Recompile the modified source code.

[xxxxx@obtest004 ~]# cd benchmarksql-5.0
[xxxxx@obtest004 benchmarksql-5.0]# ant

In the benchmarksql-5.0/run directory, create a file named prop.oceanbase.
```
db=oceanbase
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://$oceanbase_url:$port/$db_name?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=30000000
//Please fill in the complete user information
user=$user
password=*****
warehouses=1000
loadWorkers=40
terminals=600
database=$db_name
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=5
//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
//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec
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
```
Parameters in prop.oceanbase
- JDBC connection string: conn=jdbc:mysql://x.x.x.x(oceanbase link address):xx(port)/xxxx(dbname)?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=3000000
- rewriteBatchedStatements:
  - This parameter is very important and can significantly affect the data import efficiency. Do not ignore it.
  - If the data import is slow, you can log in to the corresponding tenant and use the show full processlist command to check whether it is enabled.
  - Batch updates are also used in new order transactions, so this parameter must be enabled during both data import and benchmarking.
- Number of concurrent terminals: 600. In MySQL compatible mode, the number of concurrent terminals needs to be dynamically adjusted based on specific configurations.
- useLocalSessionState: Specifies whether to use the internal values of autocommit, read_only, and transaction isolation at the JDBC end (local values). It is recommended to set this parameter to true. If set to false, statements need to be sent to the remote server, increasing the frequency of requests and affecting performance.
- warehouses and loadWorkers: These parameters are used to set the amount of data to be loaded. They can be adjusted as needed.
- The value of numTerminals must be greater than 0 and less than or equal to 10 times the value of numWarehouses.

Modify the benchmarksql-5.0/run/funcs.sh file to add the OceanBase database type.

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)      # Add the OceanBase database type
    cp="../lib/oceanbase/*:../lib/*"
    ;;
    esac
    myCP=".:${cp}:../dist/*"
    export myCP
}

...Omitted

case "$(getProp db)" in
    firebird|oracle|postgres|oceanbase)  # Add OceanBase database type
    ;;
    "") 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

Add the MySQL Java connector driver. We recommend that you use the mysql-connector-java-5.1.47.jar file.

[xxxxxx@obtest004 benchmarksql-5.0]# mkdir lib/oceanbase/
[xxxxxx@obtest004 benchmarksql-5.0]# cp xx/mysql-connector-java-5.1.47.jar lib/oceanbase/

Modify the benchmarksql-5.0/run/runDatabaseBuild.sh file.

AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"
# Change it to:
AFTER_LOAD="indexCreates buildFinish"

Modify the SQL statements in BenchMarkSQL5.

Backup and rewrite the benchmarksql-5.0/run/sql.common/tableCreates.sql file.

CREATE TABLE bmsql_config (
  cfg_name    varchar(30) PRIMARY KEY,
  cfg_value   varchar(50)
);

CREATE TABLEGROUP IF NOT EXISTS tpcc_group binding true partition by hash partitions 96;

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

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

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

CREATE TABLE bmsql_history (
  hist_id  integer AUTO_INCREMENT,
  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 96;

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

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

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

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' partition by hash(s_w_id) partitions 96;

Back up and rewrite the benchmarksql-5.0/run/sql.common/tableDrops.sql file.

DROP TABLE bmsql_config;
DROP TABLE bmsql_new_order;
DROP TABLE bmsql_order_line;
DROP TABLE bmsql_oorder;
DROP TABLE bmsql_history;
DROP TABLE bmsql_customer;
DROP TABLE bmsql_stock;
DROP TABLE bmsql_item;
DROP TABLE bmsql_district;
DROP TABLE bmsql_warehouse;
DROP TABLEGROUP tpcc_group;

Backup and rewrite benchmarksql-5.0/run/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;

Backup and rewrite benchmarksql-5.0/run/sql.common/indexDrops.sql.

ALTER TABLE bmsql_customer DROP INDEX bmsql_customer_idx1;
ALTER TABLE bmsql_oorder DROP INDEX bmsql_oorder_idx1;

Procedure

All the following commands are executed in the benchmarksql-5.0/run directory. Perform the TPC-C test as follows:

Run the following command to initialize the environment:
```
[obcloud ~]./runDatabaseDestroy.sh prop.oceanbase
```
Run the following command to create tables and import data:
```
[obcloud ~]./runDatabaseBuild.sh prop.oceanbase
```
Perform a major compaction.

A major compaction merges the current major version's SSTables and MemTables with the full static data of the previous major version. This improves the accuracy of storage layer statistics and stabilizes the generated execution plans.
```
MySQL [oceanbase]> ALTER SYSTEM major freeze;
Query OK, 0 rows affected
```
Check whether the major compaction is completed.
```
MySQL [oceanbase]> select STATUS from oceanbase.DBA_OB_MAJOR_COMPACTION;
```
The major compaction is completed when the STATUS column changes from COMPACTING to IDLE.

Manually collect statistics.

MySQL [oceanbase]> call dbms_stats.gather_schema_stats('$db_name',degree=>96);

Run the following command to perform the stress test:
```
[obcloud ~]$./runBenchmark.sh prop.oceanbase
```

OceanBase

Customer Stories

Documentation