Sysbench Test Report

Background Information

Sysbench is an open-source, modular, cross-platform, multi-threaded benchmarking tool that can perform database performance testing in areas like CPU, memory, threads, and I/O.

Benchmark Boot is a one-stop benchmarking platform developed for PolarDB-X, currently supporting benchmarks including Sysbench, TPC-C, and TPC-H, enhancing testing efficiency through a graphical interface.

Test Design

Data Volume for the Test

The sysbench data includes 16 tables, each with a volume of 10 million records.

Test Cases

Sysbench evaluates the performance of PolarDB-X for both partitioned tables and balanced single-table scenarios.

Host Resources

Storage nodes require better I/O, while compute nodes need more powerful CPU processing. Adopting a separate deployment approach can improve the cost-effectiveness of resource utilization.

Stress Test Machines

Test Methodology

Step 1: Prepare the Stress Test Machine

Deploy Benchmark-Boot, refer to the documentation: Using Benchmark Boot for Stress Testing

# Download the image
docker pull polardbx/benchmark-boot:latest

# Start the container
docker run -itd --name 'benchmark-boot' --privileged --net=host \
    -v /etc/localtime:/etc/localtime polardbx/benchmark-boot:latest \
    /usr/sbin/init

# Verification
curl http://127.0.0.1:4121/

For offline deployment, the benchmark-boot image can be downloaded on the OPS machine and then uploaded to a private repository.

Step 2: Prepare the PolarDB-X Instance for Stress Testing

Create a PolarDB-X instance. For detailed steps, please see: Deploy by PXD

Instance creation parameters:

version: v1
type: polardbx
cluster:
  name: pxc-test
  no_logger: false
  gms:
    image: polardbx/polardbx-engine:v2.4.0_8.4.19
    engine: galaxy
    engine_version: "8.0"
    host_group: [192.168.1.102, 192.168.1.103, 192.168.1.104]
    resources:
      mem_limit: 16G
      cpu_limit: 8
  cn:
    image: polardbx/polardbx-sql:v2.4.0_5.4.19
    replica: 3
    nodes:
      - host: 192.168.1.102
      - host: 192.168.1.103
      - host: 192.168.1.104
    resources:
      mem_limit: 64G
      cpu_limit: 16
  dn:
    image: polardbx/polardbx-engine:v2.4.0_8.4.19
    engine: galaxy
    engine_version: "8.0"
    replica: 3
    nodes:
      - host_group: [192.168.1.105, 192.168.1.106, 192.168.1.107]
      - host_group: [192.168.1.106, 192.168.1.107, 192.168.1.105]
      - host_group: [192.168.1.107, 192.168.1.105, 192.168.1.106]
    resources:
      mem_limit: 50G
      cpu_limit: 16

Verify the instance is connectable, open Benchmark-Boot, and select 'Run Stress Test' -> 'Database Connection' on the left. Enter the database connection information into Benchmark-Boot and click "Submit".

Note: PolarDB-X provides multiple CN nodes. For production environments, it is recommended to use a load balancer for access. In POC test scenarios, you can use Benchmark-Boot's built-in load balance mechanism.

Configuration Example:

Enter multiple host IPs, for example: 192.168.1.102:3306,192.168.1.103:3306,192.168.1.104:3306, and fill in -1 for the corresponding port.

Step 3: Adjust Instance Parameters

Through Benchmark-Boot, on the left under 'Run Stress Test' -> 'Database Connection', set the tuning parameters. It is recommended to check all the TP mode optimization parameters.

Step 4: Data Preparation for Stress Testing

Prepare Partitioned Tables

Open Benchmark-Boot, select 'Run Stress Test' -> 'Sysbench' -> 'Switch Database' on the left, enter the database name sbtest_auto, and select AUTO mode.

After submitting, scroll down to the bottom of the page and fill in the data preparation parameters: 16 tables, table size 10,000,000, 16 concurrent threads. Do not check 'Uniform Distribution for Single Table' mode, click submit and wait for the data preparation task to complete.

Prepare Uniform Single Table Data

Open Benchmark-Boot, select 'Run Stress Test' -> 'Sysbench' -> 'Switch Database' on the left, enter the database name sbtest_single, and select AUTO mode.

After submitting, scroll down to the bottom of the page and fill in the data preparation parameters: 16 tables, table size 10,000,000, 16 concurrent threads. Check 'Uniform Distribution for Single Table' mode, click submit and wait for the data preparation task to complete.

Step 5: Execute the Stress Test

The table above lists the Sysbench scenarios included in this stress test. The point-select scenario, oltp_point_select, will be used as an example to demonstrate the stress testing method.

Open Benchmark-Boot and ensure that the correct database is selected for stress testing. If not, click to switch databases.

Select Run Stress Test->Sysbench on the left, and fill in the Run Sysbench form.

Note that additional parameters should be specified in JSON format. Copy the following settings, complete the setup and click to run the stress test.

{
"disable-ps-mode":false,
"range-size":5
}

To view the stress test results, click to view real-time results and enter the Real-Time Data page.

After the stress test is completed, record the results.

You can return to the stress test page, choose to fill in the parameters from the last test, make slight adjustments to the scenario or concurrency and continue testing other scenarios.

Test Results

The following results all use QPS (Queries Per Second) as the performance standard.

Partition Table Performance

Balanced Single Table Performance