In PolarDB-X 1.0, Sysbench is used as the stress test tool during performance tests. This topic describes how to use Sysbench.

How to install Sysbench 0.5

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

git clone https://github.com/akopytov/sysbench.git
cd sysbench
git checkout 0.5

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

./autogen.sh
./configure
make -j
make install

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 PolarDB-X 1.0 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_insert.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 PolarDB-X 1.0 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: 

CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  KEY `xid` (`id`),
  KEY `k_1` (`k`)
) 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 PolarDB-X 1.0 instance.

Sample commands

The following shows sample commands for your reference.

Prepare data:

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:

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:

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