Parameter tuning in PostgreSQL involves the adjustment of various configuration settings inside postgresql.conf file which dictates how the database operates. These parameters affect many aspects of the database’s operation which includes memory allocation, query planning, connection handling and disk I/O operations. Proper tuning ensures that PostgreSQL runs efficiently, making full use of the available hardware resources

Why Is Parameter Tuning Essential?

PostgreSQL database is installed with default parameter settings that can work with a wide range of applications. However, the needs of each application can vary, which is influenced by factors such as the nature of the data, how it is accessed (whether the workload is more read or write intensive) and how the queries are designed.

The main goal of adjusting parameters is to make the database work better. This means speeding up query response times and more effective use of resources like memory and processing power. When there is a load, a well-tuned database can manage extra work without slowing or crashing it down. Before investing in additional hardware to address performance problems, it’s important to first assess whether the database is making full use of the existing hardware.

Understanding DBtune

DBtune is an AI-powered service designed to automate and optimize the process of database parameter tuning. It utilizes advanced algorithms and machine learning techniques to analyze your database workload and suggests optimal configuration.

You can check it out here: https://www.dbtune.com/

Benefits of Using DBtune

DBtune continuously monitors PostgreSQL catalog views, hardware usage (Memory/Disk/CPU) and then adjusts settings to maintain optimal performance:

• DBtune fully automates the parameter tuning process, eliminating manual effort and reduces the time required for tuning.
• DBtune provides tuning suggestions as per your specific workload and hardware setup which makes sure that your PostgreSQL database is operating at its peak efficiency.
• DBtune minimizes the risk of incorrect configuration, which can lead to performance degradation or downtime.

Which PostgreSQL parameters are modified by DBtune?

When configuring a database for optimization with DBtune, it asks if it is permissible to restart the database. This is because some PostgreSQL parameters only take effect after a restart. If DBtune is authorized to restart the database, it will adjust the following parameters:

1. shared_buffers: Specifies how much dedicated memory is allocated for caching data within PostgreSQL.
2. work_mem: Determines the amount of memory used for operations within individual queries, such as sorting and hashing.
3. max_wal_size: Specifies the maximum size of the Write-Ahead Logging (WAL) files.
4. seq_page_cost: It represents the cost of reading sequential disk pages. And helps the planner in deciding the most efficient method to execute a query
5. random_page_cost: Similar to seq_page_cost, but for random disk page reads. A lower value can make the planner favor index scans over sequential scans.
6. checkpoint_completion_target: This setting is used to spread out disk I/O and reduce peak load during checkpoint operations.
7. effective_io_concurrency: This parameter helps PostgreSQL optimize its use of available I/O channels. Note that this is only used for bitmap operations.
8. bgwriter_lru_maxpages: Determines the maximum number of buffer pages that the background writer process can write per round. It works in conjunction with checkpointer process
9. max_worker_processes: The maximum number of background processes that the database can support at any given time. It’s an important setting because these worker processes are used for a variety of purposes, including carrying out parallel queries, autovacuum workers etc.
10. max_parallel_workers: Sets the maximum number of parallel workers that can be used globally in PostgreSQL. This parameter value is bounded by max_worker_processes:
11. max_parallel_workers_per_gather: Specifies the maximum number of parallel processes that can be used for a single node of a query. This parameter value is bounded by max_parallel_workers.

IF DBtune is not allowed to restart the PostgreSQL database instance, it will focus on following parameters as they only need a reload signal to take effect.

• work_mem
• max_wal_size
• seq_page_cost
• random_page_cost
• checkpoint_completion_target
• effective_io_concurrency
• bgwriter_lru_maxpages

DBtune in Action

Create a new VM on AWS (m5d.xlarge)

We are using Ubuntu focal distribution to run this test.

Install git repo

git clone https://github.com/dbtuneai/synthetic_workload.git

Set disk mountpoint variable

export VAR=nvme1n1  => Can be found using lsblk command

Output of lsblk command:

NAME     MAJ:MIN RM   SIZE RO TYPE MOUNTPOINT

loop0      7:0 0  24.9M  1 loop /snap/amazon-ssm-agent/7628

loop1      7:1 0  55.7M  1 loop /snap/core18/2790

loop2      7:2 0  40.9M  1 loop /snap/snapd/20290

loop3      7:3 0  63.5M  1 loop /snap/core20/2015

loop4      7:4 0  91.9M  1 loop /snap/lxd/24061

nvme1n1  259:0 0 139.7G  0 disk

nvme0n1  259:1 0   100G  0 disk

├─nvme0n1p1  259:2 0  99.9G  0 part /

├─nvme0n1p14 259:3 0 4M  0 part

└─nvme0n1p15 259:4 0   106M  0 part /boot/efi

Setup synthetic workload

cd synthetic_workload

source setup_ubuntu_20.sh

Load synthetic data from BenchBase

cd benchbase-postgres

python3 loader.py --benchmark resourcestresser

Run benchmark

cd benchbase-postgres

python3 runner.py --benchmark resourcestresser

Observations

Tuning Session

For DBtune integration, first create a trial account on DBtune Create Account.

And follow the steps on DBtune Documentation (starting from Page 8).

Following options were selected with DBtune as preferred recommendations.

• Database =  PostgreSQL
• Database Hosting = Self-Managed
• Restarts = on
• Tuning Target = Query Runtime (You can also choose Throughput to focus on increasing TPS)

Let your workload run for 5-10 minutes and then start the DBtune process.

sudo su → Switch to root user

python3 dbtune.py

click on the Start Tuning button on GUI.

Total 4 tests were performed to check DBtune recommendations. The tuning session completed 30 iterations, each one accompanied by modifications to the postgresql.conf file and a subsequent restart. Each iteration ran for an average time of 7 minutes. In each iteration, it monitors postgresql performance based on current parameters, query execution times, pg_stat_statement view statistics and hardware usage.

The current nature of the workload appears to be focused solely on executing updates on the iotable, involving 50 concurrent users operating in Repeatable Read mode.

Table Structure:

Iosmalltable

Id int PK,

Value text

Iotable

Id int PK,

Value1 Text,

Value2 Text,

.,

.,

Value 15 Text

Table sizes:

Iotable: 37GB

Iosmalltable: 730MB

Test Case #1

This test began using the default values set in the postgresql.conf file.

So DBtune gave the best setting after 30 iterations. Here we got 13.6x improvement, reducing the query runtime to 1.3 ms and increased the overall throughput by 4.8x.