Run the TPC-C benchmark in OceanBase Database

Notice

A Java runtime environment not earlier than V1.8.0 is required.

Note

• db: the database type. Retain the setting in the template.
• driver: the driver file. We recommend that you use the JDBC driver for OceanBase Database, mysql-connector-java-5.1.47. If you need to use this driver, obtain it from MySQL Downloads.
• conn: the connection information. We recommend that you use the IP address and port number of the OBServer node and retain 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. We recommend that you set this parameter to at most 1,000 in a production environment and to not smaller than 100 in a 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.

Data preparations

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

After the data is initialized, log on to the test tenant of the cluster and run the following command 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 obtain better test results. You can manually trigger a major compaction using the following method, but this step is not mandatory.

obclient[oceanbase]> ALTER SYSTEM MAJOR FREEZE;

Run the following command to query the state of the major compaction. If the result 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

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. In fact, New-Order transactions are counted. Therefore, the tpmC value is the number of orders processed per minute.

Try out the scalable OLTP capability of OceanBase Database

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 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='zone1';