Run the Sysbench benchmark on OceanBase Database|V4.2.5| docs|Distributed Database

Run the Sysbench benchmark on OceanBase Database

Last Updated：2025-01-02 01:58:39 Updated

This topic describes how to use Sysbench to test the online transaction processing (OLTP) performance of OceanBase Database. In this topic, you can run the Sysbench benchmark on OceanBase Database in the following two ways:

Use OceanBase Deployer (obd) to run the benchmark.
Use the official Sysbench tool to manually run the Sysbench benchmark step by step.

Note

OceanBase Database V4.0.0 and later are extensively optimized to improve the user experience, ease of use, and performance. You can use Sysbench only for performance tuning based on basic parameters.

Introduction to Sysbench

Sysbench is a LuaJIT-based multi-thread benchmark tool that allows you to write scripts and test the CPU, memory, thread, I/O, and database performance. It is often used for evaluating and testing the database workload under various system parameters. You can run the Sysbench benchmark in diversified business scenarios by customizing Lua scripts without modifying the source code. The Sysbench benchmark covers the following aspects:

CPU performance
Disk I/O performance
Scheduler performance
Memory allocation and transmission speed
POSIX thread performance
Database performance (OLTP benchmark)

Prepare the environment

Java Development Kit (JDK): Use JDK 1.8u131 or later.
Make: Run the yum install make command to install make.
Automake: Run the yum install automake command to install Automake.
Autoconf: Run the yum install autoconf command to install Autoconf.
Libtool: Run the yum install libtool command to install Libtool.
GCC: Run the yum install gcc command to install GCC.
Mariadb-devel: Run the yum install mariadb-devel mariadb command to install Mariadb-devel.
Java Database Connectivity (JDBC): Use MySQL Connector/J 5.1.47.
Sysbench: Use Sysbench 1.0 or later.
OBClient: For more information, see OBClient documentation.

Notice

In OBClient V2.2.0 or later, the OceanBase 2.0 protocol and end-to-end tracing are enabled by default, which will affect the performance in the Sysbench benchmark. We recommend that you disable the protocol by executing the export ENABLE_PROTOCOL_OB20=0 statement.
OceanBase Database: We recommend that you deploy OceanBase Database Proxy (ODP). For more information, see Quick start with OceanBase Database Community Edition.
IOPS: We recommend that the disk IOPS be above 10,000.
Tenant specifications:
```
CREATE RESOURCE UNIT sysbench_unit max_cpu 26, memory_size '100g';
CREATE RESOURCE POOL sysbench_pool unit = 'sysbench_unit', unit_num = 1, zone_list=('zone1','zone2','zone3');
CREATE TENANT sysbench_tenant resource_pool_list=('sysbench_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 configurations in Sysbench benchmark report of OceanBase Database. You need to adjust the specifications based on the hardware configurations of your database.
- We recommend that you do not use the obd cluster autodeploy command to deploy the cluster. To ensure stability, this command does not maximize the resource utilization. For example, it does not use all of the memory. If you use this command, we recommend that you modify the configuration file separately to maximize the resource utilization.

Test plan

Five servers are required to run this test. Deploy Sysbench and obd on the same server, and deploy ODP on a separate server. Deploy an OceanBase cluster using obd on the other three servers in the 1-1-1 architecture, which means that the OceanBase cluster has three zones, with each containing one OBServer node.
Note

In the Sysbench benchmark, you can flexibly configure the servers on which Sysbench, ODP, and 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.
After successful deployment, create the tenant and users required for running the Sysbench benchmark. The sys tenant is a built-in system tenant used 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, which indicates that the leader of the new table partitions is randomly assigned to one of the three OBServer nodes.
Use Sysbench to import 30 tables. Each table has 1 million rows of data.
Launch the Sysbench client, and run the POINT_SELECT, READ_WRITE, READ_ONLY, and WRITE_ONLY tests.
Set the time parameter to 60s for each round of test. The number of threads can be 32, 64, 128, 256, 512, or 1024.

Use obd to run the benchmark

Add 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

Note that deploy_name specifies the name of the deployed cluster, and tenant_name specifies the name of the tenant. You must modify them as needed.

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

Notice

For more information about the parameters required for running Sysbench by using obd, see obd test sysbench.
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 in 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 parameter. After the obd cluster edit-config command is executed, you must run the obd cluster reload
After you run the obd test sysbench command, the system lists the test steps and outputs in detail. A larger data amount requires a longer test time.
The obd test sysbench command automatically completes all operations, including test data generation, 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 sysbench. For example, you can skip data generation, and directly load data and run the test.

Manually run the Sysbench benchmark

Test specifications

--mysql-db=test 
--table_size=1000000 
--tables=30 
--threads=32/64/128/256/512/1024 
--report-interval=10
--time=60 
--db-ps-mode=disable
--rand-type=uniform

Install Sysbench

Perform the following steps to install Sysbench:

Download Sysbench.

For more information, see Sysbench Release 1.0.20.
Decompress the Sysbench installation package.
```
unzip ./1.0.20.zip
```

Compile Sysbench.

Enter the directory of Sysbench and run the following command to compile Sysbench:

[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/

The following table describes the parameters.

Parameter	Description
--prefix	The installation directory of Sysbench.
--with-mysql-includes	The directory that contains the MySQL header files.
--with-mysql-libs	The directory that contains the MySQL libraries.
--with-mysql	Specifies whether MySQL is supported. By default, MySQL is supported.

Run the following command to verify whether Sysbench is installed:

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

If the following information is returned, Sysbench is installed:

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

Optimize the test environment

Before you start the Sysbench benchmark, you need to configure OceanBase Database.

Perform OBServer node optimization:

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;

Procedure

Perform the following steps to start the Sysbench benchmark:

Run the following command to initialize the database:

/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

Run the following command to create tables:

/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

Run the following command to perform the 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

The following table describes the parameters.

Parameter	Description
--mysql-host	The IP address of the server that runs OceanBase Database. We recommend that you use the IP address of the ODP server.
--mysql-port	The port number of the OBServer node.
--mysql-db	The database to be connected to.
--mysql-user	The username.
--mysql-password	The password.
--table_size	The volume of initialized data of each table.
--tables	The number of initialized tables.
--threads	The number of initialized threads.
--time	The running duration. The value `0` means no time limit.
--report-interval	The interval in seconds at which runtime logs are reported.
--events	The maximum number of requests. If you specify this parameter, the `time` parameter is optional.
--rand-type	The random number generation function used to access data. Valid values: `special`, `uniform`, `gaussian`, and `pareto`. Default value: `special`.
--skip_trx=on	Specifies whether to enable transactions in the read-only test. Default value: `on`.
--percentile=N	The percentile rank of query response times to count. Default value: `95`.
oltp_write_only	Sysbench comes with test cases for different scenarios, such as `INSERT` and `POINT_SELECT`. You can find them in the `lua` directory.

Note

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

FAQ

What do I do if the Sysbench installation failed with the following error message returned?
```
automake 1.10.x (aclocal) wasn't found, exiting
```
Automake is not installed. Run the following command to install Automake:
```
yum install automake.noarch
```
What do I do if the Sysbench installation failed with the following error message returned?
```
libtoolize 1.4+ wasn't found, exiting
```
Libtool is not installed. Run the following commands to install Libtool:
```
yum install libtool
```
What do I do if the Sysbench installation failed with the following error message returned?
```
drv_mysql.c:35:19: fatal error: mysql.h: No such file or directory
```
The libraries for MySQL are not installed on the operating system. Run the following command to install the libraries:
```
yum install mysql-community-devel.x86_64
```
What do I do if the Sysbench installation failed with the following error message returned?
```
cannot find MySQL client libraries in /usr/lib/mysql/
```
Run the following command to search for the libraries for MySQL. They may be located in the /usr/lib64/mysql directory.
```
find /usr -name mysql
```
I have configured Sysbench and OceanBase Database properly. Why is the benchmark result still unsatisfactory?
1. Sysbench is sensitive to CPU, memory, and network performance. If the client and tested database are not in the same LAN, network latency may deteriorate the Sysbench performance.
2. The use of ODP in the OceanBase cluster may deteriorate the performance. You can perform a separate high-concurrency stress test on a single observer process, and then compare the result with that of the case where ODP is used.
3. We recommend that you deploy ODP and Sysbench on the same server and connect Sysbench to ODP.
Why is the CPU utilization still low under high concurrency stress?

Although the overall CPU utilization is low, some cores may have already been heavily loaded.