parameters in pg_conf

From: "Ram Ravichandran" <ramkaka(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: parameters in pg_conf
Date: 2008-07-08 19:45:07
Message-ID: c8cd6fbb0807081245u216a17ebja85049c933d7f6f@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.

SPECS

This 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 Settings

Looking 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

Browse pgsql-novice by date

  From Date Subject
Next Message Selvakaruppiah s-TLS,Chennai 2008-07-09 06:08:47 [Problem] - Error: cannot open pg_class_oid_index: Permission denied
Previous Message Albe Laurenz 2008-07-08 12:15:31 Re: please explain vacuum with WAL