I got the new server PowerEdge M605 with 32 GB RAM and CentOS 5 as a dedicated server for Postgresql 9.
After reading a lot, I got to a point in setting up postgresql.conf parameters that I described below:
1. Set a good value for shared_buffers. I prefer to start from 25% of system memory and then go upwards or downwards based on the performance. I set it to 6 GB.
2. Set max_connection according to the requirement. I set it as default that is 100.
3. Start the server and find out how much memory is still available for the OS filesystem cache.
4. Set effective_cache_size based on the result of step 3. I set it to 10 GB.
5. Set work_mem according to the formula: (free + cached memory from the result of free)/(max_connection*2). I set it to 300 MB.
6. Set a good value for maintenance_work_mem for faster data import. I set it to 1 GB.
7. Increase checkpoint_segment. I set it to 100.
8. Increase wal_buffers to 16MB.
9. Increase default_statistics_target. I set it to 200.
10. Set log_min_duration_statement to find out the slow query logs. I set it to 4000, that is 4s.
11. I set log_connection and log_disconnections to true.
12. Set listen_address to '*'.
13. Set random_page_cost and seq_page_cost to 1.0
The server has been running smoothly for the last 3 months.
There were some other important parameters such as wal_sync_method and max_fsm_pages. I did not change those values. Besides, before setting these postgresql.conf values, I set shmall and shmmax according to my earlier post http://airumman.blogspot.com/2011/02/set-unix-shared-memory-parameters-shm.html.
