Difference between revisions of "Tuning the PostgreSQL Database"

Revision as of 12:25, 5 December 2017

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;

Creating a ramdisk of the stats table

mkdir -p /db/ramdisk
chown postgres:postgres /db/ramdisk
chmod 777 /db/ramdisk
chmod +t /db/ramdisk

#in /etc/fstab add
tmpfs   /db/ramdisk     tmpfs   defaults,noatime,nosuid,nodev,size=30M  0       0
#In postgresql.conf, add that, and then reload it:
stats_temp_directory = '/db/ramdisk'
#Restart server