[PERFORM] PostgreSQL 9.3.2 Performance issues

[fburgess]

We have 64GB of Memory on RHEL 6.4

shared_buffers = 8GB
work_mem = 64MB
maintenance_work_mem = 1GB
effective_cache_size = 48GB

I found this list of recommended parameters for memory management in PostgreSQL.

About shared_buffers:

• Below 2GB, set to 20% of total system memory.
• Below 32GB, set to 25% of total system memory.
• Above 32GB, set to 8GB

About work_mem, this parameter can cause a huge speed-up if set properly, however it can use that amount of memory per planning node.
Here are some recommendations to set it up.

• Start low: 32-64MB
• Look for ‘temporary file’ lines in logs
• Set to 2-3x the largest temp file

About maintenance_work_mem, some recommendations were:

• 10% of system memory, up to1GB
• Maybe even higher if you are having VACUUM problems

About effective_cache_size, guidelines suggested.

• Set to the amount of file system cache available
• If you don’t know, set it to 50% of total system memory

We have real time 24/7 data ingest processes running on our 9.3.2 database 7TB in size

Do these settings look correct for 9.3.2?

thanks