Use Sysbench for PolarDB-X 1.0 performance testing - PolarDB

This topic describes how to use Sysbench to test PolarDB-X 1.0 performance.

Installation

In this example, Sysbench 0.5 is used for performance testing. Installation code:

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 code is for installing Sysbench on an ECS instance for performance testing. To install Sysbench on a different operating system, see Sysbench's official documentation.

After the installation is complete, all built-in benchmarking scripts used in performance testing for PolarDB-X 1.0 are stored in the /usr/local/share/sysbench directory. The scripts can also be found in the source code directory sysbench/sysbench/tests/db.

Usage

Common benchmarking models

Sysbench provides the following commonly used benchmarking models defined by using scripts.

Benchmarking model	Description
bulk_insert.lua	Bulk data insertion.
insert.lua	Single-value data insertion.
delete.lua	Data deletion.
oltp.lua	Mixed read-write testing with a read-to-write ratio of 14:4.
select.lua	Simple primary key queries.

Table schema

To test PolarDB-X 1.0, the Sysbench test table is modified to adopt a sharding structure.

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

The number of database shards and table partitions can be adjusted as needed. During the data preparation phase of the Sysbench test, the table is automatically generated based on the provided schema.

Test commands and parameters

Sysbench-based benchmarking involves the following steps:

prepare: prepares data.
run: runs the benchmarking model.
cleanup: cleans up test data.

In most cases, you only need to prepare the data once. Various models can be tested based on this prepared data.

Commonly used parameters

--oltp-tables-count=1: the number of tables.
--oltp-table-size=10000000: the number of rows generated per table.
--oltp-read-only=off: specifies whether to generate read-only SQL queries. Default value: off. If you set it to on, the OLTP model does not generate UPDATE, DELETE, or INSERT statements.
--oltp-skip-trx=[on|off]: specifies whether to skip BEGIN and COMMIT statements. Default value: off.
--rand-init=on: specifies whether to randomly initialize data. If you set it to off, the initial data rows have identical contents, except for the primary key.
--num-threads=12: the number of concurrent threads (or simulated client connections).
--report-interval=10: the reporting interval (in seconds) of performance metrics.
--max-requests=0: the total number of requests generated during the test. ·If you specify the test duration by using the max-time parameter, you can set this parameter to 0.
--max-time=120: the running duration (in seconds) 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 as a non-primary key to prevent primary key conflicts.
--oltp_range_size=5: specifies the range size for consecutive values in queries.
--mysql_table_options='dbpartition by hash(id) tbpartition by hash(id) tbpartitions 2': the sharding strategy for table creation in PolarDB-X 1.0

Sample commands

You can perform the corresponding operations by using the following commands.

Note

Before executing the commands, manually create a test database, and modify the following parameters based on your actual information:

<user_name>: the account name.
<password>: the account password.
<host>: the private or public endpoint.
<port>: the port number corresponding to the private or public endpoint.
<db_name>: the database name.

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

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