Using Benchmark Boot for Performance Testing

This article provides a brief and easy-to-understand guide on how to perform performance testing on PolarDB-X using Benchmark Boot.

Introduction

Benchmark Boot is a comprehensive benchmarking platform specifically designed for PolarDB-X. It offers support for various benchmark tests including Sysbench, TPC-C, and TPC-H.

Function Roadmap

Console

• Concurrent import of test data
• Automatic tuning for TP/AP scenarios
• Viewing historical tasks
• Visualization of benchmark results using charts
• Visual comparison of historical tasks
• Real-time display of results (QPS/tpmC)
• Query historical tasks with specified conditions
• Verify the integrity of the dataset
• Automatic warming up

Sysbench

• Customizable workload

TPC-C

• Customizable number of partitions for table creation statements
• Transaction response time statistics
• Custom jdbcUrl parameters

TPC-H

• Disk space reminder for benchmarking machines
• Customizable number of partitions for data import
• Injection of execution plans based on HINTs
• Saving table creation, index, and other information
• Frontend display of execution plans
• Comparison of execution plans

Deployment and Startup

In this section, please follow the three steps of Preparation, Download and Installation, and Verification.

Preparation

1. Prepare a machine for load testing (>= 4C8G, to avoid the load testing machine becoming a performance bottleneck), which can access the target database;
2. Prepare a machine with a browser (can be the same machine as the load testing machine), which can access the port 4121 of the load testing machine.;
3. Configure the hosts file of the frontend machine by adding {load testing machine IP} benchmark-boot to the end of the hosts file and save it.(there is no need to configure the hosts file since version V1.2.1).

Notes:

1. The hosts file on Windows systems is located in the "C:\Windows\System32\drivers\etc folder". (there is no need to configure the hosts file, since version V1.2.1).
2. It is recommended to have the load testing machine and the target database in the same intranet to avoid the network becoming a performance bottleneck.

Download and Installation

Please select the appropriate installation method based on the operating system of the load testing machine.

Linux

Supported operating systems：

• x86
  • CentOS 7.9, 8.x
  • AliOS 3
  • Ubuntu 18.x
• arm
  • CentOS 8.x

One-click installation:

bash -c "$(curl -fsSL https://benchmark-boot.oss-cn-hangzhou.aliyuncs.com/setup.sh)"

Offline installation：

1. Download the installation script on a machine with internet access:Installation script
2. Edit the installation script setup.sh, refer to the code inside, and download the corresponding benchmark-boot-{architecture}-{release time}.tar.gz package using wget according to the hardware architecture of the target machine. Then transfer the compressed package to the target machine.
3. Input tar xzf benchmark-boot.tar.gz, execute bash ./install-and-run.sh to install（the target machine needs to have a package manager source for the dependency library）

1. Use Account root, execute One-click installation in /root directory（it is not mandatory to install in the /root directory）
2. Make sure there is no JRE in the current environment. Otherwise, there will be an error during startup because the startup program relies on Java 11 (If JRE already exists, after the installation, use alternatives --config java to switch to JDK 11 and then start again).
3. If you want to keep the default JDK version (e.g., Java 8), you can manually edit the install-and-run.sh script to specify the JDK 11 path for the Benchmark Boot startup command.
4. If there are errors during dependency installation or conflicts with existing dependencies: you can try manually modifying the install-and-run.sh script to remove the corresponding dependencies from the installation, and then execute the script again.

For other incompatible operating systems, you can refer to the deployment method based on Docker images below.

Windows Or Macos

After installing Docker, execute the following command to run the container:

docker pull polardbx/benchmark-boot:latest

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

Verification

Open a web browser on the frontend machine and visit http://{frontend machine IP}:4121/，If the Benchmark Boot homepage appears, it indicates that the deployment was successful.

Usage

Database Connection Configuration

Configure the connection to the target database instance for load testing. Connectivity testing and database schema detection will be automatically performed upon submission.

1. If you are testing Sysbench, only fill in the Sysbench database name; if you want to test multiple tasks on the same instance, fill in the corresponding database names separately.
2. If the corresponding database has not been created yet, simply select the schema creation mode, and it will be automatically created.
3. If you are using load balancing with multiple addresses, you can enter the IP1:PORT1,IP2:PORT2... in the Host IP field and -1 in the Port field.

Configuration

Successful Configuration

Switching Data Sources

One-Click PolarDB-X Parameter Optimization

Sysbench

Importing

Execution

You can manually enter the parameters or automatically fill the parameters from the previous run:

Running

Results

Real-time Results（this step allows stop the task）

Task-Detail and QPS

Comparison

TPC-C

Importing

Input the number of threads based on the specifications of the load testing machine and the number of warehouses:

Import Progress

Execution

You can manually enter the parameters or click the button to automatically populate the parameters from the previous run:

Results

Real-time Results

Task-Detail and QPS

Comparison

TPC-H

Importing

You can manually enter the parameters or click the button to automatically populate the parameters from the previous run:

1. TPC-H import does not support concurrency control. For a scale of 10GB or above, it is recommended to have a load testing machine with at least 8C16GB specifications.
2. TPC-H import requires generating text data of the corresponding scale on the load testing machine. Please ensure that there is sufficient disk space available on the load testing machine.

TPC-H Import Progress

After importing, the corresponding indexes will be added to the tables automatically, and analyze table will be executed to collect statistics.

Execution

You can run all queries (22 queries) or manually specify the query ID:

1. id=0 represents the HINT used by all queries，more information about HINT: How to Use HINT
2. HINT is optional. If not specified, the queries will be executed according to the plan selected by the optimizer.
3. If you select all queries, it will run all 22 queries. If you select specific queries, only those queries will be executed.

Explain

Results

Real-time Results

Comparison

Changelog

2023-12-01 (V3.2.0)

New Features

1. TPC-H supports columnar execution plan detection.
2. TPC-H integrity check supports printing the dataset size.
3. TPC-H result page displays the execution statements for each query.
4. Database connection configuration displays the current instance type.

Bug Fixes and Improvements

1. Fixed the number of partitions for the region and nation tables in the TPC-H columnar index creation statements.
2. Fixed the issue of missing log output during TPC-H data import.
3. Fixed the issue of possible submission of multiple tasks at the same time in TPC-H.
4. Fixed the incorrect task status after stopping a Sysbench task.
5. Fixed the issue of missing display for the same host but different ports in the database connection selection.
6. Improved the parameter settings for one-click optimization of AP queries.
7. Optimized the TPC-H analyze table script.

2023-11-08 (V3.1.2)

Bug Fixes and Improvements

1. Fixed the error of task platform failing to retrieve the current task during the transaction test.
2. Updated the binary package for the transaction test program.

2023-10-27 (V3.1.1)

New Features

1. TPC-H provides additional guidance prompts for creating columnar indexes.
2. TPC-H includes built-in script for result accuracy check (currently only supports command line).

Bug Fixes and Improvements

1. Fixed the issue of populating the previous task group's run parameters in Sysbench.
2. Improved the field types in the TPC-H table creation statements.

2023-10-09 (V3.1.0)

New Features

1. Sysbench supports running task groups, where multiple workload scenarios can be queued with different concurrency levels.
2. Sysbench supports the "IN" query scenario, including primary key "IN" queries and index "IN" queries, with the ability to set parameters such as the number of "IN" values.

Bug Fixes and Improvements

1. Fixed the issue of incorrect data in the streamed generation of TPC-H orders and lineitem tables.
2. Optimized the task ID generation rule.

2023-05-23 (V3.0.0)

New Features

1. Added transaction test.
2. TPC-C supports stored procedure mode.

Bug Fixes and Improvements

1. Improved error output in the backend logs when import fails for easier troubleshooting.
2. Added a check for running the same benchmark before running the benchmark to avoid incorrect task status updates.
3. Frontend displays the one-click optimization SQL text.

2023-04-24 (V2.1.0)

New Features

1. TPC-C data import now supports resumable data import with manual specification of the starting warehouse.

Bug Fixes and Improvements

1. Fixed the issue of Sysbench automatically selecting single table balanced mode in DRDS mode.
2. TPC-C import will exit early after reporting an error (as the dataset may not be complete) and will not continue executing operations such as adding indexes and collecting statistics. The last successful imported warehouseID will be printed for resumable import.

2023-04-18 (V2.0.0)

New Features

1. For Sysbench and TPC-C, support manual refreshing to view real-time QPS/tpmC curves.
2. TPC-C supports recording and displaying response time statistics for each type of transaction (manual selection required).
3. TPC-H supports streaming data generation and import without requiring a corresponding amount of disk space to be reserved.
4. Support rerunning the current benchmark from the real-time data interface.

Bug Fixes and Improvements

1. Optimized the error judgment of available memory in the container when starting the load testing script, preventing OOM issues caused by incorrectly determining the available memory of the container as the physical machine's memory.
2. Optimized the write parallelism setting during import in the AUTO library.
3. Optimized the TPC-C table creation statements to reduce space usage.
4. Fixed the issue of manual stop not working during TPC-H data import.
5. Removed some ineffective frontend components.

2023-03-21 (V1.5.2)

Bug Fixes and Improvements

1. Fixed the issue of Sysbench running incorrectly in the oltp_update_index scenario.
2. Fixed the issue of TPC-H unable to stop running queries.
3. Fixed the error when reading the empty cross-database ratio parameter in the built-in TPC-C.
4. Enabled transaction support in the oltp_update_index and oltp_update_non_index scenarios in Sysbench.

2023-03-20 (V1.5.1)

New Features

1. Sysbench supports automatic sharding of single tables in AUTO mode during import.
2. TPC-C supports setting the cross-database transaction ratio.

Bug Fixes and Improvements

1. Fixed the import error in TPC-H when parsing addresses fails in non-load balance mode.
2. Sysbench table creation defaults to alignment with the default database in DRDS mode.

2023-03-13 (V1.5.0)

New Features

1. Added support for entering load-balanced multiple IP addresses in the "Database Connection" panel.
2. Sysbench supports data import and load testing in load-balanced mode.
3. TPC-C supports data import and load testing in JDBC load-balanced mode.
4. TPC-H supports data import in JDBC load-balanced mode.

Improvements

1. Automatically adjust the "NEW SEQUENCE" parameter during high-concurrency import.

2023-02-21 (V1.4.1)

Bug Fixes and Improvements

1. Fixed the parsing of other parameters in Sysbench.
2. Added the "report-interval" parameter for other parameters in Sysbench (interval time for output).
3. Improved the error recovery logic in the installation and startup script, and added a check for the current Java version before starting.

2023-02-06 (V1.4.0)

Improvements

1. Added support for one-click installation and startup in directories other than '/root'.
2. Optimized the log rolling and compression strategy in the backend.
3. Improved the log output in the one-click installation script and added a check for successful startup of the Benchmark-Boot backend program.
4. Optimized the issue of client getting stuck when encountering a large number of errors in TPC-C.

2023-02-03 (V1.3.3)

Features

1. Sysbench and TPC-C support automatically setting the default table partition number based on the number of DNs (only in Auto mode).
2. Sysbench supports customizing the table partition number (only in Auto mode).

2023-02-01 (V1.3.2)

Features

1. TPC-C import supports customizing the table partition number (only in Auto mode).
2. Added the ability to stop TPC-C import/load testing processes on the graphical interface.

2022-11-16 (V1.3.1)

Features

1. TPC-H import supports manually setting the concurrency level (based on the number of cores available on the test machine).

Bug Fixes and Improvements

1. Reduced Sysbench compilation logs.
2. Fixed the issue of garbled characters in TPC-H data import using BatchTool in Docker containers.
3. Fixed the issue of incorrect unit of duration when running TPC-C and filling in the previous run parameters.
4. Automatically adjust the innodb_commit_cleanout_max_rows parameter before and after data import.

2022-11-10 (V1.3.0)

Features

1. Added the ability to record data import tasks in the "Benchmark Results" panel, including statistics on import duration.
2. Added the text prompt "Task has finished" when real-time result output ends.

Bug Fixes and Improvements

1. Fixed the issue of websocket connection failure in real-time TPC-H results.
2. Fixed the issue of missing binary programs for generating data in TPC-H on ARM.
3. Fixed the error when deleting database connection information.
4. Fixed the issue of error in TPC-C data import not being printed on the front-end.
5. Fixed the value of MPP_METRIC_LEVEL in the "One-click Optimization" - Disable CPU information sampling.
6. Added adjustment of XPROTO_MAX_DN_WAIT_CONNECTION in the "One-click Optimization" - Increase the concurrency limit of private protocols option.
7. Changed the display of TPC-C task duration to seconds, consistent with other benchmarks.
8. Reduced the warning log printing in the one-click installation script.

2022-11-07 (V1.2.2)

Bug Fixes and Improvements

1. Modified the import scripts of TPC-C and TPC-H to support testing machines with low memory.
2. Automatically limit the maximum concurrent import based on the remaining available memory on the test machine (TPC-C).
3. Added prompts for suggested database specifications in the "One-click Optimization" option.

2022-11-03 (V1.2.1)

Bug Fixes and Improvements

1. Improved user experience by not requiring the configuration of the benchmark-boot domain on the front-end machine.
2. Validated the import parameters during data import to avoid issues with excessive concurrency, etc.
3. Disabled the "Data Verification" button on the front-end during data import or load testing.
4. Automatically perform "analyze table" to collect statistics after importing TPC-C data.

2022-10-26 (V1.2.0)

Features

1. Added support for data set integrity verification.
2. Added new parameters for one-click optimization (AHI, table_open_cache, etc.).
3. Released Docker images for ARM platforms.

Bug Fixes and Improvements

1. Fixed the logic of checking if the text has been generated during TPC-H data generation.
2. Fixed the table creation statement for TPC-C in regular MySQL mode.
3. Adjusted the connection string for TPC-C to increase the timeout and disable SSL.

2022-10-21 (V1.1.0)

Features

1. Added support for searching historical tasks based on multiple criteria.
2. Added support for batch deletion of historical tasks.
3. Added an introduction to common parameters in Sysbench.
4. Added support for directly switching the currently used database to avoid duplicate input of connection strings.

Bug Fixes

1. Fixed the issue where the benchmark configuration file was not automatically updated when switching database connections.

2022-10-09 (V1.0.0)

Features

1. Added SQL display for corresponding parameter optimization in the front-end interface.
2. Disabled some unsupported features in the front-end (global concurrency setting, TPC-H import concurrency setting).
3. Removed the watermark in the front-end.
4. Improved the prompt for import concurrency setting.

Bug Fixes

1. Fixed the issue where the option to skip automatic table creation during TPC-H import was not working.

2022-09-23

Features

1. Added display of platform version in the front-end, and provided additional table option prompts.
2. Added a minimum requirement for test machine specifications (>=4C8G).
3. Added a one-click installation command for Linux：bash -c "$(curl -fsSL https://benchmark-boot.oss-cn-hangzhou.aliyuncs.com/setup.sh)"

Bug Fixes

1. Fixed JVM error during TPC-C execution: stack size too small.