Skip to main content

PostgreSQL

Parameter tuning

For PostgreSQL, DBtune tunes the following parameters without restarting the database:

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

If you decided that your system can handle restarts gracefully and you want to tune more parameters, DBtune can also tune the following parameters with a restart and provide you with additional performance:

  • shared_buffers
  • max_worker_processes
  • max_parallel_workers
  • max_parallel_workers_per_gather

Optimization Objectives

Average Query Runtime

Average query runtime represents latency, which is measured in milliseconds ms. DBtune computes this using the calls and total_exec_time columns from the pg_stat_statements table.

Throughput

Throughput is defined as the number of transactions that the database completes successfully. DBtune computes PostgreSQL's throughput from the xact_commit metric in the pg_stat_database statistics table.

Client monitoring stats

info

DBtune only retrieves performance metrics from the database and does not access or transmit any sensitive data, e.g., metadata and the tables data are not transferred.

System monitoring (posted every second)

Below are the data DBtune collects and sends to the DBtune server every second:

Collected data
CategorySubcategory
CPU statscpu_util
Memory statsfree
slab
used
total
active
cached
shared
buffers
percent
inactive
available
IO statsbusy_time
read_time
read_bytes
read_count
write_time
write_bytes
write_count
read_merged_count
write_merged_count
DB StatsThroughput
Query Runtime

System information (posted when the agent starts)

In less frequent intervals dbtune fetches the following data to understand the specifications of the system:

Collected data
CategorySubcategory
HardwareNumber of CPUs
Total memory
Available memory
Cloud provider
Instance type
Disk type
SoftwareDatabase version
Operating system type
Maximum connections
Database size

To fetch the hardware information such as number of CPUs, total memory, and available memory, we utilize Python's psutil library. Custom methods were developed to retrieve cloud provider, instance type, and disk type. The software information operating system type is retrieved using a Python library named platform. Database version and max connections are obtained by querying the database system directly, i.e., SHOW server_version and SHOW max_connections. A custom method is implemented to fetch disk size.