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

Run the Sysbench benchmark on OceanBase Database

Last Updated：2024-03-05 01:54:26 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 with one click.
Use the official Sysbench tool to manually run the Sysbench benchmark step by step.

Note

OceanBase Database is extensively optimized in versions later than V4.0.0 to improve the user experience, ease of use, and database performance. The test method described in this topic tunes only the basic parameters for performance improvement.

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

Prepare the environment

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 '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 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 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. We recommend that you modify the configuration file separately to maximize the resource utilization.

Test plan

The test requires five servers. Deploy Sysbench and OBD on the same server, and deploy ODP on a separate server. When you use OBD to deploy an OceanBase cluster, three servers are required, which are evenly distributed in three zones.
Note

In the Sysbench test, you can flexibly configure the server 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 primary_zone to RANDOM for the tenant, which indicates that the leader of the new table partitions is randomly assigned to one of the three servers.
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 test

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 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 on 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: # root user password parameter. After the obd cluster edit-config command is executed, you must run the obd cluster reload command.
After the obd test sysbench command is executed, the system lists the test steps and output in detail. A larger data volume leads to a longer test time.
The obd test sysbench command automatically completes all steps without any additional operations, including test data generation and OceanBase Database parameter optimization, loading, and testing. If an error occurs during the process, you can retry by referring to the obd test sysbench command. For example, you can skip data generation and directly load and test the parameters.

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

The following information indicates a successful installation of Sysbench.

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

Environment optimization

Before you start the Sysbench test, 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 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 that runs OceanBase Database. We recommend that you use the IP address of the ODP server.
--mysql-port	The port number.
--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 started threads.
--time	The running duration. The value `0` means no time limit.
--report-interval	The interval in seconds at which runtime logs are reported. The value `0` indicates that runtime logs are not reported.
--events	The maximum number of requests. The `--time` option is not necessary if you specify this parameter.
--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 results, see Sysbench benchmark report of OceanBase Database.

FAQ

Q: What do I do when an error occurred when I was installing Sysbench? Here is the error message:
```
automake 1.10.x (aclocal) wasn't found, exiting
```
A: Automake is not installed. Run the following command to install Automake:
```
yum install automake.noarch
```
Q: What do I do when an error occurred when I was installing Sysbench? Here is the error message:
```
libtoolize 1.4+ wasn't found, exiting
```
A: Libtool is not installed. Run the following commands to install Libtool:
```
yum install libtool
```
Q: What do I do when an error occurred when I was installing Sysbench? Here is the error message:
```
drv_mysql.c:35:19: fatal error: mysql.h: No such file or directory
```
A: The lib library for MySQL is not installed on the operating system. Run the following command to install it:
```
yum install mysql-community-devel.x86_64
```
Q: What do I do when an error occurred when I was 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: I have configured Sysbench and OceanBase Database properly. Why is the benchmark result still lame?

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 directly connect Sysbench to a port of 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.