Hi,
I was going through the various performance tweaking documents pointed out by various people on this mailing list last month. I was hoping I could do a quick spot-check, and get your expert opinion.
SPECSThis is for a new web application. Currently I have one quad-core 2.5Ghz xeon 5420, 8GB RAM with 2x 73GB 15K SCSI drives in a battery-backed RAID 1. My target DB size will be around 10GB. The server hosts postgresql + application server (Rails) + web server (NGinx) + MemcacheD . I expect to see really high usage for about 5 - 7 hours a day (same time every day).
From benchmarking with test data (that I am not sure will reflect real life scenario), the application server + nginx demands about 2GB of memory. So I think I will have about 4 - 5 GB of working memory for Postgresql + OS. My FS is ext3 with noatime and writeback cache enabled. My understanding is that writeback is okay since I have a battery-backed raid controller.
CONF SettingsLooking through [1 - 4], I set the following non-default parameters in my conf file:
---------------
max_connections = 50
shared_buffers = 1GB # 25% of 4GB remaining memory
work_mem = 2MB
maintenance_work_mem = 128MB
checkpoint_segments = 8
wal_buffers = 1MB
effective_cache_size = 2GB
random_page_cost = 2.5
----------------
Questions:1. Should I turn autovacuum off and run them nightly since I have a peak usage for a few hours a day?
2. Since I have a battery-backed raid controller, is it safe to turn fsync off? Is this done in practice? What is the average increase in performance?
3. [1] says "cpu_* = multiply all by .2" I am not sure what that means. Does that mean that I should set them to 0.2 or multiply the defaults by 0.2?
Any suggestions given my current setup?
4. Does the random_page_cost of 2.5 seem reasonable for my setup?
Thanks a lot.
Ram
References[1] "Performance Whack-a-Mole" Tutorial PGCon 2007. Presentation
[2] http://www.powerpostgresql.com/PerfList
[3] http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm
[4] http://www.revsys.com/writings/postgresql-performance.html