DocsICP FilingConsole
官方文档
首页

Performance test method (OLTP)

更新时间：
复制 MD 格式
产品详情
我的收藏

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

  1. 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

  2. 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.

  1. 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

  2. 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

  3. 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.

  1. 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

  2. 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

  3. 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.

  1. 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

  2. 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

  3. 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

What to do next

Previous：OLTP performanceNext：Performance test results of PolarDB for MySQL 8.0.1 Cluster Edition