Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group