Shared Memory Configuration and Tuning for Postgres

Products

• VMware Tanzu Greenplum

Environment

Product Version: 11.5

Resolution

Checklist:
This KB provides guidelines for configuring host resources on a VMware Postgres server.

The main resources which can be configured to improve the performance of VMware Postgres are CPU, RAM, Disk, and Network.

CPU
How Postgres uses the CPU is mainly controlled by the application's behavior, but there is one significant indirect impact which should be managed:
 

• CPU impact of Postgres memory operations: The Linux kernel may perform many operations in the process of allocating memory, the number of these operations can be reduced by managing memory in fewer, bigger chunks.
  • For systems with > 8GB of RAM, allocate 1/4 of the system RAM plus 1GB to hugepages at startup. See Postgres documentation on Linux huge Pages.
  • Disable transparent hugepages. See RedHat documentation for more information.
  • Postgres default settings enable the use of hugepages

RAM

• How much memory is reserved for data in use by Postgres: One quarter of the system's memory is a good default amount of memory to allocate to Postgres shared buffers.
• How much memory is available for operating system cache: Because Postgres relies on the operating system for caching frequently accessed files, at least 30% of the system's memory should be available for this purpose. This does not conflict with the recommendation above except on very small systems or when Postgres is sharing a system with another memory-intensive application.
• How much memory is allocated for Postgres operations: Without specific information about the workload, there is no reason to change defaults for these settings.

Disk

• When Postgres writes data to disk: Without specific information about the workload, there is no reason to change the default settings in this area.
• How much Postgres writes to disk besides database content: Intensive logging is often blamed for performance issues on database systems. Systems which log too little can be difficult to tune and debug, so there is balance to be achieved. For systems which are still being actively tuned, the default logging level is probably too sparse and it is recommended to set log_min_messages to INFO (two levels higher than the default). This logging level can be reduced as the system moves towards a more production ready state.
• How Postgres activity is spread across disks: Postgres produces 4 types of disk activity (index, table, WAL, log). Separates them if the hardware supports it. That is, use different disks/filesystems for each if possible.

Network

• Without specific information on the workload, there is no reason to change these settings. The main reason to change network settings would be for systems which import or export bulk data.