Use Sysbench to test OLTP performance for MySQL - PolarDB

Use Sysbench to run online transactional processing (OLTP) performance tests against a PolarDB for MySQL cluster. This page covers setup, installation, and test commands for read-only, read/write, and write-only scenarios.

Test tool

Sysbench is an open source, cross-platform performance test tool. It is primarily used for database benchmarks (such as MySQL) and system performance tests including CPU, memory, I/O, and threads. Sysbench supports multi-threaded testing and uses Lua scripts to flexibly control test logic, making it suitable for database performance evaluation and stress testing.

Test environment

Client setup

Category	Specification
Instance type	ECS.c5.4xlarge (16 CPU cores, 32 GB memory)
Operating system	CentOS 7.0 64-bit
Number of instances	1 per 8-core compute node; scale proportionally (for example, 2 instances for 16-core nodes)
Network requirement	Same region, zone, and Virtual Private Cloud (VPC) as the PolarDB cluster
PING latency	Must be less than 0.2 ms

PolarDB cluster configuration

Category	Specification
Cluster type	Two-node cluster (one primary node, one read-only node)
Endpoints	Primary endpoint, cluster endpoint, and custom endpoints — combined as needed to maximize resource utilization

Parameter settings

For high-concurrency tests, configure the following parameters before running:

`max_prepared_stmt_count` — Increase this value to prevent the can't create more than max_prepared_stmt_count statements error at high thread counts.
`--db-ps-mode=disable` — Add this flag to the test command in large-scale, high-concurrency scenarios when max_prepared_stmt_count alone is insufficient. This disables the Prepared Statement feature.
Extra-large clusters (88 cores and above) — Additional parameter tuning is required to fully use cluster resources. Tune based on your instance type and actual test results.

For parameter configuration steps, see Set cluster parameters and node parameters.

Test notes

All commands on this page require root permissions.
Tests use tables=250 and table-size=25000. Increment threads across runs. Each product page shows peak results only.
This page uses a PolarDB for MySQL Cluster Edition cluster as an example.

Test scenarios

This test measures the OLTP performance for read-only, write-only, and read/write scenarios on a two-node cluster (one primary and one read-only node) across different Dedicated instance types.

Metrics

Metric	Description
TPS (Transactions Per Second)	Number of transactions executed per second, based on successful COMMITs
QPS (Queries Per Second)	Number of SQL statements (INSERT, SELECT, UPDATE, DELETE) executed per second

Install Sysbench

Clone and build Sysbench on the ECS instance:

git clone https://github.com/akopytov/sysbench.git
cd sysbench
./autogen.sh
./configure
make -j
make install

Configure the Sysbench client to distribute network packet processing across all CPU cores, reducing context switching:

The value ffff enables all 16 CPU cores for packet processing. Adjust based on your core count: ff for 8 cores, ffff for 16 cores, and so on.

sudo sh -c 'for x in /sys/class/net/eth0/queues/rx-*; do echo ffff>$x/rps_cpus; done'
sudo sh -c "echo 32768 > /proc/sys/net/core/rps_sock_flow_entries"
sudo sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-0/rps_flow_cnt"
sudo sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-1/rps_flow_cnt"

Run the tests

Before running the tests, make sure you have:

An ECS instance in the same region, zone, and VPC as your PolarDB cluster
A PolarDB for MySQL two-node cluster with endpoints configured
Sysbench installed on the ECS instance

Step 1: Get the cluster endpoint

Get the endpoint and port of your PolarDB for MySQL cluster. See Manage connection addresses.

Step 2: Configure the cluster endpoint

Set Primary Node Accepts Read Requests to Yes for the cluster endpoint. See Configure a database proxy.

Step 3: Create the test database

Run the following command on the ECS instance to create a database named testdb:

mysql -h <host> -P <port> -u <username> -p <password> -e 'create database testdb'

Parameter	Description
`-h <host>`	Cluster endpoint
`-P <port>`	Port number
`-u <username>`	Database username
`-p <password>`	Password for the username

Step 4: Run scenario tests

Each scenario follows the same three-phase pattern: prepare → run → cleanup. All examples use the same base parameters:

Parameter	Value	Description
`--db-driver`	`mysql`	Database driver
`--mysql-db`	`testdb`	Target database
`--table_size`	`25000`	Rows per table
`--tables`	`250`	Number of tables
`--events`	`0`	No event limit (run until time expires)
`--time`	`600`	Test duration in seconds
`--rand-type`	`uniform`	Data distribution type
`--threads`	Your value	Concurrent threads; increment across runs to find peak performance

The following output fields are displayed in the test results:

Parameter category	Displayed content	Description
`tables`	Number of data tables	The total number of data tables in the test
`table_size`	Number of rows in data table	The number of records in each table
Data volume size	—	The data volume of the table, in units such as MB or GB
`threads`	Number of concurrent threads	The currently configured number of threads
Thread status	—	Lets you view the running status of threads in real time

Read-only test (oltp_read_only)

This test uses --range-selects=0, which limits the workload to point selects only — equivalent to the oltp_point_selects scenario.

Prepare data:

sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=testdb --range-selects=0 --table_size=25000 --tables=250 --events=0 --time=600 --rand-type=uniform --threads=<threads> oltp_read_only prepare

Run the test:

sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=testdb --range-selects=0 --table_size=25000 --tables=250 --events=0 --time=600 --rand-type=uniform --threads=<threads> oltp_read_only run

Clean up:

sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=testdb --range-selects=0 --table_size=25000 --tables=250 --events=0 --time=600 --rand-type=uniform --threads=<threads> oltp_read_only cleanup

Mixed read/write test (oltp_read_write)

This test simulates a typical mixed read/write business workload.

Core parameters such as tables, table-size, and threads are displayed on a one-to-one basis on the test data page.

Prepare data:

sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=testdb --table_size=25000 --tables=250 --events=0 --time=600 --rand-type=uniform --threads=<threads> oltp_read_write prepare

Run the test:

sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=testdb --table_size=25000 --tables=250 --events=0 --time=600 --rand-type=uniform --threads=<threads> oltp_read_write run

Clean up:

sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=testdb --table_size=25000 --tables=250 --events=0 --time=600 --rand-type=uniform --threads=<threads> oltp_read_write cleanup

Write-only test (oltp_write_only)

Core parameters such as tables, table-size, and threads are displayed on a one-to-one basis on the test data page.

Prepare data:

sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=testdb --table_size=25000 --tables=250 --events=0 --time=600 --rand-type=uniform --threads=<threads> oltp_write_only prepare

Run the test:

sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=testdb --table_size=25000 --tables=250 --events=0 --time=600 --rand-type=uniform --threads=<threads> oltp_write_only run

Clean up:

sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=testdb --table_size=25000 --tables=250 --events=0 --time=600 --rand-type=uniform --threads=<threads> oltp_write_only cleanup

PolarDB:Performance test method (OLTP)

Test tool

Test environment

Client setup

PolarDB cluster configuration

Parameter settings

Test notes

Test scenarios

Metrics

Install Sysbench

Run the tests

Step 1: Get the cluster endpoint

Step 2: Configure the cluster endpoint

Step 3: Create the test database

Step 4: Run scenario tests

Read-only test (oltp_read_only)

Mixed read/write test (oltp_read_write)

Write-only test (oltp_write_only)

What to do next