Re: Config help - Mailing list pgsql-general

On Sun, Nov 15, 2009 at 2:43 PM, BuyAndRead Test <test@buyandread.com> wrote:
> Hi
>
> I need some help with our postgresql.conf file. I would appreciate if
> someone could look at the values and tell me if it looks alright or if I
> need to change anything.
>
> The db server has 4 GB of memory and one quad core CPU (2,53 GHz).
> The hard drives is on a iSCSI array and is configured as follows:
> DB data: 4 x SAS (10.000 rpm) disks in RAID 10
> DB WAL: 2 x SAS (10.000 rpm) disks in RAID 1

Is there a battery backed cache in there somewhere?  That would help
on handling high write loads.

> OS: Linux (Debian Lenny)
> DB: PostgreSQL 8.4
>
> The DB is used by a website. It has 75 tables and about a total of 10 mill
> rows. The total size of the DB data (data+indexes?) is reported to be about
> 4 GB when I use the \l+ command in version 8.4.

The cheapest performance boost would be more memory.  Going to 8Gigs
would let the whole db get cached and leave enough memory over for
sorts and OS etc.

> I have used the following link as a guide:
> http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
> This is the changes I have done to the default postgresql.conf file:
> shared_buffers = 2048MB

A little high for a machine with only 4G ram.  With 8 G if you
allocate 4G for share_buffers you'd leave 4G for OS and pg.  Here
you're only leaving 2G.

> work_mem = 6MB

Depending on your workload it might be better to raise this and lower
shared_buffers.

> wal_buffers = 256kB
> checkpoint_segments = 20
> random_page_cost = 3.0
> default_statistics_target = 50

The new default is 100, I'd tend to stick with that unless you have
very uniform data.

> Should I change the default value of temp_buffers or maintenance_work_mem as
> well, and what value should I choose? Is there any other values that should
> be changed from the default?

Always consider cranking up maint work mem because not many things use
it and the things that do can really use it.

> And another question: Is there a way to find out the maximum simultaneous
> connections that has been used? I think that I could reduce the max number
> of connection to save some memory.

You'd really need to track that yourself with some kind of simple
script.   (bash)

while true; do psql mydb -c "select count(*) from pg_stat_activity"
;sleep 60;done | tee myconn.log

or something like that.