Run the TPC-C benchmark on OceanBase Database|V4.2.5| docs|Distributed Database

Run the TPC-C benchmark on OceanBase Database

Last Updated：2025-01-02 01:58:39 Updated

Introduction to TPC-C

TPC-C is an online transaction processing (OLTP) benchmark. It uses a commodity sales model to test an OLTP system. For more information, visit TPC-C.

Note

OceanBase Database is extensively optimized in versions later than V4.0.0 to improve the user experience, ease of use, and performance. You can use TPC-C only for performance tuning based on basic parameters.

Prepare the environment

Java Development Kit (JDK): Use JDK 1.8u131 or later.
Java Database Connectivity (JDBC): Use MySQL Connector/J 5.1.47. Other versions may incur syntax compatibility issues.
Ant: Use Apache Ant 1.10 or later.
Benchmark SQL: Use Benchmark SQL 5.0.
OBClient: For more information, see OBClient documentation.
OceanBase Database: We recommend that you deploy OceanBase Database Proxy (ODP) separately. For more information, see Quick start with OceanBase Database.
IOPS: We recommend that the disk IOPS be above 10,000.
Tenant specifications:
```
CREATE RESOURCE UNIT tpcc_unit max_cpu 26, memory_size '100g';
CREATE RESOURCE POOL tpcc_pool unit = 'tpcc_unit', unit_num = 1, zone_list=('zone1','zone2','zone3');
CREATE TENANT tpcc_tenant resource_pool_list=('tpcc_pool'),  zone_list('zone1', 'zone2', 'zone3'), primary_zone=RANDOM, locality='F@zone1,F@zone2,F@zone3' set variables ob_compatibility_mode='mysql', ob_tcp_invited_nodes='%';
```
Notice
- The preceding tenant specifications are provided based on the hardware configurations in TPC-C benchmark report of OceanBase Database. You need to adjust the specifications based on the hardware configurations of your database.
- We recommend that you do not use the obd cluster autodeploy command to deploy the cluster. To ensure stability, this command does not maximize the resource utilization. For example, it does not use all of the memory. If you use this command, we recommend that you modify the configuration file separately to maximize the resource utilization.

Test plan

Four servers are required to run this test. Deploy BenchmarkSQL, ODP, and OceanBase Deployer (obd) on one server for stress testing, and deploy an OceanBase cluster using obd on the other three servers in the 1-1-1 architecture, which means that the OceanBase cluster has three zones, with each containing one OBServer node.
Note

In the TPC-C benchmark, you can flexibly configure the server on which BenchmarkSQL, ODP, and obd are deployed based on the total number of OBServer CPU cores. The server configuration varies in the following three cases:
- When the total number of OBServer CPU cores is less than or equal to 47, the server requires 8 CPU cores and 64 GB of memory.
- When the total number of OBServer CPU cores ranges from 48 to 72, the server requires 16 CPU cores and 128 GB of memory.
- When the total number of OBServer CPU cores is greater than or equal to 73, the server requires 32 CPU cores and 128 GB of memory.
After successful deployment, create the tenant and users required for running the TPC-C benchmark. The sys tenant is a built-in system tenant for managing the cluster. Do not use the sys tenant to run the benchmark. Set the value of the primary_zone parameter of the tenant to RANDOM. The value RANDOM indicates that the leader of the new table partitions is randomly assigned to one of the three OBServer nodes.

Test specifications

warehouses=1000
loadWorkers=40
terminals=600
runMins=5
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4

Use obd to run the benchmark

sudo yum install -y yum-utils
sudo yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo
sudo yum install obtpcc java
obd test tpcc obperf --tenant=tpcc_mysql --warehouses 10  --run-mins 1

Considerations

For more information about the parameters required for running the TPC-C benchmark by using obd, see obd test tpcc.
In this example, default values are used for most parameters. You can adjust their values as needed. For example, the cluster name used in this example is obperf, and the tenant name is tpcc_mysql.
If you use obd to run the test, you must use obd to install and deploy the cluster. Otherwise, the information about the cluster cannot be obtained. As a result, performance tuning cannot be performed based on the configurations of the cluster.
If you want to change the password of the sys tenant on a terminal, you must log in to the terminal, change the password of the sys tenant to the default value, and then run the obd cluster edit-config command to set a password for the sys tenant in the configuration file. The password is specified by the root_password parameter. After the obd cluster edit-config command is executed, you must run the obd cluster reload command for the modification to take effect.
After you run the obd test tpcc command, the system lists the test steps and outputs in detail. A larger data amount requires a longer test time.
The obd test tpcc command automatically completes all operations, including the generation and transmission of test data, OceanBase Database parameter optimization, data loading, and testing. If an error occurs during the process, you can retry the test by referring to obd test tpcc. For example, you can skip data generation and transmission, and directly load data and run the test.

Manually run the TPC-C benchmark

Install Ant

Perform the following steps to install Ant:

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 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, Ant is successfully installed:
```
Apache Ant(TM) version 1.10.6 compiled on May 2 2019
```

Install BenchmarkSQL

Perform the following steps to install BenchmarkSQL:

Download BenchmarkSQL.

For more information, see Download BenchmarkSQL installation package.
Decompress the BenchmarkSQL installation package.
```
unzip ./benchmarksql-5.0.zip
```
Compile BenchmarkSQL.

Enter the directory of BenchmarkSQL and run Ant to compile BenchmarkSQL:

[admin@obtest004 ~] $ cd benchmarksql-5.0
[admin@obtest004 benchmarksql-5.0] $ ant

Adapt BenchmarkSQL 5.0

BenchmarkSQL 5.0 does not support running the TPC-C benchmark on OceanBase Database. This section provides a step-by-step guide on how to modify the BenchmarkSQL 5.0 source code to support OceanBase Database.

Add OceanBase Database-related information to the benchmarksql-5.0/src/client/jTPCC.java file.

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

Add OceanBase Database as a database type in the jTPCCConfig.java file in the benchmarksql-5.0/src/client/ directory.

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

Use AS L to specify "L" as the alias for the SQL subquery in the jTPCCConnection.java file in the benchmarksql-5.0/src/client/ directory.

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");    // Use AS L to specify "L" as the alias.
                break;

Recompile the modified source code.

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

Create a file named prop.oceanbase in the benchmarksql-5.0/run directory.
```
db=oceanbase
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://$host_ip:$port/$db_name?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=30000000
// Specify the complete user information.
user=$user@$tenant 
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 the prop.oceanbase file are described as follows:
- JDBC connection string: conn=jdbc:mysql://x.x.x.x(ip):xx(port)/xxxx(dbname)?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=3000000
- rewriteBatchedStatements:
  - This parameter has a great impact on data import efficiency and cannot be ignored.
  - If data import is slow, log in to the corresponding tenant and run the show full processlist command to check whether this parameter is set to true.
  - The batch update feature is also used in NewOrder transactions. So, you need to enable it in both the data import and the benchmark stages.
- terminals: the number of concurrent threads. The default value is 600. You can adjust it for a MySQL tenant as needed.
- useLocalSessionState: specifies whether to use the internal values (local values on the JDBC client) of autocommit, read_only, and transaction isolation. We recommend that you set the value to true. Otherwise, requests need to be sent to the remote terminal, which increases the request frequency and decreases the performance.
- warehouses and loadWorkers: The two parameters are used to set the data volume in the stress test and can be adjusted as needed.

Add OceanBase Database as a database type in the funcs.sh file in the benchmarksql-5.0/run/ directory.

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 the 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 JDBC driver. We recommend that you download the mysql-connector-java-5.1.47.jar package.

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

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

AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"
# Modified code:
AFTER_LOAD="indexCreates buildFinish"

Modify .sql files in BenchMarkSQL 5.0.

Back up and rewrite the tableCreates.sql file in the benchmarksql-5.0/run/sql.common/ directory.

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 `tableDrops.sql` file in the `benchmarksql-5.0/run/sql.common/` directory.

```sql
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;

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

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;

Back up and rewrite the indexDrops.sql file in the benchmarksql-5.0/run/sql.common/ directory.

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

Optimize the test environment

Perform ODP optimization in the sys tenant.

Run the obclient -h$host_ip -P$host_port -uroot@sys -A command to connect to the sys tenant. Then execute the following statements:
```
ALTER PROXYCONFIG SET proxy_mem_limited='4G';

ALTER PROXYCONFIG SET enable_compression_protocol=false;
```

Perform OBServer node optimization in the sys tenant.

Run the obclient -h$host_ip -P$host_port -uroot@sys -A command to connect to the sys tenant. Then execute the following statements:

ALTER SYSTEM SET enable_sql_audit=false;

select sleep(5);

ALTER SYSTEM SET enable_perf_event=false;

ALTER SYSTEM SET syslog_level='PERF';

ALTER SYSTEM SET enable_record_trace_log=false;

After you optimize the parameters, run the obd cluster restart $cluster_name command to restart the cluster.

Procedure

Run the following commands in the benchmarksql-5.0/run directory. Perform the following steps to start the TPC-C benchmark:

Run the following command to initialize the test environment:
```
./runDatabaseDestroy.sh prop.oceanbase
```
Run the following command to create tables and import data:
```
./runDatabaseBuild.sh prop.oceanbase
```
Log in to the sys tenant to perform a major compaction.

A major compaction compacts MemTables and SSTables of the current major version with the full static data of the previous major version and generates a new set of full data. This ensures that the statistics of the storage layer are more accurate and improves the stability of execution plans.
```
MySQL [(none)]> USE oceanbase
Database changed
MySQL [oceanbase]> ALTER SYSTEM major freeze tenant=<your tenant name>;
Query OK, 0 rows affected
```

Check whether the major compaction is completed.

MySQL [oceanbase]> SELECT FROZEN_SCN, LAST_SCN FROM oceanbase.CDB_OB_MAJOR_COMPACTION;
+---------------------+---------------------+
| FROZEN_SCN          | LAST_SCN            |
+---------------------+---------------------+
| 1667239201167716767 | 1667239201167716767 |
| 1667239200111919300 | 1667239200111919300 |
| 1667239201167452168 | 1667239201167452168 |
| 1667239201168053124 | 1667239201168053124 |
| 1667239201167520213 | 1667239201167520213 |
+---------------------+---------------------+

If all FROZEN_SCN and LAST_SCN values are equal, the major compaction is completed.

Manually collect statistics.

Run the obclient -h$host_ip -P$host_port -u$user@$tenant -p$password -A -D$database command to connect to the test user tenant. Then execute the following statement:
```
call dbms_stats.gather_schema_stats('$db_name',degree=>96);
```
Run the following command to perform the stress test:
```
./runBenchmark.sh prop.oceanbase
```

Note

For more information about the test result, see TPC-C benchmark report of OceanBase Database.

FAQ

How do I stop BenchmarkSQL?

You can set the value of the runMins parameter in the prop.oceanbase file so that BenchmarkSQL automatically stops when the specified time expires.

You can also run the ps auxf | grep java command to forcibly terminate the runBenchmark process.
What do I do when the main class cannot be found with the following error message returned?
```
Could not find the main class: ExecJDBC.  Program will exit.
```
You can try upgrading JDK or JDBC. Run the following command to install Java:
```
sudo yum install jdk
```
What do I do if the number of terminals is invalid with the following error message returned?
```
Invalid number of terminals!
```
You probably specified an incorrect value for the terminals parameter in the prop.oceanbase file. The value for the terminals parameter must be greater than 0 and less than or equal to 10 times the number of warehouses.
What do I do if a transaction times out with the following error message returned?
```
transaction timeout
```
You need to increase the timeout periods. You can execute the SET GLOBAL ob_query_timeout=36000000000 and SET GLOBAL ob_trx_timeout=36000000000 statements in the test tenant.

Run the TPC-C benchmark on OceanBase Database

Introduction to TPC-C

Note

Prepare the environment

Notice

Test plan

Note

Test specifications

Use obd to run the benchmark

Considerations

Manually run the TPC-C benchmark

Install Ant

Install BenchmarkSQL

Adapt BenchmarkSQL 5.0

Optimize the test environment

Procedure

Note

FAQ