Run the TPC-C benchmark on OceanBase Database

This topic introduces how to run the TPC-C benchmark on OceanBase Database.

What is TPC-C?

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

Environment preparation

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

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

• Ant: Apache Ant 1.10 or later.

• BenchmarkSQL: BenchmarkSQL 5.0 is recommended.

• 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 tenant specifications mentioned above are based on the hardware configuration in the TPC-C benchmark report of OceanBase Database. You need to adjust the specifications based on the hardware configurations of your database.
  • When deploying a cluster, it is recommended not to use the obd cluster autodeploy command. This command prioritizes stability and does not maximize resource utilization (for example, it does not use all available memory). It is advisable to optimize the configuration file separately to maximize resource utilization.

Test plan

1. This test requires a total of four servers. Deploy BenchmarkSQL, ODP, and obd on one server for stress testing, and set up the OceanBase cluster using obd on the remaining three servers in a 1:1:1 configuration.

   Note

   In the TPC-C test, you can flexibly configure the servers for BenchmarkSQL, ODP, and obd based on the total number of OBServer CPU cores. The server configuration varies in the following three cases:

   • If 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.
   • If the total number of OBServer CPU cores ranges from 48 to 72, the server requires 16 CPU cores and 128 GB of memory.
   • If 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 a successful deployment, create the tenants and users needed for the TPC-C test. The sys tenant is a built-in system tenant for cluster management and do not use it to perform the test. Set the tenant's primary_zone to RANDOM, which indicates that the leader of 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

One-click TPC-H test using obd

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

• When using obd to run the TPC-C benchmark, refer to obd test tpch for detailed parameters.

• In this example, most parameters are set to their default values. In actual scenarios, you can adjust these parameters based on specific needs. For example, the cluster name used here is obperf, and the tenant name is tpcc_mysql.

• To use obd for the test, make sure that the cluster is installed and deployed using obd. Otherwise, the cluster information cannot be obtained, and performance tuning cannot be performed based on the cluster configuration.

• If the system tenant's password is changed through terminal login and is no longer the default empty value, you need to reset it to the default through the terminal first. Then, use the obd cluster edit-config command to set the password in the configuration file for the system tenant, under the # root_password: # root user password section. After that, run the obd cluster reload command to apply the changes.

• 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 data generation, data transmission, OceanBase parameter optimization, data loading, and testing, without requiring any additional interventions. If any errors occur during the process, refer to obd test tpch to try again. For example, you can skip data generation and transmission to proceed directly with data loading and testing.

Manual TPC-C test

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 that the installation is successful.

   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 use Ant to compile BenchmarkSQL:

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

Adapt BenchmarkSQL 5.0

Since BenchmarkSQL 5.0 does not support running the TPC-C benchmark on OceanBase Database, this section details how to modify parts of BenchmarkSQL5's source code to support OceanBase Database.

1. Edit the benchmarksql-5.0/src/client/jTPCC.java file to add OceanBase-related information.

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

2. Edit the benchmarksql-5.0/src/client/jTPCCConfig.java file to add OceanBase Database as a database type.

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

3. Edit the benchmarksql-5.0/src/client/jTPCCConnection.java file to add the alias "AS L" 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 alias "AS L".
                   break;
   

4. Recompile the modified source code.

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

5. In the benchmarksql-5.0/run directory, create a file named prop.oceanbase.

   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
   //Fill in 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 be equal to zero
   runTxnsPerTerminal=0
   //To run for specified minutes- runTxnsPerTerminal must be equal to 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: These 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.

6. Edit the benchmarksql-5.0/run/funcs.sh file to add OceanBase Database as a 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 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
   

7. Add the MySQL JDBC driver. The mysql-connector-java-5.1.47.jar is recommended.

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

8. Edit the benchmarksql-5.0/run/runDatabaseBuild.sh file.

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

9. 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 tuning

1. Perform ODP tuning 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;
   

2. Perform OceanBase Database tuning 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;
   

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

TPC-C test

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

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 merges the current major version's SSTables and MemTables with the complete static data from the previous major version. This results in more accurate storage-level statistics and more stable 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 major compaction is complete.

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

5. 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);
   

6. Run the following command to perform the stress test:

   ./runBenchmark.sh prop.oceanbase
   

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 the main class cannot be found? 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 periods by executing set global ob_query_timeout=36000000000 and set global ob_trx_timeout=36000000000 in the test tenant.