Thursday, March 3, 2011

Postgresql parameters for a new dedicated server


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

Leave a Reply