Tuning the PostgreSQL Database
From Observer GigaFlow Support | VIAVI Solutions Inc.
Revision as of 10:02, 23 November 2017 by Kevin Wilkie (Talk | contribs)
Tuning of Postgres is done via the postgresql.conf file.
- Make a copy of the original file should changes to the file cause any problems (allowing you to revert)
- Edit file as appropriate and save
- Restart Postgres
- Restart applications requiring Postgres
Settings based on available RAM
shared_buffers=1/4 of the available memory
effective_cache_size = 1/2 of available memory
Standard Settings
effective_cache_size = 2GB shared_buffers = 1000MB autovacuum_max_workers = 1 checkpoint_completion_target = 0.9 checkpoint_segments = 32 log_min_duration_statement = 5000 synchronous_commit = off work_mem = 10MB wal_buffers = 10MB commit_delay = 10 max_locks_per_transaction = 3000 max_wal_size = 2000MB max_connections = 150 maintenance_work_mem = 64MB
Example for a server with 16GB of RAM
effective_cache_size = 8GB shared_buffers = 4000MB autovacuum_max_workers = 1 checkpoint_completion_target = 0.9 checkpoint_segments = 32 log_min_duration_statement = 5000 synchronous_commit = off work_mem = 10MB wal_buffers = 10MB commit_delay = 10 max_locks_per_transaction = 3000 max_wal_size = 2000MB max_connections = 150 maintenance_work_mem = 128MB
Housekeeping
vacuum pg_depend;vacuum full pg_constraint;vacuum full pg_inherits;