pg_tuner

pg_tuner is another automated PostgreSQL database parameter tuning tool based on the Bayesian Optimization technique, utilizing the Optuna library.

As mentioned in the References section, several RDBMS tuning tools already exist.

Here are some key features of pg_tuner:

• PostgreSQL-specific: Designed specifically for tuning PostgreSQL databases.

• Scenario-Based Workloads: Simulates realistic workloads by scheduling tasks (e.g., Multiple tasks can run concurrently. Task A starts at 1 minute, runs for 10 minutes; Task B starts at 5 minutes, runs for 8 minutes, etc.)

• Extensible Benchmark Support: Currently supports sysbench and pgbench, but allows adding custom benchmarks through similar classes.

• Comprehensive Monitoring: Captures not only RDBMS statistics but also OS (Linux only) statistics by leveraging the pg_linux_stats module (optional).

Installation

See README: Installation.

Quick Start

See README: Quick Start.

Configuration

See README: Configulation.

Commands

See README: Commands.

History

Initially, I was looking for a set of PostgreSQL statistics to utilize as training data for automating PostgreSQL management through reinforcement learning and deep learning techniques. However, it is very difficult for an individual to acquire a large volume of statistical information about practical systems. As a result, I decided to develope a data generator along with a set of tools for collecting statistical information.

Later, when I was considering this tool, I realized its potential to not only generate data but also optimize PostgreSQL configuration parameters.

Many parameter optimization tools find the best settings through numerous trials, essentially discarding the results from all but the winning configuration. In contrast, my tool collects statistics from all trials and, as a by-product, identifies the optimal configuration parameters.

All the data collected is valuable for my purposes. For example, statistical information from trials with poor performance due to parameter tuning failures can be used as training data for diagnosing anomalies in machine learning or reinforcement learning.

The name pg_tuner reflects the functionality of the by-product rather than its original purpose.

References

• Bayesian Optimization (BO) based Models:

  • postgres_opttune
  • Ottertune paper
  • Tuneful
  • ResTune (BO + meta-learning)
  • RelM (BO + Deep Distributed Policy Gradient:DDPG)
  • CGPTuner (Contextual Gaussian Process Bandit Optimisation:CGPBO, extension of BO)

• Reinforcement Learning (RL) based models:

  • DMSConfig
  • CDBTune
  • QTune

Change Log

10.May.2014: Version 1.0 Released.