How to load test PostgreSQL database and not miss anything

During the process of load testing Tantor Postgres databases or other PostgreSQL-based systems using the widely adopted tool pgbench, practitioners frequently encounter challenges that lead to non-representative results. A common issue arises from the lack of detailed documentation regarding the testing environment, including DBMS configurations, server specifications, and PostgreSQL versions. This often necessitates repeated tests, consuming valuable time and resources. To address these concerns, the innovative tool pg_perfbench has been developed. This tool ensures repeatability of testing scenarios, safeguards critical data, and simplifies result comparisons by consolidating all parameters into a single, cohesive template. It automatically initiates pgbench with TPC-B load generation, gathers comprehensive metadata about the testing environment, and produces a structured report.

Automation of PostgreSQL-based DB Load Testing and System Information Collection

In the realms of development and support, the necessity for swift load testing often arises to validate hypotheses or assess the overall performance of Tantor Postgres DB or other PostgreSQL-based databases. Typically, specialists resort to pgbench for TPC-B load generation and subsequent evaluation of results. However, the absence of a clear description of the testing environment can lead to ambiguous conclusions regarding performance. Key aspects such as PostgreSQL parameters, server hardware characteristics, client versions, and network settings are frequently overlooked, resulting in the need for retesting.

To mitigate these issues and reduce the likelihood of overlooking crucial details, the pg_perfbench tool has been introduced. This tool not only initiates pgbench but also captures extensive data regarding the database environment, subsequently generating a comprehensive report based on a predefined template. This functionality facilitates the replay of testing scenarios, documents essential database environment details, and allows for rapid comparison of test results when necessary.

Motivation for Development

Consider a typical scenario where performance measurement is required for Tantor Postgres or other PostgreSQL-based databases. The objective is not only to ascertain transactions per second but also to compile a detailed account of the conducted tests. In practice, database administrators and developers face several challenges:

• Assessing database behavior under increased load (e.g., number of connections, data size).
• Comparing performance across different PostgreSQL versions or configurations (e.g., variations in parameters like shared_buffers, work_mem, enabled extensions).
• Documenting test results for future analysis and replay.

However, without a comprehensive description of the environment—including OS versions, network infrastructure parameters, server characteristics (CPU, RAM, disk subsystem), and PostgreSQL configurations—subsequent analysis and replay of experiments become significantly more complex. Even minor discrepancies, such as variations in data directories or hardware configurations, can dramatically influence results, rendering comparisons between tests impractical. This underscores the necessity for a tool that supplements standard load testing with automatic report generation, capturing both performance metrics and environmental details.

Standard Load Testing Cycle with pgbench

pgbench operates on a TPC-B model, enabling rapid acquisition of various performance metrics:

• tps (transactions per second) – the number of transactions executed per second.
• Latency average – average latency between transaction executions, measured in milliseconds.
• Number of transactions processed – total number of successfully processed transactions.
• Number of failed transactions – transactions that concluded with an error.
• Initial connection time – time taken to establish the initial connection with the database.

For demonstration, consider a simple Bash script that cyclically launches pgbench with varying numbers of clients:

/usr/lib/postgresql/15/bin/pgbench -i --scale=4000 --foreign-keys -h `hostname` -p 5432 -U postgres test

#!/bin/bash
clients="1 10 20 50 100"
t=600
dir=/var/lib/postgresql/test_result
mkdir -p $dir
for c in $clients; do
echo "pgbench_${c}_${t}.txt"
echo "start test: "`date +"%Y.%m.%d_%H:%M:%S"` >> "${dir}/pgbench_${c}.txt"
/usr/lib/postgresql/15/bin/pgbench -h `hostname` -p 5432 test -c $c -j $c -T $t >> "${dir}/pgbench_${c}.txt"
echo "stop test: "`date +"%Y.%m.%d_%H:%M:%S"` >> "${dir}/pgbench_${c}.txt"
done

This script:

1. Initializes the pgbench test database with a scale of 4000 and foreign keys.
2. Launches load cycles based on a variable number of clients over a duration of 600 seconds.
3. Logs results into separate files, marking the start and end of each iteration with timestamps.

Typically, a chart is generated at the conclusion, illustrating the relationship between performance indicators (e.g., tps or average latency) and the number of clients. Additionally, a chart depicting the Latency average can be created.

While pgbench provides fundamental performance estimates, a comprehensive analysis requires consideration of various factors:

• Server parameters:
• CPU (model, number of cores, Hyper-Threading support).
• RAM (capacity, frequency, type).
• Disk space (storage type, capacity, RAID configuration).
• Network parameter settings (TCP/IP stack, MTU).
• System drivers (e.g., for SSDs or network cards).
• Database parameters:
• Exact version of PostgreSQL (server and client).
• Enabled extensions (e.g., pg_repack, pg_stat_statements).
• Database configuration (postgresql.conf) and key parameters.
• pg_config results (build options, paths to directories).
• Enabled replications (physical or logical).

Ensuring consistency of these environmental parameters is crucial when replaying tests, as discrepancies can skew results. For instance, differences in CPU architecture or PostgreSQL versions can render direct comparisons meaningless. Thus, thorough documentation of the testing environment is essential for reliable analysis.

The Need for Ready-Made Solutions and Automation

While a variety of open-source utilities exist for conducting load testing and gathering system information, most require manual integration and additional scripting. Administrators often find themselves collecting information about the configuration of Tantor Postgres or other PostgreSQL-based databases, network settings, and operating system kernel versions separately, culminating in a unified report that may lack consistency and comparability.

To streamline the process of data collection and documentation, automation is essential. The pg_perfbench tool addresses this need by facilitating:

• Execution of standard or custom pgbench scripts.
• Simultaneous collection of metadata regarding hardware, OS, and PostgreSQL configurations.
• Automatic generation of output reports based on specified templates.

As a result, stress tests can be conducted with confidence that no critical environmental detail will be overlooked. This comprehensive reporting simplifies result comparisons, reduces the time spent on configuring repeated runs, and enhances the reproducibility of experiments.

Automation of DB Performance Test

The operation of pg_perfbench during load testing significantly simplifies the process, culminating in a structured report that encompasses not only numerical indicators (such as tps and latency averages) but also detailed environmental information. Such a report aids in:

1. Comparing results from different runs and configurations without losing context.
2. Storing all essential information about server configurations, PostgreSQL parameters, and network settings in one location.
3. Replaying test scenarios using a ready-to-use template containing all critical data.

The generated report comprises several core sections:

• System properties – detailing hardware characteristics (processor, memory, disks, network settings).
• Common database info – encompassing PostgreSQL version, enabled extensions, and key database parameters.
• Common benchmark info – specifying pgbench run settings (number of clients, testing duration, commands used).
• Tests result info – summarizing the results of executed tests, displaying performance indicators.

The report’s formatting facilitates easy reading and visualization of test results, utilizing JSON for data storage and HTML for report visualization, allowing for the inclusion of links, styles, and charts. In this implementation, the HTML report contains a nested JSON object filled with data used for visualization. The process unfolds as follows:

1. Running test scenarios and collecting information.
2. Automatic report generation: a JSON file with a detailed structure is created, along with an HTML version of the report based on the same template.
3. Analysis of results: required values are extracted from JSON to construct charts and comparison tables, with the HTML report presenting testing results in an interactive format.

Each section of the report is equipped with parameters for structuring the interactive HTML layout, including descriptions of the server environment and database configuration.

Collecting Data on the DB Environment

To gather server information, terminal commands are typically executed, such as:

df -h   # Disk space
cat /etc/fstab # Description of file systems
cat /etc/os-release # Description of the Linux distribution
uname -r -m # Kernel version

Information about the database environment can be collected using shell or SQL commands stored in separate files. The report item description indicates a file containing these commands, with their results included in the data field.

Consider a filled report section detailing the server environment:

Each report structure item includes parameters for interactive HTML layout, such as:

• header – the specified name of the section;
• description – a brief note;
• state – the state of the report item (collapsed or expanded);
• item_type – type of the returned script result (table or text);
• shell_command_file or sql_command_file – name of the shell or SQL script;
• data – the field where the result of the script execution is recorded.

Configuration of the Database Load Testing

pgbench is configured in pg_perfbench user parameters:

python -m pg_perfbench --mode=benchmark 
--log-level=debug   
--connection-type=ssh   
--ssh-port=22   
--ssh-key=/key/p_key 
--ssh-host=10.100.100.100    
--remote-pg-host=127.0.0.1  
--remote-pg-port=5432   
--pg-host=127.0.0.1 
--pg-port=5439  
--pg-user=postgres  
--pg-password=pswd  
--pg-database=tdb   
--pg-data-path=/var/lib/postgresql/16/main  
--pg-bin-path=/usr/lib/postgresql/16/bin    
--benchmark-type=default    
--pgbench-clients=1,10,20,50,100    
--pgbench-path=/usr/bin/pgbench 
--psql-path=/usr/bin/psql   
--init-command="ARG_PGBENCH_PATH -i --scale=10 --foreign-keys -p ARG_PG_PORT -h ARG_PG_HOST -U postgres ARG_PG_DATABASE"    
--workload-command="ARG_PGBENCH_PATH -p ARG_PG_PORT -h ARG_PG_HOST -U ARG_PG_USER ARG_PG_DATABASE -c ARG_PGBENCH_CLIENTS -j 10 -T 10 --no-vacuum"

All settings are documented in the report, providing a complete description of tool startup. Users can specify the number of iterations by either –pgbench-clients (number of clients) or –pgbench-time (duration) for each test run. The configuration of pg_perfbench user parameters is detailed in the documentation.

pgbench launch iterations will be reflected in the benchmark description section, showcasing the commands used for initialization and loading:

Result of Database Load Testing

pgbench data is conveniently structured in tables, with each row representing one iteration and columns corresponding to key metrics. Following this, a chart can be generated to compare iterations by TPS (transactions per second).

Ultimately, automating the collection of all necessary data and converting it into a user-friendly format (JSON + HTML, charts, tables) significantly enhances both analysis and collaborative efforts on the project. This allows for quicker test reproduction, result comparisons, and identification of performance bottlenecks in PostgreSQL without the need for extensive investigation into the testing conditions.

Comparison of Reports

When testing and analyzing PostgreSQL performance, it is often essential to compare multiple load test results. The pg_perfbench tool offers a mechanism for comparing reports in –mode=join mode. The primary objective is to:

1. Ensure that the environments being compared are indeed corresponding (identical PostgreSQL versions, similar system parameters, and the same set of extensions).
2. After confirming the key elements for matching, generate a consolidated report that highlights differences in settings and benchmark results.

A JSON file structured for comparison may resemble the following:

{
      "description": "Description of the current task",
      "items": [
          "sections.system.reports.sysctl_vm.data",
          "sections.system.reports.total_ram.data",               
          "sections.system.reports.cpu_info.data",
          "sections.system.reports.etc_fstab.data",
          "sections.db.reports.version_major.data",
          "sections.db.reports.pg_available_extensions.data",
          "sections.db.reports.pg_config.data",
          "sections.benchmark.reports.options.data",
          "sections.benchmark.reports.custom_tables.data",
          "sections.benchmark.reports.custom_workload.data"
      ]
}

The “items” field contains an array of object fields representing the report structure. The utility checks for equality of values based on these fields. If any mandatory items differ (e.g., CPU specifications or PostgreSQL versions), the utility halts the join process and displays an error message, thereby preventing incorrect comparisons.

Example log output may include:

2025-03-28 16:31:02,285 INFO  root : 218 - Loaded 2 report(s): benchmark-ssh-custom-config.json, benchmark-ssh-default-config.json
2025-03-28 16:31:02,286 ERROR root : 190 - Comparison failed: Unlisted mismatch in 'cpu_info'
 reference report - benchmark-ssh-custom-config
 comparable report - benchmark-ssh-default-config
2025-03-28 16:31:02,286 ERROR root : 222 - Merge of reports failed.
2025-03-28 16:31:02,286 ERROR root : 317 - Emergency program termination. No report has been generated.

Comparison of DB Performance with Different Settings in postgresql.conf

A common task involves testing the impact of changes to PostgreSQL settings. Consider two configuration files:

postgresql_1.conf:
.....
shared_buffers = 166MB
work_mem = 10000kB
maintenance_work_mem = 20MB
effective_cache_size = 243MB

postgresql_2.conf:
.....
shared_buffers = 90MB
work_mem = 5000kB
maintenance_work_mem = 10MB
effective_cache_size = 150MB

The goal is to evaluate how throughput capacity varies with configuration parameter changes. Running pg_perfbench with these two configurations generates separate reports. For detailed instructions on configuring user parameters, refer to the documentation.

To join the two reports, specify a JSON file listing “items” that exclude pg_settings:

{
    "description": "Comparison of database performance across different configurations in the same environment using the same PostgreSQL version",
    "items": [
        "sections.system.reports.sysctl_vm.data",
        "sections.system.reports.sysctl_net_ipv4_tcp.data",
        "sections.system.reports.sysctl_net_ipv4_udp.data",
        "sections.system.reports.total_ram.data",
        "sections.system.reports.cpu_info.data",
        "sections.system.reports.etc_fstab.data",
        "sections.db.reports.version_major.data",
        "sections.db.reports.pg_available_extensions.data",
        "sections.db.reports.pg_config.data",
        "sections.benchmark.reports.options.data",
        "sections.benchmark.reports.custom_tables.data",
        "sections.benchmark.reports.custom_workload.data"
    ]
}

Executing the following command will check compliance of the specified parameters:

python -m pg_perfbench --mode=join 
--report-name=join-diff-conf-reports 
--join-task=task_compare_dbs_on_single_host.json 
--input-dir=/pg_perfbench/report

The utility will verify that the parameters align, including identical CPU specifications, RAM amounts, PostgreSQL versions, and matching extension sets. If discrepancies are found, an error message will be displayed. The process log will detail loaded reports and the result of the join:

python -m pg_perfbench --mode=join  
--report-name=join-diff-conf-reports    
--join-task=task_compare_dbs_on_single_host.json    
--input-dir=/pg_perfbench/report
2025-03-28 16:53:31,476       INFO                                root :   55 - Logging level: info
.....
#-----------------------------------
2025-03-28 16:53:31,476       INFO                                root :  211 - Compare items 'task_compare_dbs_on_single_host.json' loaded successfully:
sections.system.reports.sysctl_vm.data
sections.system.reports.sysctl_net_ipv4_tcp.data
sections.system.reports.sysctl_net_ipv4_udp.data
sections.system.reports.total_ram.data
sections.system.reports.cpu_info.data
sections.system.reports.etc_fstab.data
sections.db.reports.version_major.data
sections.db.reports.pg_available_extensions.data
sections.db.reports.pg_config.data
sections.benchmark.reports.options.data
sections.benchmark.reports.custom_tables.data
sections.benchmark.reports.custom_workload.data
.....
2025-03-28 16:53:31,481       INFO                                root :   99 - The report is saved in the 'report' folder
2025-03-28 16:53:31,481       INFO                                root :  322 - Benchmark report saved successfully.

The join report will contain:

• System data matches at least for the items listed in the join list. If there are elements not specified in the join list with differences in the data field, the corresponding tab in the final join report will be marked with a special color and labeled “Diff”. The tab will display results for the corresponding items from all compared reports.

On the chart, users can swiftly assess differences in benchmarks based on tps.