How can we tune Postgres database for Windows box with 16GB Memory?

Products

• CA Application Performance Management Agent (APM / Wily / Introscope)
• INTROSCOPE

Issue/Introduction

  We have seen many times that database was undersized or not properly configured. Default deployment of database is using only small amount of RAM for caching underlying data (1GB). So even if users have big machines with 64GB of memory, it is not used. One should do initially health check seeing if database is configured properly.

How can we tune Postgres database for Windows box with 16GB Memory?

Environment

10.5.1.x and 10.5.2.x

Resolution

Postgres tuning example for Windows box with 16GB memory (Postgres configured to consume 8GB):

postgresql.conf
# The shared_buffers configuration parameter determines how much memory is dedicated to PostgreSQL to use for caching data.
shared_buffers = 512MB
# effective_cache_size should be set to an estimate of how much memory is available for disk caching by the operating
# system and within the database itself, after taking into account what's used by the OS itself and other applications
effective_cache_size = 6GB
# Increasing the work_mem parameter allows PostgreSQL to do larger in-memory sorts which, unsurprisingly, will be faster than disk-based equivalents.
work_mem = 64MB
# Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.
maintenance_work_mem = 512MB

Additional Information

- Tuning Your PostgreSQL Server.
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

- How to purge or reduce the size of a Postgresql APM database and optimize CEM data retention.
https://support.ca.com/us/knowledge-base-articles.TEC597519.html

- APM database is growing fast - too many appmap_states tables.
https://support.ca.com/us/knowledge-base-articles.TEC1846866.html