Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have PostgreSQL 9.2 setup on Windows 2008 R2 64bit with 96 gigs of RAM and 8 cores. What would the optimal settings be for shared_buffers, effective_cache_size, work_mem, etc.? I realize that these values vary a lot between Linux and Windows so any help for best practices would be greatly appreciated!

share|improve this question
    
Also how many cores are we talking about on the CPU? –  Chris Travers Feb 3 '13 at 4:19
    
Another important thing: do not run a virus scanner on a DB server! –  a_horse_with_no_name Feb 5 '13 at 11:42

1 Answer 1

In general, the consensus is that shared_buffers is less important to increase on Windows than it is on Linux. Again a lot of this is very workload specific.

I would probably start with a max connections of 20 unless you have a lot of idle connections as the norm. Start with defaults for work_mem and shared_buffers and experiment with tweaking upwards. Always run test queries twice and discard the first one just to ensure comparable caching. work_mem should be tweaked upwards if you have a lot of sorts or aggregates which would use a lot of memory per operation. shared_buffers may need to go higher if you need to reserve more space in memory specifically for PostgreSQL. However assuming you aren't running anything else that is a memory hog on the machine, I would start with default values and see how far those get you. In most setups these should be sufficient for decent performance (occasionally even on Linux, lower shared_buffers can be helpful) and then watch for problems.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.