Experience high-concurrency OLTP scenarios

OceanBase Database is based on the LSM-Tree storage architecture and a native distributed design, delivering excellent throughput and low latency in high-concurrency transaction processing (OLTP) scenarios. This topic helps you directly experience the OLTP capabilities of OceanBase Database through data import and stress testing.

This topic covers the following experiences:

• Data import: Use direct load to experience OceanBase Database's high-throughput data loading capability.
• Sysbench stress testing: Use obd test sysbench to start an OLTP benchmark in one step and experience high-concurrency read/write performance.
• TPC-C stress testing: Use obd test tpcc to start a TPC-C test in one step and experience high-concurrency transaction processing.

Prerequisites

Before you perform the operations in this topic, ensure that the following conditions are met:

In the command-line examples below, a leading $ indicates that the command is run as a regular user, and obclient [database name]> indicates that an SQL statement is run in a database session. Do not type the prompt itself.

Create a test tenant

The sys tenant is a built-in management tenant in OceanBase Database, used for cluster management and maintenance operations. It is not recommended to use the sys tenant directly for stress testing. Before performing data import and stress testing, you must first create an independent business tenant and allocate appropriate resource specifications to it.

Resource specification description

OceanBase Database allocates computing resources through the hierarchy "Unit Config → Resource Pool → Tenant". A unit config defines the CPU and memory specifications, representing the upper limit of resources available to a tenant.

Procedure

1. Log in to the sys tenant of the cluster as the root user (the password is randomly generated during deployment and can be found in the deployment output or in the output of the obd cluster display demo command. For details, see Deploy OceanBase):

   $ obclient -h127.0.0.1 -uroot@sys -P2881 -p -A oceanbase
   

2. Create a unit config. The following example creates a 2C4G unit config. You can adjust MAX_CPU and MEMORY_SIZE based on your machine configuration:

   obclient [oceanbase]> CREATE RESOURCE UNIT oltp_unit MAX_CPU=2, MIN_CPU=2, MEMORY_SIZE='4G';
   

   The return result is Query OK, 0 rows affected.

3. Create a resource pool and associate it with the unit config. For a single-node cluster, the ZONE_LIST is ('zone1'):

   obclient [oceanbase]> CREATE RESOURCE POOL oltp_pool UNIT='oltp_unit', UNIT_NUM=1, ZONE_LIST=('zone1');
   

   The return result is Query OK, 0 rows affected.

4. Create a MySQL-compatible tenant, associate it with the resource pool, and set the allowlist:

   obclient [oceanbase]> CREATE TENANT IF NOT EXISTS oltp_tenant CHARSET='utf8mb4', PRIMARY_ZONE='zone1', RESOURCE_POOL_LIST=('oltp_pool') SET ob_tcp_invited_nodes='%';
   

   The return result is Query OK, 0 rows affected. Resources are actually allocated when the tenant is created, which takes some time. Please be patient.

5. Confirm that the tenant has been created successfully:

   obclient [oceanbase]> SELECT TENANT_ID, TENANT_NAME, TENANT_TYPE, STATUS FROM DBA_OB_TENANTS;
   

   When a record with TENANT_NAME as oltp_tenant and STATUS as NORMAL appears in the query result, the tenant has been created successfully (the query result also includes records for the sys tenant and its corresponding Meta tenant). For detailed column descriptions, see DBA_OB_TENANTS.

6. Log in to the new oltp_tenant tenant. The root user of a new tenant has an empty password by default, so press Enter when prompted. test is the default empty database that comes with the tenant and can be used for testing directly. All subsequent operations are performed in this tenant:

   $ obclient -h127.0.0.1 -uroot@oltp_tenant -P2881 -p -A test
   

Experience data import

OceanBase Database supports multiple data import methods. Among them, direct load bypasses the SQL layer and the memory table (MemTable), writing data directly to the underlying storage (SSTable), thereby significantly improving the throughput of large-scale data loading.

Direct load is suitable for the following scenarios:

• Full data loading during data migration and synchronization
• Large-scale data import in ETL processes
• Loading data from text files or other data sources into OceanBase

Procedure

The following example uses LOAD DATA INFILE combined with the direct load hint to import data from a CSV file into a test table.

1. Prepare the test data file on the machine where the OBServer node resides:

   $ cat > /tmp/tbl1.csv << 'EOF'
   1,11
   2,22
   3,33
   EOF
   

2. Set the secure_file_priv system variable to allow OceanBase to read files from the /tmp directory. For security reasons, this variable can only be modified over a local Unix Socket connection. The socket file path depends on the deployment method (it is usually ~/oceanbase-ce/run/sql.sock for obd demo). Find the path with the following command, and then use it to connect to the oltp_tenant tenant (the root user password of the tenant is empty by default; press Enter when prompted):

   $ find / -name sql.sock 2>/dev/null
   $ obclient -S /path/to/sql.sock -uroot@oltp_tenant -p
   

   After a successful connection, execute the following statement:

   obclient [(none)]> SET GLOBAL secure_file_priv = "/tmp";
   

   Note

   secure_file_priv restricts the file access paths for statements like LOAD DATA INFILE. Modifications take effect after reconnecting to the database. For more information, see secure_file_priv.

3. Reconnect to the oltp_tenant tenant and create a test table:

   $ obclient -h127.0.0.1 -uroot@oltp_tenant -P2881 -p -A test
   

   obclient [test]> CREATE TABLE tbl2 (
                       col1 INT PRIMARY KEY,
                       col2 INT
                    );
   

   The return result is Query OK, 0 rows affected.

4. Use direct load to import the data from the CSV file into the tbl2 table:

   obclient [test]> LOAD DATA /*+ direct(true,1024,'full') parallel(4) */ INFILE '/tmp/tbl1.csv' INTO TABLE tbl2 FIELDS TERMINATED BY ',';
   

   Note

   In the hint, direct(true,1024,'full') enables full direct load, and parallel(4) sets the degree of parallelism to 4. Full direct load is suitable for the initial loading of an empty table. If the target table already contains data, you can change the third parameter to 'inc' (incremental direct load) or 'inc_replace' (incremental replace). For more information, see Import data by using the LOAD DATA statement.

   After the command is successfully executed, obclient returns the import result, which includes the number of affected rows, skipped rows, and warnings:

   Query OK, 3 rows affected
   Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
   

   Note

   To evaluate the performance advantages of direct load, you can use the time command in the operating system together with obclient -e to execute the LOAD DATA statement with and without the DIRECT hint to import the same data and compare the execution times. For details, see Experience parallel import and high compression. Direct load skips the SQL layer and MemTable, so the import speed is usually significantly faster than regular import in scenarios with large data volumes.

5. Verify whether the data was imported successfully:

   obclient [test]> SELECT * FROM tbl2;
   

   The following return result indicates a successful import:

   +------+------+
   | col1 | col2 |
   +------+------+
   |    1 |   11 |
   |    2 |   22 |
   |    3 |   33 |
   +------+------+
   

Experience Sysbench stress testing

Sysbench is a multi-threaded benchmarking tool based on LuaJIT that is commonly used to evaluate database performance under different workloads. OceanBase provides the obd test sysbench command, which can generate test data, tune parameters, load data, and run a stress test in one step.

About Sysbench

Sysbench primarily includes the following OLTP test scenarios:

• oltp_read_only: read-only test
• oltp_write_only: write-only test
• oltp_read_write: read/write mixed test
• oltp_point_select: point query test

Procedure

1. Install ob-sysbench:

   $ sudo yum install -y yum-utils
   $ sudo yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo
   $ sudo yum install ob-sysbench
   

   Note

   • If your machine cannot connect to the external network, we recommend using a machine that can access the internet to visit OceanBase Developer Tools Download. Download the corresponding installation package for your system version and upload it for installation.

   • If you receive the error 404 for .../repodata/repomd.xml when executing yum install (for example, if the system version is not within the el7/8/9 range supported by the image, such as on Alibaba Cloud Linux 3), you can append --releasever=8 to the installation command to use the el8 image source. For example: sudo yum install ob-sysbench --releasever=8.

2. Run the following command as the same user that deployed obd demo to start a read/write mixed stress test in one step. OBD locates the deployment from the current user's cluster configuration, so run this command under the account used for the deployment:

   $ obd test sysbench demo --tenant=oltp_tenant --script-name=oltp_read_write.lua --table-size=1000000 --threads=32 --rand-type=uniform
   

   Key parameters in the command:

   Parameter	Description
   demo	The deployment name, fixed as demo when using obd demo for deployment.
   --tenant	The name of the test tenant, which must be created in advance.
   --script-name	The name of the Sysbench test script. Multiple names are supported, separated by commas.
   --table-size	The number of data rows initialized for each table, default is 20,000.
   --threads	The number of concurrent threads. Multiple values are supported, separated by commas.
   --rand-type	The random data generation function. We recommend using uniform.

   Note

   • obd test sysbench automatically completes all operations, including generating test data, tuning OceanBase parameters, loading data, and executing stress testing, requiring no additional intervention. If an error occurs during any step, you can refer to the obd test sysbench command to retry.

   • The command initializes 30 tables by default (adjustable via --tables). --table-size=1000000 indicates loading 1 million rows of data per table. Data preparation time depends on the machine specifications. The stress test phase runs for 60 seconds by default, adjustable via --time. Note that the default maximum data disk size for a deployment by obd demo is 8 GB. With the default 30 tables, each preparing 1 million rows, the peak usage from data loading and compaction will fill the data disk, causing the subsequent Experience TPC-C Stress Testing data preparation stage to become unresponsive for a long time. For minimum-specification deployment, we recommend reducing --table-size (e.g., to 100000) or decreasing the number of tables via --tables.

3. View the test results. After the command finishes executing, Sysbench outputs the following key metrics:

   Metric	Description
   QPS (Queries Per Second)	The number of queries processed per second, which measures the database's query throughput. A higher value indicates stronger throughput capability.
   TPS (Transactions Per Second)	The number of transactions processed per second, which measures transaction throughput capability. A higher value is better.
   95% Latency (ms)	The 95th percentile response time, indicating how many milliseconds it takes to complete 95% of requests. A lower value indicates lower latency.

   The following example shows the test results (actual values depend on hardware configuration and tenant specifications):

   SQL statistics:
       transactions:                        120000 (2000.00 per sec.)
       queries:                             340000 (5666.67 per sec.)
       ...
   Latency (ms):
            min:                                  1.23
            avg:                                 12.34
            max:                                 98.76
            95th percentile:                     23.45
   

   Note

   This document uses a single-node minimum-specification deployment. The test results are primarily used to verify the feature process and do not represent the production-level performance of OceanBase Database. For the official performance test report, see OceanBase Sysbench Performance Test Report. This report presents the test results for OceanBase Database V4.2.1 in a 3-node, 32C, 128G cluster environment, achieving QPS of 138,000 with a 95% latency of only 0.26 ms under Point Select with 32 threads.

4. You can also run multiple rounds of comparative testing with different scripts and thread counts to observe the trend of QPS increasing with concurrency and the growth of latency:

   $ obd test sysbench demo --tenant=oltp_tenant --script-name=oltp_point_select.lua --table-size=1000000 --threads=64 --rand-type=uniform
   

Experience TPC-C stress testing

TPC-C is an OLTP performance benchmark developed by the TPC organization. It uses a merchandise sales model to test database systems and serves as an important reference for performance testing in the database industry. OceanBase Database broke the TPC-C world record twice, in 2019 and 2020.

OceanBase provides the obd test tpcc command, which can perform one-click generation of TPC-C test data, parameter tuning, data loading, and stress testing execution.

Procedure

1. Install obtpcc (TPC-C testing is based on BenchmarkSQL; you must also install the Java runtime environment. This step can be skipped if Java is already installed on your machine):

   $ sudo yum install -y yum-utils
   $ sudo yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo
   $ sudo yum install obtpcc java
   

   If you encounter issues such as a 404 error for the repository or no access to the external network during installation, see the instructions in Step 1 of "Experience Sysbench stress testing".

2. (Optional) If you have previously completed a Sysbench test using --table-size=1000000, the test data will fill up the data disk of the minimum-specification deployment. You need to recreate the test database first to free up space (this operation will delete all data tables in the database, including the Sysbench test tables and the tbl2 table created during data import).

   $ obclient -h127.0.0.1 -uroot@oltp_tenant -P2881 -p -A -e "DROP DATABASE IF EXISTS test; CREATE DATABASE test;"
   

3. As the same user that deployed obd demo, run the following command to start the TPC-C test in one step:

   $ obd test tpcc demo --tenant=oltp_tenant --warehouses=10 --run-mins=5
   

   Key parameters in the command:

   Parameter	Description
   demo	The deployment name, fixed as demo when using obd demo.
   --tenant	The test tenant name, which must be created in advance.
   --warehouses	The number of warehouses for the TPC-C test. The number of warehouses determines the test data scale, defaulting to 10. If not specified, it defaults to the cluster's CPU count × 20.
   --run-mins	The test execution time, in minutes. Default is 10 minutes.
   --load-workers	The concurrency level during data loading. If not specified, it defaults to the smaller value between the single-machine CPU count and the tenant's available memory (GB) / 2.
   --terminals	The number of virtual terminals during testing. If not specified, it defaults to the smaller value between the cluster's CPU count × 15 and the number of warehouses × 10.

   Note

   obd test tpcc automatically completes all operations, including test data generation, parameter tuning, data loading, and stress testing execution. The process lists detailed steps and outputs. A larger data volume takes longer.

4. View the test results. After the command finishes executing, it outputs key TPC-C test metrics:

   Metric	Description
   Measured tpmC (NewOrders)	New orders processed per minute. This is the core metric of the TPC-C test. A higher value indicates stronger transaction processing capability.
   Measured tpmTOTAL	Total transactions processed per minute (including five types: new orders, payments, order status, delivery, and inventory queries).
   Transaction Count	Total number of transactions completed during the test.

   A sample test result is shown below (actual values depend on hardware configuration and tenant specifications):

   Measured tpmC (NewOrders)   : 9968.91
   Measured tpmTOTAL           : 22048.03
   Session Start               = 2026-10-10 12:26:45
   Session End                 = 2026-10-10 12:31:46
   Transaction Count           = 110406
   

   Note

   tpmC (Transactions per Minute) is the core metric of the TPC-C benchmark, based on NewOrder transactions. This document uses a single-node minimum specification deployment, so the test results are primarily for verifying functional processes. For the official performance test report, see OceanBase TPC-C Performance Test Report. This report presents the test results for OceanBase Database V4.2.3 in a 3-node, 32C, 128G cluster with 1000 warehouses, achieving over 300,000 tpmC.

Next steps

After you complete the OLTP stress testing in this topic, you can explore the following:

1. Experience HTAP mixed workload: Experience OceanBase Database's HTAP capability through row-store + column-store tables, running both transactional and analytical queries on the same table. For details, see Experience HTAP mixed workload scenarios.
2. Experience multi-tenancy features: Create multiple business tenants to experience resource isolation and elastic scaling. For details, see Experience multi-tenancy and resource isolation.
3. Distributed cluster stress testing: To experience OceanBase Database's scalable OLTP capability (linear expansion across multiple nodes), it is recommended to use a cluster of at least three nodes for testing, and set the tenant's PRIMARY_ZONE to RANDOM. For details, see OceanBase Database TPC-C testing.

References

• For detailed instructions on direct load, see Overview of direct load.
• For the complete syntax and more examples of the LOAD DATA statement, see Import data by using the LOAD DATA statement.
• For the complete steps of manual Sysbench testing, see Sysbench testing for OceanBase Database.
• For the complete steps of manual TPC-C testing, see TPC-C testing for OceanBase Database.
• For detailed parameters of OBD test commands, see Test command group.
• For creating and managing multi-tenancy, see Experience multi-tenancy and resource isolation.