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.

Partitioned Tables Each table in Sysbench is sharded by the id column, allowing large tables to be evenly distributed across multiple nodes through partitioning, offering linear scalability for scenarios like e-commerce transactions.
Balanced Single-Table All Sysbench tables are created as single tables, which are evenly distributed across multiple nodes through partitioning, providing linear scalability for multiple single tables, such as in SaaS scenarios.

Host Resources

Component Name Model Description
OPS Machine ecs.g7.4xlarge For installation and deployment
Compute Node (CN) ecs.hfg7.4xlarge * 3 16c64g Unit price: 2324 CNY/month
Storage Node (DN) + Meta Node (GMS) ecs.i4.4xlarge * 3 16c128g + 3.5TB storage Unit price: 3776 CNY/month

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

Component Name Model Description
Stress Test Machine ecs.hfg7.6xlarge 24c96g

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-2.0:latest
    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:latest
    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-2.0:latest
    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.

benchmark_connect

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.

benchmark_optimize

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.

benchmark_sbtest_auto

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.

benchmark_sysbench_load

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.

benchmark_sbtest_single

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.

benchmark_sysbench_load_single

Step 5: Execute the Stress Test

Stress Test Scenario Name
Point-Select oltp_point_select
Read-Only oltp_read_only
Read-Write oltp_read_write
Update-Index oltp_update_index
Update-Non-Index oltp_update_non_index
Write-Only oltp_write_only

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.

benchmark_sysbench

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

benchmark_sysbech_run

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.

benchmark_sysbench_watch

benchmark_sysbench_result

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

workload 200 threads 400 threads 600 threads 800 threads 1000 threads 2000 threads
oltp_point_select 298986.10 332317.22 353429.85 368768.08 384603.36 401115.95
oltp_read_only 127038.97 149952.23 158191.47 164165.17 167982.73 175842.29
oltp_read_write 70659.78 93330.17 100237.59 106222.37 110953.07 122152.42
oltp_update_index 25998.96 34852.18 42258.07 48654.28 51277.58 65098.83
oltp_update_non_index 26524.99 33817.18 41295.47 48256.25 52668.72 65155.87
oltp_write_only 25870.66 34311.45 40751.06 47657.71 50757.05 61934.61

Balanced Single Table Performance

workload 200 threads 400 threads 600 threads 800 threads 1000 threads 2000 threads
oltp_point_select 301323.14 330835.04 349769.66 365575.37 379552.11 396272.64
oltp_read_only 266093.60 296501.34 315341.23 325793.21 339478.66 351669.63
oltp_read_write 84944.97 115190.00 134560.57 148352.19 156346.45 172508.63
oltp_update_index 26522.20 35441.67 41753.73 47883.84 53172.88 64892.92
oltp_update_non_index 26247.67 34830.22 41075.44 46921.88 52130.87 65652.31
oltp_write_only 26085.07 34952.64 40768.98 46192.18 50490.49 59538.28

results matching ""

    No results matching ""