Use Sysbench to test the performance of seekdb

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

What is Sysbench

Sysbench is a multi-threaded benchmarking tool based on LuaJIT. It allows you to customize the test logic by writing scripts. It can test the performance of CPU, memory, threads, disk I/O, and databases. It is commonly used to evaluate the impact of different system parameters on database load. This tool allows you to simulate diverse business scenarios by customizing Lua scripts without modifying the source code. The types of tests supported by Sysbench include CPU performance, disk I/O performance, thread scheduling performance, memory allocation and access speed, POSIX thread performance, and database performance. This topic focuses on testing database performance.

Prepare the environment

Before you start the test, make sure that the test environment meets the following requirements:

• Sysbench: We recommend that you use version 1.1 or later.

• seekdb: Use the server mode of seekdb. For more information about how to deploy seekdb, see Deploy seekdb by using yum install.

• The log disk, clog disk, and data disk of the deployed seekdb are three separate disks with performance level PL1. When you start seekdb, you need to set the following parameters in the /etc/oceanbase/seekdb.cnf file:

  port=2881
  base-dir=/data/1/seekdb
  data-dir=/data/2/seekdb
  redo-dir=/data/3/seekdb

  tip

  If the test is performed on a seekdb instance with a 1C2G specification, you need to set the memory_limit=2G parameter.

Test plan

• In this test, you need to use two servers. Deploy Sysbench on one server and seekdb on another server. The seekdb instance is of the 4C8G or 1C2G specification. The log disk, clog disk, and data disk of the seekdb instance are three separate disks with performance level PL1.

• Import 30 tables into the seekdb instance. Each table contains 10,000 rows of data.

• Start the Sysbench client and perform point_select, read_write, read_only, insert, update, and write_only tests.

• Set the --time parameter to 10s for each test. The value of the --threads parameter can be 10/50/100/200/400, and so on.

Run a Sysbench test

Step 1: Optimize the environment

Before you start the test, optimize the seekdb instance. Log in to the seekdb instance and execute the following statements to configure the parameters.

alter system SET syslog_level='PERF';
alter system set _lcl_op_interval = '0ms';
call DBMS_MONITOR.OB_TENANT_TRACE_DISABLE(TENANT_NAME => 'sys');
alter system set default_auto_increment_mode = 'noorder'; 
alter system set _enable_adaptive_compaction = false;
alter system set _enable_defensive_check = false;

Parameter description:

• syslog_level: specifies the log printing level.
• _lcl_op_interval: specifies the interval between message pushes between nodes in the LCL (Lock Chain Length) deadlock detection algorithm. The value 0ms indicates that the LCL deadlock detection feature is disabled.
• default_auto_increment_mode: specifies the default auto-increment mode for auto-increment columns.
• _enable_adaptive_compaction: specifies whether to enable adaptive major compaction. The value false indicates that adaptive major compaction is disabled.
• _enable_defensive_check: specifies whether to enable the 4377 check.
• DBMS_MONITOR.OB_TENANT_TRACE_DISABLE: specifies whether to disable the full-link tracing feature.

Step 2: Install Sysbench

To install Sysbench, perform the following steps:

1. Install the required dependencies.

   sudo yum -y install make automake libtool pkgconfig libaio-devel mariadb-devel openssl-devel

2. Download Sysbench.

   For more information, see Sysbench download address.

3. Decompress Sysbench.

   unzip sysbench-master.zip

4. Compile Sysbench.

   Go to the directory where Sysbench is decompressed and run the following command to compile Sysbench:

   [admin@localhost ~] $ cd sysbench-master
   [admin@localhost sysbench-master] $ ./autogen.sh
   [admin@localhost sysbench-master] $ ./configure --prefix=/usr/local/sysbench --with-mysql --with-mysql-includes=/usr/include/mysql --with-mysql-libs=/usr/lib64/mysql
   [admin@localhost sysbench-master] $ sudo make && sudo make install
   [admin@localhost sysbench-master] $ sudo ln -s /usr/local/sysbench/bin/* /usr/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	specifies whether to enable MySQL support. By default, MySQL support is enabled.

5. Run the following command to verify whether Sysbench is installed:

   [admin@localhost sysbench-master] $ sysbench --version

   If the following information is returned, Sysbench is installed.

   sysbench 1.1.0

Step 4: Run the Sysbench test

To run the Sysbench test, perform the following steps:

1. Initialize the test data.

   sysbench --mysql-host=xxx.xxx.xxx.xxx --mysql-port=2881 --mysql-user=root --mysql-db=test --report-interval=1 --db-ps-mode=disable --percentile=99 --tables=30 --mysql-ignore-errors=1062 --table_size=10000 --auto_inc=on --time=20 --threads=10 oltp_read_write.lua prepare

2. Warm up.

   sysbench --mysql-host=xxx.xxx.xxx.xxx --mysql-port=2881 --mysql-user=root --mysql-db=test --report-interval=1 --threads=400 --db-ps-mode=disable --percentile=99 --tables=30 --mysql-ignore-errors=1062 --table_size=10000 --auto_inc=on --time=10 oltp_point_select.lua run
   
   sysbench --mysql-host=xxx.xxx.xxx.xxx --mysql-port=2881 --mysql-user=root --mysql-db=test --report-interval=1 --threads=400 --db-ps-mode=disable --percentile=99 --tables=30 --mysql-ignore-errors=1062 --table_size=10000 --auto_inc=on --time=10 oltp_read_only.lua run

3. Run the performance test.

   #point_select 
   sysbench --mysql-host=xxx.xxx.xxx.xxx --mysql-port=2881 --mysql-user=root --mysql-db=test --report-interval=1 --threads=400 --db-ps-mode=disable --percentile=99 --tables=30 --mysql-ignore-errors=1062 --table_size=10000 --auto_inc=on --warmup-time=2 --time=10 oltp_point_select.lua run
   #read_only
   sysbench --mysql-host=xxx.xxx.xxx.xxx --mysql-port=2881 --mysql-user=root --mysql-db=test --report-interval=1 --threads=400 --db-ps-mode=disable --percentile=99 --tables=30 --mysql-ignore-errors=1062 --table_size=10000 --auto_inc=on --warmup-time=2 --time=10 oltp_read_only.lua run
   #read_write
   sysbench --mysql-host=xxx.xxx.xxx.xxx --mysql-port=2881 --mysql-user=root --mysql-db=test --report-interval=1 --threads=400 --db-ps-mode=disable --percentile=99 --tables=30 --mysql-ignore-errors=1062 --table_size=10000 --auto_inc=on --warmup-time=2 --time=10 --rand-seed=24433 --rand-type=uniform oltp_read_write.lua run
   #insert
   sysbench --mysql-host=xxx.xxx.xxx.xxx --mysql-port=2881 --mysql-user=root --mysql-db=test --report-interval=1 --threads=400 --db-ps-mode=disable --percentile=99 --tables=30 --mysql-ignore-errors=1062 --table_size=10000 --auto_inc=on --warmup-time=2 --time=10 --rand-type=uniform --rand-seed=12104 oltp_insert.lua run
   #update_non_index
   sysbench --mysql-host=xxx.xxx.xxx.xxx --mysql-port=2881 --mysql-user=root --mysql-db=test --report-interval=1 --threads=400 --db-ps-mode=disable --percentile=99 --tables=30 --mysql-ignore-errors=1062 --table_size=10000 --auto_inc=on --warmup-time=2 --time=10 --rand-type=uniform --rand-seed=10515 oltp_update_non_index.lua run
   #write_only
   sysbench --mysql-host=xxx.xxx.xxx.xxx --mysql-port=2881 --mysql-user=root --mysql-db=test --report-interval=1 --threads=400 --db-ps-mode=disable --percentile=99 --tables=30 --mysql-ignore-errors=1062 --table_size=10000 --auto_inc=on --warmup-time=2 --time=10 --rand-seed=11972 --rand-type=uniform oltp_write_only.lua run

Parameter description

Parameter	Description
--mysql-host	the IP address of the server where seekdb is deployed.
--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 you set this parameter to 0, no time limit is imposed.
--report-interval	the interval at which logs are generated during the test, in seconds.
--events	the maximum number of requests. If you specify this parameter, you do not need to specify the --time parameter.
--rand-type	the random number generation function used to access data. 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. By default, transactions are enabled.
--percentile=N	specifies the percentile of response time to be printed. The default value is 95.
oltp_write_only	The Sysbench Lua directory contains test cases for different scenarios, such as insert and point_select.

References

For more information about the test results, see seekdb Sysbench performance test report.