PolarDB:Sysbench tests

Step 1: Create an ECS instance

Create an Elastic Compute Service (ECS) instance to host the stress testing tool. To avoid performance bottlenecks when testing high-spec PolarDB-X instances, use an instance with 32 vCPUs and 128 GB of memory.

Deploy the ECS instance in a VPC. Record the VPC name and ID — all database instances created in subsequent steps must reside in the same VPC.

Step 2: Create a PolarDB-X instance

1. Create a PolarDB-X instance. Select the MySQL 5.7 or MySQL 8.0 engine based on your requirements. For instructions, see Create a PolarDB-X instance.

   The PolarDB-X instance and the ECS instance must reside in the same VPC.

2. Create a database named sbtest. For instructions, see Create a database.

   CREATE DATABASE sbtest;

Step 3: Configure instance parameters

Modify the compute node parameters to achieve optimal performance during stress testing.

1. Set the following parameters. For instructions, see Parameter settings.

   Parameter	Value
   ENABLE_COROUTINE	true
   XPROTO_MAX_DN_CONCURRENT	4000
   XPROTO_MAX_DN_WAIT_CONNECTION	4000

2. Connect to the PolarDB-X instance using a command-line client, then run the following SQL statements in the same session to disable logging and CPU statistical sampling:

   set global RECORD_SQL=false;
   set global MPP_METRIC_LEVEL=0;
   set global ENABLE_CPU_PROFILE=false;
   set global ENABLE_TRANS_LOG=false;

Step 4: Install Sysbench and prepare data

1. Download the sysbench.tar.gz package and decompress it:

   tar xzvf sysbench.tar.gz
   cd sysbench/

2. Install and compile dependencies:

   You can also download Sysbench directly from GitHub.

   yum -y install make automake libtool pkgconfig libaio-devel mysql-devel
   ./autogen.sh
   ./configure
   make -j
   make install

   Verify the installation by running sysbench --version. A successful installation returns sysbench 1.1.0.

3. Create the configuration file sysb.conf with your PolarDB-X connection details:

   Parameter	Description
   percentile	Percentile used to sample response time. Set to 95 for 95th-percentile latency.
   histogram	Whether to display a response time distribution histogram.
   report-interval	Interval at which real-time results are displayed, in seconds.
   time	Duration of the stress test, in seconds.
   rand-type	Distribution type for random number generation.

   mysql-host='<HOST>'
   mysql-port='<PORT>'
   mysql-user='<USER>'
   mysql-password='<PASSWORD>'
   mysql-db='sbtest'
   db-driver='mysql'
   percentile='95'
   histogram='on'
   report-interval='1'
   time='60'
   rand-type='uniform'

4. Import the test data:

   Parameter	Description
   config-file	Path to the common configuration file.
   create-table-options	Table creation mode. Uses PolarDB-X dbpartition by hash(id) syntax.
   tables	Number of tables to create.
   table-size	Number of rows per table.
   threads	Number of concurrent threads for data import.
   auto_inc	Whether to use AUTO_INCREMENT. Set to off for this test.

   sysbench --config-file='sysb.conf' \
     --create-table-options='dbpartition by hash(id)' \
     --tables='16' \
     --table-size='10000000' \
     --threads='16' \
     --auto_inc='off' \
     oltp_point_select prepare

Step 5: Warm up the buffer cache

Before collecting results, run a warm-up pass to load data from disk into memory. Skipping this step causes the first run to produce lower QPS due to cold-cache reads.

sysbench --config-file='sysb.conf' \
  --db-ps-mode='disable' \
  --mysql-ignore-errors='all' \
  --tables='16' \
  --table-size='10000000' \
  --threads=<threads> \
  oltp_point_select run

We recommend that you increase the duration of this first run to allow the cache to stabilize before you collect benchmark results.

Step 6: Run the stress tests

Run the following commands to test each of the six Sysbench OLTP workloads. Replace <threads> with the number of concurrent threads.

The parameters used in all run commands:

oltp_point_select

sysbench --config-file='sysb.conf' \
  --db-ps-mode='disable' \
  --mysql-ignore-errors='all' \
  --tables='16' \
  --table-size='10000000' \
  --threads=<threads> \
  oltp_point_select run

oltp_read_only

sysbench --config-file='sysb.conf' \
  --db-ps-mode='disable' \
  --mysql-ignore-errors='all' \
  --tables='16' \
  --table-size='10000000' \
  --range-size=5 \
  --threads=<threads> \
  oltp_read_only run

oltp_read_write

sysbench --config-file='sysb.conf' \
  --db-ps-mode='disable' \
  --mysql-ignore-errors='all' \
  --tables='16' \
  --table-size='10000000' \
  --range-size=5 \
  --threads=<threads> \
  oltp_read_write run

oltp_update_index

sysbench --config-file='sysb.conf' \
  --db-ps-mode='disable' \
  --mysql-ignore-errors='all' \
  --tables='16' \
  --table-size='10000000' \
  --threads=<threads> \
  oltp_update_index run

oltp_update_non_index

sysbench --config-file='sysb.conf' \
  --db-ps-mode='disable' \
  --mysql-ignore-errors='all' \
  --tables='16' \
  --table-size='10000000' \
  --threads=<threads> \
  oltp_update_non_index run

oltp_write_only

sysbench --config-file='sysb.conf' \
  --db-ps-mode='disable' \
  --mysql-ignore-errors='all' \
  --tables='16' \
  --table-size='10000000' \
  --threads=<threads> \
  oltp_write_only run