Benchmark PolarDB-X 1.0 Distributed OLTP with Sysbench - PolarDB

Use Sysbench to run performance benchmarks against PolarDB-X 1.0. This guide covers installation, table schema configuration, and the full prepare–run–cleanup workflow.

Prerequisites

Before you begin, make sure you have:

An Elastic Compute Service (ECS) instance running a yum-based Linux distribution (CentOS or RHEL)
A PolarDB-X 1.0 instance with a test database already created
The private or public endpoint, port, account name, and password for your PolarDB-X 1.0 instance

Install Sysbench

The following steps install Sysbench 0.5 on an ECS instance.

Clone the repository and check out the 0.5 branch:

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

Install build dependencies:

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

Build and install:

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

After installation, the built-in benchmarking scripts are in /usr/local/share/sysbench. You can also find them in the source directory sysbench/sysbench/tests/db.

For installation on other operating systems, see the Sysbench official documentation.

Benchmarking models

Sysbench includes the following built-in test scripts for PolarDB-X 1.0 benchmarking:

Script	Workload
`bulk_insert.lua`	Bulk data insertion
`insert.lua`	Single-value data insertion
`delete.lua`	Data deletion
`oltp.lua`	Mixed read-write (read-to-write ratio: 14:4)
`select.lua`	Simple primary key queries

Configure the table schema

PolarDB-X 1.0 requires a sharded table schema. The following DDL uses dbpartition and tbpartition to distribute data across shards:

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

Adjust the number of database shards and table partitions to match your cluster configuration. Sysbench automatically creates the table during the prepare phase based on this schema.

Run the benchmark

Benchmarking consists of three steps: prepare (load test data), run (execute the workload), and cleanup (remove test data). Run prepare once; you can then test multiple models against the same dataset.

Step 1: Create a config file

Store your connection details and test parameters in a config file to avoid repeating them in every command. Create a file named config with the following content, replacing the placeholders with your actual values:

mysql-host=<host>
mysql-port=<port>
mysql-user=<user_name>
mysql-password=<password>
mysql-db=<db_name>
mysql-table-engine=innodb
test=/usr/local/share/sysbench/oltp.lua
oltp-tables-count=1
oltp-table-size=10000000
report-interval=10
max-time=300
max-requests=0
num-threads=200
rand-init=on
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

Placeholder	Description
`<host>`	Private or public endpoint
`<port>`	Port number
`<user_name>`	Account name
`<password>`	Account password
`<db_name>`	Database name (must be created in advance)

Step 2: Prepare data

Load the test dataset into PolarDB-X 1.0. Run prepare only once; all subsequent test runs use the same data.

sysbench --config-file=config prepare

Step 3: Run the test

sysbench --config-file=config run

Step 4: Clean up

Remove test data when benchmarking is complete.

sysbench --config-file=config cleanup

Parameters

The following parameters control test behavior. Adjust them in the config file before running the benchmark.

Parameter	Description	Default	Recommended value
`oltp-tables-count`	Number of test tables	—	`1`
`oltp-table-size`	Rows per table	—	`10000000`
`num-threads`	Concurrent threads (simulated client connections)	—	`200`
`max-time`	Test duration (seconds); overrides `max-requests`	—	`300`
`max-requests`	Total requests to generate; set to `0` when using `max-time`	—	`0`
`report-interval`	Metrics reporting interval (seconds)	—	`10`
`rand-init`	Randomly initialize row data; if `off`, all rows have identical content except the primary key	`on`	`on` — enables realistic data distribution
`oltp-read-only`	Generate read-only queries only; if `on`, no `UPDATE`, `DELETE`, or `INSERT` statements are issued	`off`	`off` for mixed workloads; `on` for read-only benchmarks
`oltp_skip_trx`	Skip `BEGIN` and `COMMIT` statements	`off`	`on` — reduces transaction overhead during data loading
`oltp_auto_inc`	Make the `id` column auto-increment	—	`off` — required for PolarDB-X 1.0 sharding
`oltp_secondary`	Set the `id` column as a non-primary key to prevent primary key conflicts	—	`on` — required for PolarDB-X 1.0 sharding
`oltp_range_size`	Range size for consecutive-value queries	—	`5`
`mysql_table_options`	Sharding strategy for table creation in PolarDB-X 1.0	—	`dbpartition by hash(id) tbpartition by hash(id) tbpartitions 2`