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

Run the Sysbench benchmark on OceanBase Database

Last Updated：2024-10-10 02:47:19 Updated

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

One-click Sysbench test using obd.
Manual Sysbench test using the official Sysbench tool.

Note

To enhance user experience and ensure optimal performance, OceanBase Database has been extensively optimized since V4.0.0. The performance testing methods outlined here focus on tuning basic parameters to provide developers with an improved database experience.

What is 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. A Sysbench test covers the following aspects:

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

Environment preparation

Java Development Kit (JDK): Use V1.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-java-5.1.47.
Sysbench: Use Sysbench V1.0 or later.
OBClient: For more information, see OBClient Documentation.

Notice

If you are using OBClient V2.2.0 or later, the OB2.0 protocol and end-to-end tracing are enabled by default. This affects the performance in the Sysbench test. We recommend that you manually disable the protocol by setting the export ENABLE_PROTOCOL_OB20 environment variable to 0.
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 sysbench_unit max_cpu 26, memory_size '70g';
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 tenant specifications mentioned above are based on the hardware configuration in the Sysbench 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

This test requires a total of five servers. Deploy Sysbench and obd on one server, deploy ODP on another server, and set up the OceanBase cluster using obd on the remaining three servers in a 1:1:1 configuration.
Note

In the Sysbench test, you can flexibly configure the servers for Sysbench, 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.
After a successful deployment, create the tenants and users needed for the Sysbench 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.
Import 30 tables through Sysbench, each with 1 million rows of data.
Launch the Sysbench client, and perform the point_select, read_write, read_only, and write_only tests.
Set --time to 60s for each round of test. The number of threads can be 32, 64, 128, 256, 512, or 1024.

One-click Sysbench test using obd

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

In the script above, deploy_name specifies the name of the deployed cluster, and tenant_name specifies the name of the tenant. You need to 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

When using obd to run the Sysbench benchmark, refer to obd test sysbench for detailed parameters.
To use obd for the test, install and deploy the cluster 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 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 data generation, OceanBase parameter optimization, data loading, and testing, without requiring any additional interventions. If any errors occur during the process, refer to obd test sysbench to try again. For example, you can skip data generation to proceed directly with data loading and testing.

Manual Sysbench test using the official Sysbench tool

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 from Sysbench Release 1.0.20.
Unzip the Sysbench installation package.
```
unzip ./1.0.20.zip
```

Compile Sysbench.

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

[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 parameters are described as follows:

Parameter	Description
--prefix	The installation directory of Sysbench.
--with-mysql-includes	The `includes` directory of MySQL.
--with-mysql-libs	The `lib` directory of MySQL.
--with-mysql	Specifies to support MySQL.

Run the following command to check whether Sysbench is installed:

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

If the following information is returned, Sysbench is successfully installed.

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

Environment tuning

Before you start the Sysbench test, it is necessary to complete some basic configurations on OceanBase Database.

OBServer tuning

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;

Sysbench test

Follow these steps to start the Sysbench test:

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 parameters are described as follows:

Parameter	Description
--mysql-host	The IP address of the server running OceanBase database. If ODP is in use, we recommend that you use the IP address of the ODP server.
--mysql-port	The port number.
--mysql-db	The database to connect.
--mysql-user	The username.
--mysql-password	The password.
--table_size	The amount of data initialized per table.
--tables	The number of tables to initialize.
--threads	The number of threads to start.
--time	The duration of the test. The value `0` means no time limit.
--report-interval	The interval at which runtime logs are reported, in seconds.
--events	The maximum number of requests. The `--time` option is not necessary if you specify this parameter.
--rand-type	A random function used for data access. Valid values: `special`, `uniform`, `gaussian`, and `pareto`. The default value is `special`, which was `uniform` previously.
--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	In the Lua directory of Sysbench, there are test cases for different scenarios like `insert` and `point_select`.

Note

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

FAQ

Q: What do I do if I encounter an error while installing Sysbench? Here is the error message:
```
automake 1.10.x (aclocal) wasn't found, exiting
```
A: Run the following command to install Automake:
```
yum install automake.noarch
```
Q: What do I do if I encounter an error while installing Sysbench? Here is the error message:
```
libtoolize 1.4+ wasn't found, exiting
```
A: Run the following commands to install Libtool:
```
yum install libtool
```
Q: What do I do if I encounter an error while installing Sysbench? Here is the error message:
```
drv_mysql.c:35:19: fatal error: mysql.h: No such file or directory
```
A: Run the following command to install the lib library for MySQL on the operating system.:
```
yum install mysql-community-devel.x86_64
```
Q: What do I do if I encounter an error while installing Sysbench? Here is the error message:
```
cannot find MySQL client libraries in /usr/lib/mysql/
```
A: Run the following command to search for the lib directory for MySQL. It may be located in /usr/lib64/mysql:
```
find /usr -name mysql
```
Q: Why the performance is poor even though I have configured Sysbench and OceanBase Database properly?

A:
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 cause the Sysbench performance to deteriorate.
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 perform the Sysbench test through ODP.
Q: Why is the CPU utilization still low under high concurrency stress?

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