PostgreSQL

Parameters tuned

The parameters tuned by DBtune changed in respect to the tuning mode. Below are the three tuning modes we support.

Reload-only tuning mode

This tuning mode tunes without restarting the database and using reload only. This does not cause any downtime and is suitable for production environments. The following parameters are tuned in this mode:

1. work_mem
2. random_page_cost
3. seq_page_cost
4. checkpoint_completion_target
5. effective_io_concurrency
6. max_parallel_workers_per_gather
7. max_parallel_workers
8. max_wal_size
9. min_wal_size
10. bgwriter_lru_maxpages
11. bgwriter_delay

Restart tuning mode

Most of our user base prefer reload-only tuning to avoid disruptions in production. If your system can handle restarts gracefully and you want to tune more parameters, you can chose this mode which restarts the database up to 30 times in a span of few hours and provide you with additional performance. The following parameters are tuned in this mode in addition to the parameters tuned in the reload-only mode:

12. shared_buffers
13. max_worker_processes

Restart-once tuning mode

The reload-only tuning mode provides an additional pre-flight system check to ensure the system is in a healthy state and performing defaults. These checks are happening only at the beginning of the tuning session. The checks and corresponding suggestions (that will require only one restart) are as follows:

1. Shared Buffers

• Check: Verify if shared_buffers is less than 25% of total memory.
• Suggestion: Adjust shared_buffers to 25% of total memory.

Rationale: Allocating 25% of the system RAM to shared buffers is recommended by the official PostgreSQL documentation. This setting is generally sufficient for efficient operations, balancing memory use between PostgreSQL and other system processes. Only in edge cases, such as when the entire database fits in memory, might different values yield significant improvements. For more information, refer to the PostgreSQL documentation.

References: PostgreSQL.org, EnterpriseDB, pgAnalyze.

2. Max Worker Processes

• Check: Verify if max_worker_processes, is less than 2 times the CPUs.
• Suggestion: Adjust max_worker_processes to the following value: max(2 * num_of_cpus + free_worker, 8 + free_workers).
  The free_workers is calculated by the formula free_workers = max_worker_processes - max_parallel_workers.

Rationale: The formula for max_worker_processes ensures that the parameter is large enough to handle the various processes efficiently without being excessive. This balance helps maintain system stability and performance under normal operational loads.

How will the suggestion be recommended and applied?

The DBtune agent will calculate the suggested values for the parameters and recommend applying them with a restart. You can then choose to apply the suggestion or not within the DBtune agent process. The suggestion will happen only once when you start the agent.

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 is the data DBtune collects and sends to the DBtune server every second:

Collected data

Category	Subcategory
CPU stats	cpu_util
Memory stats	free
	slab
	used
	total
	active
	cached
	shared
	buffers
	percent
	inactive
	available
IO stats	busy_time
	read_time
	read_bytes
	read_count
	write_time
	write_bytes
	write_count
	read_merged_count
	write_merged_count
DB Stats	Throughput
	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

Category	Subcategory
Hardware	Number of CPUs
	Total memory
	Available memory
	Cloud provider
	Instance type
	Disk type
Software	Database 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.