OLTP performance test: Sysbench benchmark

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

What is Sysbench

Sysbench is a multi-threaded benchmarking tool based on LuaJIT. It can perform performance tests on CPU, memory, threads, I/O, and databases. It is commonly used to evaluate database load under various system parameters. You can customize Lua scripts to test different business types without modifying the source code.

Prepare the environment

• Prepare a pressure test ECS

  Prepare an ECS. We recommend that you select an ECS with the same specifications as the OBServer node to avoid the pressure test ECS becoming a bottleneck. Note that the pressure test ECS must be in the same VPC network as the OceanBase tenant, and the OceanBase cluster must allow access from the pressure test ECS.

• Create an OceanBase instance

  1. Create an OceanBase instance. For more information, see Create an instance.
  2. Create a tenant and set the tenant specifications based on the instance resources. For more information, see Create a tenant.
  3. Create a database. In this example, a database named sysbench is created. For more information, see Create a database.

• Install the Sysbench tool: follow the steps below to install Sysbench.

  1. Download Sysbench.

     For more information, see Sysbench download address .

     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	Sysbench supports MySQL by default.

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

     [xxxx@localhost sysbench-1.0.20] $./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 environment

Test scenario:

• To better experience the characteristics of OceanBase Database, we recommend that you enable the database proxy. For more information, see Manage a database proxy.

• By default, the database proxy and OBServer are deployed on the same ECS. Enabling the database proxy can positively impact performance.

Execute the test

1. Initialize the database

   sysbench oltp_read_write.lua --mysql-host=$host --mysql-port=$port --mysql-db=$database_name --mysql-user=$user --mysql-password=****** --table_size=1000000 --tables=30 --threads=150 --report-interval=10 --time=60 cleanup 
   

2. Create a table

   sysbench oltp_read_write.lua --mysql-host=$host --mysql-port=$port --mysql-db=$database_name --mysql-user=$user --mysql-password=****** --table_size=1000000 --tables=30 --threads=150 --report-interval=10 --time=60 prepare
   

3. Run the test

   sysbench oltp_read_write.lua --mysql-host=$host --mysql-port=$port --mysql-db=$database_name --mysql-user=$user@ --mysql-password=****** --table_size=1000000 --tables=30 --threads=150 --report-interval=10 --time=60 --db-ps-mode=disable run
   

4. Parameter description

   The following is a sample Sysbench configuration file:

   --mysql-host       The IP address of the OBServer node (if an OBProxy is used, use the IP address of the OBProxy)
   --mysql-port       The port number
   --mysql-db         The database to connect to
   --mysql-user       The username
   --mysql-password   The password
   --table_size       The number of data rows to initialize in each table
   --tables           The number of tables to initialize
   --threads          The number of threads to start
   --time             Set to 0 to indicate no time limit
   --report-interval  The interval for logging during the test, in seconds
   --events           The maximum number of requests. If this parameter is specified, the --time option is not required.
   --rand-type        The random generation function to use when accessing data. The options are special, uniform, gaussian, and pareto. The default is special, and the default in earlier versions was uniform.
   --skip_trx=on      Specifies whether to enable transactions in read-only tests. The default is to enable transactions.
   --percentile=N     The percentile of response time to print. The default is 95.
   oltp_write_only    The sysbench lua directory contains test cases for different scenarios, such as insert and point_select.