Run the TPC-C benchmark test with OceanBase Database

What is TPC-C

TPC-C is a benchmark designed to evaluate the performance of Online Transaction Processing (OLTP) systems using a product sales model.

Environment setup

Before running tests, prepare your environment as follows:

Software requirements

• JDK: Version 1.8u131 or later is recommended.

• JDBC: mysql-connector-java-5.1.47 is recommended.

• Ant: apache-ant-1.10 or later is recommended.

• BenchmarkSQL: BenchmarkSQL 5.0 is recommended.

• OBClient: For more information, see the OBClient documentation.

• OceanBase Database: For more information, see Quick start with OceanBase Database Community Edition.

• IOPS: A disk IOPS of 10,000 or higher is recommended.

Tenant specifications

Configure tenant specifications based on the hardware setup described in the TPC-C benchmark report of OceanBase Database. Adjust these settings according to your actual hardware.

• Cluster deployment

  1. This test uses four machines. BenchmarkSQL, OceanBase Database Proxy (ODP), and OceanBase Deployer (obd) are deployed together on one machine as the client load generator. The OceanBase cluster, deployed via obd, uses three machines in a 1:1:1 configuration. Deployment requirements vary depending on the total number of OBServer CPU cores:

     • If the total OBServer cores are 47 or fewer, each machine requires 8 cores and 64 GB memory.
     • If the total OBServer cores are between 48 and 72, each machine requires 16 cores and 128 GB memory.
     • If the total OBServer cores are 73 or more, each machine requires 32 cores and 128 GB memory.

     Note

     When deploying the cluster, avoid using the obd cluster autodeploy command. This command prioritizes stability and does not maximize resource usage (such as memory). Tune the configuration file directly to make full use of available resources.

  2. After deployment, create a tenant and user for the TPC-C test. The sys tenant is reserved for cluster management and is not used for testing. Set the tenant's primary_zone to RANDOM, which means new table partition leaders are randomly assigned to the three machines.

• Tenant creation

  Use the OBD CLUSTER TENANT CREATE command to create a test tenant. The syntax is:

  obd cluster tenant create <DEPLOY_NAME> -n <TENANT_NAME> --max-cpu=28 --memory-size=180G -–zone-list=zone1,zone2,zone3 -–primary-zone=RANDOM  --locality=F@zone1,F@zone2,F@zone3 --charset=utf8 -s 'ob_tcp_invited_nodes="%"' --optimize=<optimize>
  

  Parameter description:

  • DEPLOY_NAME: the name of the cluster.
  • TENANT_NAME: the name of the tenant.
  • --zone-list: the list of zones for the tenant.
  • --primary-zone: the primary zone of the tenant.
  • --locality: the distribution of replicas among zones.
  • --charset: the character set of the tenant.
  • -s: the value of the system variable of the tenant.
  • OPTIMIZE: the load type of the tenant. Valid values: express_oltp, complex_oltp, olap, htap, and kv. Default value: htap, which is suitable for hybrid OLAP and OLTP workloads.

  For more information about obd deployment, see obd cluster tenant create.

  Notice

  In V4.3.x and later, you can set the scenario configuration when deploying with obd to select the appropriate cluster workload type. If not specified, scenario defaults to htap. For more information, see the Deploy an OceanBase cluster on the GUI.

  For example, to create a tenant named tpcc_tenant in a cluster named obperf, with 28 CPU cores and 180 GB memory, and set the tenant workload type to match the cluster scenario:

  obd cluster tenant create obperf -n tpcc_tenant --max-cpu=28 --memory-size=180G -–zone-list=zone1,zone2,zone3 -–primary-zone=RANDOM  --locality=F@zone1,F@zone2,F@zone3 --charset=utf8 -s 'ob_tcp_invited_nodes="%"' --optimize=htap
  

  Note

  In this example, --optimize=htap sets the default workload type. For production environments, select a workload type that matches your cluster.

Test methods

After preparing the environment, you can run TPC-C performance tests using either of these methods:

• Automatically with the obd tool

• • Manually using the TPC-C tool

Run TPC-C tests automatically with the obd tool

You can execute the TPC-C test with a single obd script. Example commands:

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

Before running the TPC-C test with obd, keep the following in mind:

• For more information about the parameters for running the TPC-C benchmark test by using obd, see obd test tpcc.

• Most parameters in this example use default values. Adjust them as needed for your scenario. For example, the cluster name here is obperf and the tenant name is tpcc_mysql.

• The cluster must be installed and deployed by obd for the automated test to work, so obd can access cluster information and optimize performance.

• If you have changed the system tenant password from the terminal and it is no longer empty, first reset it to the default value. Then use obd cluster edit-config to set the password in the config file. The relevant setting is # root_password: # root user password. After editing, run obd cluster reload to apply the changes.

• After running obd test tpcc, the system displays detailed steps and output. Larger data volumes require more time.

• The obd test tpcc command completes all operations automatically, including generating and transferring test data, optimizing OceanBase parameters, loading data, and running the test. If any step fails, refer to obd test tpcc for troubleshooting, such as skipping data generation and transfer and proceeding directly to loading and testing.

Run TPC-C tests manually with the TPC-C tool

Manual testing is performed after you choose your cluster workload type and tune the tenant scenario. This process helps you gain deeper insight into OceanBase Database, especially how to optimize parameter settings.

Step 1: Install Ant

Follow these steps to install Ant.

1. Download the Ant installation package.

   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 up Ant 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. Verify the installation.

   [admin@obtest004 ~]$ant -version
   

   If you see the following output, Ant was installed successfully:

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

Step 2: Install BenchmarkSQL

Follow these steps to install BenchmarkSQL.

1. Download the BenchmarkSQL installation package.

   For more information, see BenchmarkSQL Download Address.

2. Decompress BenchmarkSQL.

   unzip ./benchmarksql-5.0.zip
   

3. Compile BenchmarkSQL.

Go to the directory where BenchmarkSQL is decompressed and use Ant to compile BenchmarkSQL.

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

Step 3: Adapt BenchmarkSQL 5.0 for OceanBase Database

BenchmarkSQL 5.0 does not natively support OceanBase Database for TPC-C tests. Some source code changes are required for compatibility.

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

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

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

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

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 the prop.oceanbase file.

   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
   //Please fill in the complete user information.
   user=$user@$tenant
   password=*****
   warehouses=1000
   loadWorkers=40
   terminals=1000
   database=$db_name
   //To run specified transactions per terminal, set runMins to zero.
   runTxnsPerTerminal=0
   //To run for a specified number of minutes, set runTxnsPerTerminal 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
   

   Key parameters in prop.oceanbase:

   • 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:

       • If data loading is slow, log in to the tenant and use show full processlist to check if batch statements are enabled.

       • Batch updates are also used in new order transactions, so enable this for both data loading and benchmarking.

     • useLocalSessionState: specifies whether local JDBC values for autocommit, read_only, and transaction isolation are used. Set to true for better performance. If set to false, more requests are sent to the server, which can reduce performance.

   • terminals: the number of concurrent threads. In the MySQL-compatible tenant configuration, the number of concurrent threads must be dynamically adjusted based on specific configurations.

   • warehouses/loadWorkers: specifies the amount of data to be loaded. You can adjust these parameters as needed.

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

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

   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 Java Connector driver. 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. Modify the benchmarksql-5.0/run/runDatabaseBuild.sh file.

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

9. Modify the SQL statements in BenchmarkSQL 5.0.

   1. Back up 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;
      

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

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

      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;
      

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

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

Step 4: Tune the environment

1. Tune ODP.

   Tune ODP by running these commands in the system tenant (sys):

   ALTER PROXYCONFIG SET proxy_mem_limited='4G';
   
   ALTER PROXYCONFIG SET enable_compression_protocol=false;
   

2. Tune OceanBase Database.

   Tune OceanBase Database by running these commands in the system tenant (sys):

   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 tuning, execute the obd cluster restart $cluster_name command to restart the cluster.

Step 5: Run the TPC-C benchmark

In the benchmarksql-5.0/run directory, execute these commands:

1. Initialize the environment.

   ./runDatabaseDestroy.sh prop.oceanbase
   

2. Create tables and import data.

   ./runDatabaseBuild.sh prop.oceanbase
   

3. Execute a major compaction (log in as the sys tenant).

   A major compaction merges the current SSTable and MemTable with previous static data, making storage statistics more accurate and execution plans more stable.

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

   Execute the obclient -h$host_ip -P$host_port -u$user@$tenant -p$password -A -D$database command to log in to the test tenant.

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

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

   ./runBenchmark.sh prop.oceanbase
   

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

Common error messages

• How to stop the BenchmarkSQL tool.

  Automatic stop: depends on the value of runMins in prop.oceanbase.

  Force stop: run the ps auxf | grep java -- command to find and kill the runBenchmark Java process.

• The main class is not found. The error message is as follows:

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

  This is usually caused by an outdated JDK or JDBC version. Install Java with:

  sudo yum install jdk
  

• The number of terminals is invalid. The error message is as follows:

  Invalid number of terminals!
  

  The terminals value in prop.oceanbase is out of range. Set it so that numTerminals > 0 && numTerminals <= 10 * numWarehouses.

• Transaction timeout. The error message is as follows:

  transaction timeout
  

  Increase the timeout by running the following in the test tenant: set global ob_query_timeout=36000000000;set global ob_trx_timeout=36000000000.