All Products
Search
Document Center

Sysbench user guide

Last Updated: Aug 17, 2020

In Distributed Relational Database Service (DRDS), Sysbench is used as the stress test tool during performance tests. This topic describes how to use Sysbench.

Installation

Sysbench 0.5 is used in performance tests. You can install it as follows:

  1. git clone https://github.com/akopytov/sysbench.git
  2. git checkout 0.5
  3. yum -y install make automake libtool pkgconfig libaio-devel
  4. yum -y install mariadb-devel
  5. ./autogen.sh
  6. ./configure
  7. make -j
  8. make install

Note: The preceding shows how to install Sysbench 0.5 on an Elastic Compute Service (ECS) instance that serves as the stress test machine. For information about how to install Sysbench 0.5 on other operating systems, see Official documentation of Sysbench.

After Sysbench 0.5 is installed, all built-in stress test scripts are located in the /usr/local/share/sysbench directory. A script in this directory will be used to perform performance tests on the DRDS instance. In addition, you can find the corresponding stress test scripts in the source directory sysbench/sysbench/tests/db.

How it works

Common test models

Sysbench defines several common stress test models by using scripts. The following briefly describes some common models:

Stress test model Description
bulk_inert.lua Batch inserts data.
insert.lua Inserts data.
delete.lua Deletes data.
oltp.lua Performs a mixed read/write performance test with a read/write ratio of 14:4.
select.lua Queries data based on a simple primary key.

Table structure

When you test the performance of a DRDS instance, slightly modify the test table of Sysbench into the structure with database shards and table shards. For example, you can execute the following statement to create a test table:

  1. CREATE TABLE `sbtest1` (
  2. `id` int(10) unsigned NOT NULL,
  3. `k` int(10) unsigned NOT NULL DEFAULT '0',
  4. `c` char(120) NOT NULL DEFAULT '',
  5. `pad` char(60) NOT NULL DEFAULT '',
  6. KEY `xid` (`id`),
  7. KEY `k_1` (`k`)
  8. ) dbpartition by hash(`id`) tbpartition by hash(`id`) tbpartitions 4

In the preceding statement for table creation, you can adjust the number of database shards and table shards as needed. The table will be automatically generated during data preparation.

Test commands and parameters

Generally, to use Sysbench to perform a stress test, perform the following steps:

  • prepare: Prepare data.
  • run: Run a test model.
  • cleanup: Clean up test data.

In most cases, you only need to prepare data once and then run various test models based on the prepared data.

Common parameters

The following describes common parameters used in Sysbench:

  • --oltp-tables-count=1: the number of tables.
  • --oltp-table-size=10000000: the number of rows of records to be generated for each table.
  • --oltp-read-only=off: specifies whether to generate read-only SQL statements. The default value is off. If this parameter is set to on, the OLTP model does not generate SQL UPDATE, DELETE, or INSERT statements.
  • --oltp-skip-trx=[on|off]: specifies whether to skip BEGIN and COMMIT statements. The default value is off.
  • --rand-init=on: specifies whether to randomly initialize the data. If the data is not randomized during initialization, all rows of the initialized data are the same, except the primary key.
  • --num-threads=12: the number of concurrent threads. This parameter can be considered as the number of concurrent connections to the simulated client.
  • --report-interval=10: the interval at which performance data is to be generated. In this example, the interval is 10 seconds.
  • --max-requests=0: the total number of requests generated during a stress test. Set the value to 0 if you need to calculate the requests based on the max-time parameter.
  • --max-time=120: the duration of the test.
  • --oltp_auto_inc=off: specifies whether the id column is an auto-increment column.
  • --oltp_secondary=on: specifies whether to set the id column to a non-primary key to prevent primary key conflicts.
  • --oltp_range_size=5: specifies the number of consecutive values specified for shards involved in queries. In this example, the value is 5, that is, five consecutive values are specified, and therefore five shards will be involved in queries.
  • --mysql_table_options='dbpartition by hash(id) tbpartition by hash(id) tbpartitions 2': the table sharding method for the table that you want to create in the DRDS instance.

Sample commands

The following shows sample commands for your reference.

Prepare data:

  1. sysbench --test='/usr/local/share/sysbench/oltp.lua' --oltp_tables_count=1 --report-interval=10 --oltp-table-size=10000000 --mysql-user=*** --mysql-password=*** --mysql-table-engine=innodb --rand-init=on --mysql-host=**** --mysql-port=*** --mysql-db=*** --max-time=300 --max-requests=0 --oltp_skip_trx=on --oltp_auto_inc=off --oltp_secondary=on --oltp_range_size=5 --mysql_table_options='dbpartition by hash(`id`) tbpartition by hash(`id`) tbpartitions 2' --num-threads=200 prepare

Run a stress test:

  1. sysbench --test='/usr/local/share/sysbench/oltp.lua' --oltp_tables_count=1 --report-interval=10 --oltp-table-size=10000000 --mysql-user=*** --mysql-password=*** --mysql-table-engine=innodb --rand-init=on --mysql-host=**** --mysql-port=*** --mysql-db=*** --max-time=300 --max-requests=0 --oltp_skip_trx=on --oltp_auto_inc=off --oltp_secondary --oltp_range_size=5 --mysql_table_options='dbpartition by hash(`id`) tbpartition by hash(`id`) tbpartitions 2' --num-threads=200 run

Clean up the environment:

  1. sysbench --test='/usr/local/share/sysbench/oltp.lua' --oltp_tables_count=1 --report-interval=10 --oltp-table-size=10000000 --mysql-user=*** --mysql-password=*** --mysql-table-engine=innodb --rand-init=on --mysql-host=**** --mysql-port=*** --mysql-db=*** --max-time=300 --max-requests=0 --oltp_skip_trx=on --oltp_auto_inc=off --oltp_secondary --oltp_range_size=5 --mysql_table_options='dbpartition by hash(`id`) tbpartition by hash(`id`) tbpartitions 2' --num-threads=200 cleanup