Run the Sysbench benchmark test with OceanBase Database

2026-01-19 13:13:23  Updated

This topic describes how to use the Sysbench tool to test the performance of OceanBase Database.

What is Sysbench

Sysbench is a multi-threaded benchmarking tool built on LuaJIT that lets you customize test logic with scripts. It is widely used to evaluate the performance of core system components—such as CPU, memory, threads, disk I/O, and databases—under different system configurations. With Sysbench, you do not need to modify any source code; simply write your own Lua scripts to simulate a variety of business scenarios. Supported test types include CPU performance, disk I/O, thread scheduling, memory allocation and access speed, POSIX thread performance, and database performance. In this topic, we will focus on testing database performance.

Note

To deliver a better user experience and make OceanBase Database easier to use, the database has undergone extensive optimization since V4.0.0. The performance testing approach described here focuses on tuning basic parameters, helping developers achieve strong database performance out of the box.

Environment setup

Before running tests, prepare your environment as follows:

Note

This example uses a MySQL-compatible tenant.

Software requirements

  • JDK: Version 1.8u131 or later is recommended.

  • make: Install with yum install make.

  • automake: Install with yum install automake.

  • autoconf: Install with yum install autoconf.

  • libtool: Install with yum install libtool.

  • GCC: Install with yum install gcc.

  • mariadb-devel: Install with yum install mariadb-devel mariadb.

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

  • Sysbench: Version 1.0 or later is recommended.

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

    Notice

    Starting from OBClient V2.2.0, the ob20 protocol and end-to-end tracing are enabled by default, which may impact Sysbench performance. To avoid this, set the environment variable export ENABLE_PROTOCOL_OB20=0 to disable these features.

  • OceanBase Database: We recommend that you deploy OceanBase Database Proxy (ODP) separately. 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 Sysbench benchmark report of OceanBase Database. Adjust these settings according to your actual hardware.

  • Cluster deployment

    1. This test uses five machines: one for both Sysbench and OceanBase Deployer (obd), one dedicated to ODP, and three for the OceanBase cluster (deployed via obd) in a 1:1:1 configuration.

      Note

      Configure the machine hosting Sysbench, ODP, and obd according to the total number of OBServer CPU cores:

      • With up to 47 OBServer cores, 8 cores and 64GB RAM are sufficient.
      • For 48 to 72 OBServer cores, use 16 cores and 128GB RAM.
      • With 73 OBServer cores or more, use 32 cores and 128GB RAM.

    2. After deployment, create a dedicated tenant and user for Sysbench testing. The sys tenant is reserved for cluster management and is not intended for testing. Set the tenant's primary_zone to RANDOM, so the leader of new table partitions is randomly assigned to one of the three machines.

      Note

      Avoid using the obd cluster autodeploy command during cluster deployment. This command restricts resource usage (such as reserving extra memory) to ensure system stability. For better resource utilization, manually adjust the configuration files as needed.

  • Tenant creation

    Create a test tenant using the OBD CLUSTER TENANT CREATE command. The syntax is as follows:

    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>

    Parameters 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 for the tenant.

    • --locality: the distribution of replicas across zones.

    • --charset: the character set of the tenant.

    • -s: the system variable value of the tenant.

    • OPTIMIZE: the load type of the tenant. Valid values: express_oltp, complex_oltp, olap, htap, and kv. The default value is htap, which is suitable for hybrid OLAP and OLTP workloads. For more information about obd deployment, see obd cluster tenant create.

      Notice

      For versions V4.3.x and above, specify the cluster workload type by setting the scenario configuration during obd deployment. If not set, scenario defaults to htap. For more information, see Deploy an OceanBase cluster on the GUI.

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

    obd cluster tenant create obperf -n sysbench_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 the workload type that best fits your actual cluster scenario.

Test methods

Once your test environment is set up, you can run Sysbench performance tests in two ways:

  • Automatically with the obd tool

  • Manually using the Sysbench tool

Run Sysbench tests automatically with the obd tool

Keep these points in mind before using the obd tool for Sysbench testing:

  • For a detailed guide on running Sysbench with obd, see the documentation for obd test sysbench.
  • Automated testing with obd requires the cluster to be installed and deployed using obd. If the cluster was set up another way, obd will not be able to access cluster information or optimize performance based on the configuration.
  • If you changed the system tenant password from the default (empty) value via the terminal, reset it to the default first. Then, update the password in the configuration file using the obd cluster edit-config command. The relevant setting is # root_password: # root user password. After editing the config, run obd cluster reload.
  • When you run obd test sysbench, the system will walk you through each step and display the output. The larger the data set, the longer the test will take.
  • obd test sysbench handles everything automatically—including generating test data, tuning OceanBase parameters, loading data, and running the tests. If any step fails, check the documentation for obd test sysbench to retry. For example, you can skip data generation and go straight to loading and testing.

Steps to run Sysbench tests automatically with obd:

  1. Install the required dependencies:

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

  2. Install the Sysbench tool:

    sudo yum install ob-sysbench

  3. Write the ob_sysbench.sh script:

    #!/bin/bash
export ENABLE_PROTOCOL_OB20=0

echo "run oltp_read_only test"
obd test sysbench <DEPLOY_NAME> --tenant=<TENANT_NAME> --script-name=oltp_read_only.lua --table-size=1000000 --threads=32 --rand-type=uniform
obd test sysbench <DEPLOY_NAME> --tenant=<TENANT_NAME> --script-name=oltp_read_only.lua --table-size=1000000 --threads=64 --rand-type=uniform
obd test sysbench <DEPLOY_NAME> --tenant=<TENANT_NAME> --script-name=oltp_read_only.lua --table-size=1000000 --threads=128 --rand-type=uniform
obd test sysbench <DEPLOY_NAME> --tenant=<TENANT_NAME> --script-name=oltp_read_only.lua --table-size=1000000 --threads=256 --rand-type=uniform
obd test sysbench <DEPLOY_NAME> --tenant=<TENANT_NAME> --script-name=oltp_read_only.lua --table-size=1000000 --threads=512 --rand-type=uniform
obd test sysbench <DEPLOY_NAME> --tenant=<TENANT_NAME> --script-name=oltp_read_only.lua --table-size=1000000 --threads=1024 --rand-type=uniform

echo "run oltp_write_only test"
obd test sysbench <DEPLOY_NAME> --tenant=<TENANT_NAME> --script-name=oltp_write_only.lua --table-size=1000000 --threads=32 --rand-type=uniform
obd test sysbench <DEPLOY_NAME> --tenant=<TENANT_NAME> --script-name=oltp_write_only.lua --table-size=1000000 --threads=64 --rand-type=uniform
obd test sysbench <DEPLOY_NAME> --tenant=<TENANT_NAME> --script-name=oltp_write_only.lua --table-size=1000000 --threads=128 --rand-type=uniform
obd test sysbench <DEPLOY_NAME> --tenant=<TENANT_NAME> --script-name=oltp_write_only.lua --table-size=1000000 --threads=256 --rand-type=uniform
obd test sysbench <DEPLOY_NAME> --tenant=<TENANT_NAME> --script-name=oltp_write_only.lua --table-size=1000000 --threads=512 --rand-type=uniform
obd test sysbench <DEPLOY_NAME> --tenant=<TENANT_NAME> --script-name=oltp_write_only.lua --table-size=1000000 --threads=1024 --rand-type=uniform

echo "run oltp_read_write test"
obd test sysbench <DEPLOY_NAME> --tenant=<TENANT_NAME> --script-name=oltp_read_write.lua --table-size=1000000 --threads=32 --rand-type=uniform
obd test sysbench <DEPLOY_NAME> --tenant=<TENANT_NAME> --script-name=oltp_read_write.lua --table-size=1000000 --threads=64 --rand-type=uniform
obd test sysbench <DEPLOY_NAME> --tenant=<TENANT_NAME> --script-name=oltp_read_write.lua --table-size=1000000 --threads=128 --rand-type=uniform
obd test sysbench <DEPLOY_NAME> --tenant=<TENANT_NAME> --script-name=oltp_read_write.lua --table-size=1000000 --threads=256 --rand-type=uniform
obd test sysbench <DEPLOY_NAME> --tenant=<TENANT_NAME> --script-name=oltp_read_write.lua --table-size=1000000 --threads=512 --rand-type=uniform
obd test sysbench <DEPLOY_NAME> --tenant=<TENANT_NAME> --script-name=oltp_read_write.lua --table-size=1000000 --threads=1024 --rand-type=uniform

    Replace DEPLOY_NAME with your cluster name and TENANT_NAME with your tenant name as needed.

  4. Run the test script:

    chmod +x ob_sysbench.sh
./ob_sysbench.sh

  5. (Optional) After the tests are complete and you reviewed the results, clean up the test data:

    obd test sysbench <deploy_name> --tenant=<tenant_name> --cleanup

Run Sysbench tests manually with the Sysbench tool

Manual testing allows you to select the cluster workload type and tenant tuning scenario, making it easier to understand OceanBase Database in depth—especially for optimizing parameter settings.

Step 1: Create a test tenant

Note

If you already created the test tenant during environment setup, you can skip this step.

Run these commands under the system tenant (sys tenant) to create your test tenant:

Note

This example uses a 1:1:1 OceanBase cluster deployment.

  1. Create a resource unit named mysql_box.

    CREATE RESOURCE UNIT mysql_box
   MAX_CPU 28,
   MEMORY_SIZE '200G',
   MIN_IOPS 200000,
   MAX_IOPS 12800000,
   LOG_DISK_SIZE '300G';

  2. Create a resource pool named mysql_pool.

    CREATE RESOURCE POOL mysql_pool
   UNIT = 'mysql_box',
   UNIT_NUM = 1,
   ZONE_LIST = ('z1','z2','z3');

  3. Create a MySQL-compatible tenant named mysql_tenant.

    CREATE TENANT mysql_tenant
   RESOURCE_POOL_LIST = ('mysql_pool'),
   PRIMARY_ZONE = RANDOM,
   LOCALITY = 'F@z1,F@z2,F@z3'
   SET VARIABLES ob_compatibility_mode='mysql', ob_tcp_invited_nodes='%', secure_file_priv = "/";

Step 2: Tune your environment

Before starting the test, optimize OceanBase Database by running these commands under the system tenant (sys tenant):

ALTER SYSTEM SET enable_sql_audit=false;
ALTER SYSTEM SET enable_perf_event=false;
ALTER SYSTEM SET syslog_level='PERF';
ALTER SYSTEM SET enable_record_trace_log=false;

Step 3: Install Sysbench

Follow the steps below to install Sysbench.

  1. Download Sysbench.

    For more information, see Sysbench download address.

  2. Extract the Sysbench package.

    unzip ./1.0.20.zip

  3. Compile Sysbench.

    Change to the extracted directory and run:

    [wieck@localhost ~] $ cd sysbench-1.0.20
[wieck@localhost sysbench-1.0.20] $./autogen.sh
[wieck@localhost sysbench-1.0.20] $./configure --prefix=/usr/sysbench/ --with-mysql-includes=/usr/include/mysql/ --with-mysql-libs=/usr/lib64/mysql/ --with-mysql
[wieck@localhost sysbench-1.0.20] $make
[wieck@localhost sysbench-1.0.20] $make install
[wieck@localhost sysbench-1.0.20] $cp -r /usr/sysbench/share/sysbench/* /usr/sysbench/bin/

    Parameter description:

    Parameter Description
    --prefix Specifies the installation directory of Sysbench.
    --with-mysql-includes Specifies the includes directory of MySQL.
    --with-mysql-libs Specifies the lib directory of MySQL.
    --with-mysql Indicates that Sysbench supports MySQL by default.

  4. Verify the installation:

    [wieck@localhost sysbench-1.0.20] $./src/sysbench --help

    If you see something like this, Sysbench is installed successfully:

    Usage:
   sysbench [options]... [testname] [command]
Commands implemented by most tests: prepare run cleanup help

Step 4: Generate test data

Test configuration:

  • Data size: 1 million rows per table, 30 tables total
  • Load: 150 concurrent threads
  • Statistics: Real-time statistics every 10 seconds
  • Random mode: Uniform distribution
  • Duration: 60 seconds

Steps to run Sysbench:

  1. Initialize the test data.

    /usr/sysbench/bin/sysbench oltp_read_write.lua --mysql-host=x.x.x.x --mysql-port=xxxx --mysql-db=test --mysql-user=$user@$tenant --mysql-password=xxx --table_size=1000000 --tables=30 --threads=150 --report-interval=10 --rand-type=uniform --time=60 cleanup

  2. Generate the test data.

    /usr/sysbench/bin/sysbench oltp_read_write.lua --mysql-host=x.x.x.x --mysql-port=xxxx --mysql-db=test --mysql-user=$user@$tenant --mysql-password=test --table_size=1000000 --tables=30 --threads=150 --report-interval=10 --rand-type=uniform --time=60 prepare

  3. Run the performance test.

    /usr/sysbench/bin/sysbench oltp_read_write.lua --mysql-host=x.x.x.x --mysql-port=xxxx --mysql-db=test --mysql-user=$user@$tenant --mysql-password=xxx --table_size=1000000 --tables=30 --threads=150 --report-interval=10 --time=60 --rand-type=uniform --db-ps-mode=disable run

    Parameter description:

    Parameter Description
    --mysql-host The IP address of the server where OceanBase Database is deployed. Use ODP's IP if available.
    --mysql-port The port number.
    --mysql-db The database to be connected.
    --mysql-user The username.
    --mysql-password The password.
    --table_size The number of data rows in each table.
    --tables The number of tables to be initialized.
    --threads The number of threads to be started.
    --time The test duration. If the value is set to 0, the test will not be time-limited.
    --report-interval The interval for outputting logs during the test, in seconds.
    --events The maximum number of requests. If this parameter is specified, the --time parameter is not required.
    --rand-type The random number generation function used for data access. Valid values: special, uniform, gaussian, and pareto. Default value: special. The value before the upgrade was uniform.
    --skip_trx=on Specifies whether to enable transactions in read-only tests. Default value: enabled.
    --percentile=N The percentile of response time to be printed. Default value: 95.
    oltp_write_only The Sysbench lua directory contains test cases for different scenarios, such as insert and point_select.

Reference

For more information about the test results, see Sysbench test report of OceanBase Database.

Common error messages

  • Sysbench installation fails with:

    automake 1.10.x (aclocal) wasn't found, exiting

    This indicates that Automake is not installed on the operating system. Run the following command to install Automake:

    yum install automake.noarch

  • Sysbench installation fails with:

    libtoolize 1.4+ wasn't found, exiting

    This indicates that Libtool is not installed on the operating system. Run the following command to install Libtool:

    yum install libtool

  • Sysbench installation fails with:

    drv_mysql.c:35:19: fatal error: mysql.h: No such file or directory

    This indicates that the MySQL library is not installed on the operating system. Run the following command to install the library:

    yum install mysql-community-devel.x86_64

  • Sysbench installation fails with:

    cannot find MySQL client libraries in /usr/lib/mysql/

    Run the following command to locate the MySQL library. It may be located at /usr/lib64/mysql.

    find /usr -name mysql

  • Performance is poor even with correct Sysbench and OceanBase Database configuration:

    1. Sysbench is sensitive to CPU, memory, and network. If the client and database are not on the same LAN, network latency can impact performance.

    2. If you are using ODP with your OceanBase cluster, try running a high-concurrency test directly on a single OceanBase process and compare results.

    3. We recommend that you deploy ODP and Sysbench on the same server and run Sysbench through ODP.

  • Why is overall CPU usage still low under heavy load?

    While overall CPU usage may appear low, some modules can still be using a high percentage of CPU resources.

Last
Next

OceanBaseOceanBase

OceanBase, A Highly Scalable Database for Transactional, Analytical, and AI Workloads.

Company

About OceanBaseContact UsPartnerTrust Center

Product

OceanBase CloudOceanBase EnterpriseQuick StartDownloadPricing

Resources

DocsBlogWhite Paper

Follow us

LinkedInLinkedInDiscordDiscordTwitterTwitterYoutubeYoutubeForumForumGitHubGitHubStack OverflowStack Overflow

© OceanBase 2024. All rights reserved | Cloud Service Agreement | Privacy Policy | Legal | Security

Contact Us