Run the TPC-C benchmark on OceanBase Database

Introduction to TPC-C

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

Prepare the environment

• Java Development Kit (JDK): Use V1.8u131 or later.

• Java Database Connectivity (JDBC): mysql-connector-java-5.1.47 is recommended. Other versions may have syntax compatibility issues.

• Ant: Use Apache Ant 1.10 or later.

• BenchmarkSQL: Use BenchmarkSQL 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 Get started 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 '70g';
  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 configuration described in TPC-C benchmark report of OceanBase Database. You need to adjust the specifications based on your hardware configuration.
  • We recommend that you not use the obd cluster autodeploy command to deploy the cluster. To ensure stability, this command does not maximize the resource utilization, for example, does not use all of the memory. We recommend that you modify the configuration file separately to maximize the resource utilization.

Test plan

1. Four servers are required in this test. BenchmarkSQL, ODP, and OBD are deployed on a separate server for stress testing. When you use OBD to deploy an OceanBase cluster, three servers are required, which are evenly distributed in three zones.

   Note

   In the TPC-C benchmark test, you can flexibly configure the server on which BenchmarkSQL, ODP, and OceanBase Deployer (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.

2. 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 servers.

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 test

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

Notice

• 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. In the user scenario, you can set the parameters based on the actual situation. 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 on 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: # root user 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:

1. Download Ant.

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

2. Install Ant

unzip apache-ant-1.10.6-bin.zip

3. 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

4. Check whether the installation is successful.

[admin@obtest004 ~]$ant -version

The following information indicates successful installation.

 Apache Ant(TM) version 1.10.6 compiled on May 2 2019

Install BenchmarkSQL

Perform the following steps to install BenchmarkSQL:

1. Download BenchmarkSQL.

   Download the installation package on the BenchmarkSQL download page.

2. Unzip the BenchmarkSQL installation package.

   unzip ./benchmarksql-5.0.zip
   

3. 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 by using OceanBase Database. This section describes how to solve the problem by adapting the source code of BenchmarkSQL 5.0.

1. 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 another database type to the benchmarksql-5.0/src/client/jTPCCConfig.java file.

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

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;

3. Recompile the modified source code.

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

4. Create the prop.oceanbase file 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 prop.oceanbase 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 on to the corresponding tenant and run the show full processlist command to check whether the parameter is enabled.

  • The batch update feature is also used in new order transactions. So, you need to enable it in both the data import and the benchmarking stages.

• terminals: indicates the number of concurrent threads. The default value is 600. You can adjust it for a MySQL tenant as needed.

• useLocalSessionState: indicates 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.

• numTerminals > 0 && numTerminals <= 10*numWarehouses: The value of terminals must be within this range.

5. Add OceanBase Database as another database type to the benchmarksql-5.0/run/funcs.sh file.

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

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

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

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

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

8. 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.

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 in the 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;

Environment optimization

1. ODP optimization

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

ALTER proxyconfig SET proxy_mem_limited='4G';
ALTER proxyconfig set enable_compression_protocol=false;

2. OBServer node optimization

Run the following command to access the sys tenant: obclient -h$host_ip -P$host_port -uroot@sys -A. 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;

3. 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 test.

1. Run the following command to initialize the test environment:

./runDatabaseDestroy.sh prop.oceanbase

2. Run the following command to create tables and import data:

 ./runDatabaseBuild.sh prop.oceanbase

3. Log on to the sys tenant to perform a major compaction.

A major compaction compacts SSTables and MemTables of the current major version with the full static data of the previous major version and generates a new set of full data. This makes the statistics of the storage layer 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

4. Check whether the 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.

5. Manually collect statistics.

Run the following command to access the test tenant: obclient -h$host_ip -P$host_port -u$user@$tenant -p$password -A -D$database. Then run the following command:

call dbms_stats.gather_schema_stats('$db_name',degree=>96);

6. 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

• Q: How do I stop BenchmarkSQL?

  A: You can set runMins in prop.oceanbase so that BenchmarkSQL automatically stops when the time you set for runMins expires.

  You can also run the ps auxf | grep java command to forcibly terminate the runBenchmark process.

• Q: What do I do when an error occurred while looking for the main class. Here is the error message:

  Could not find the main class: ExecJDBC.  Program will exit.
  

  A: You can try upgrading JDK or JDBC. Run the following command to install Java:

  sudo yum install jdk
  

• Q: What do I do if the number of terminals is invalid? Here is the error message:

  Invalid number of terminals!
  

  A: You probably specified an incorrect value for the terminals parameter in the prop.oceanbase file. The value of terminals must be within the range of numTerminals <= 0 || numTerminals > 10*numWarehouses.

• Q: What do I do if the transaction times out? Here is the error message:

  transaction timeout
  

  A: You need to increase the timeout value. Set the following values in the test tenant: set global ob_query_timeout=36000000000;set global ob_trx_timeout=36000000000.