Re: Postgres Config/Tuning problem

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Andrew Walmsley" <andrew(dot)walmsley(at)ttsltd(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Postgres Config/Tuning problem
Date: 2006-04-11 14:09:43
Message-ID: 7452.1144764583@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

"Andrew Walmsley" <andrew(dot)walmsley(at)ttsltd(dot)com> writes:
> We have what I would call a beast of a machine, 8 * 2.7 GHz Xeon
> processors, with 4 GB of memory, running Red Hat Enterprise (
> 2.6.9-11.ELsmp ) and PostgreSql 7.4.8
> ...
> The processors are all showing 80-95% idle, but vmstat shows 20,000+
> context switches and is dropping processes from the run queue.=20

This sounds a lot like the buffer contention problems that we fixed in
PG 8.1.

However, it might be something simpler than that. Are you vacuuming the
updated table(s) on a regular basis? It sounds like your update process
might involve replacing the entire table contents, in which case you'd
really better vacuum after each such update.

> shared_buffers = 128000 # min 16, at least max_connections*2,

On 7.4 I'd cut that down by a factor of 10, unless you've specifically
measured that the higher number is better for your workload. 7.4 does
not have amazingly good buffer management behavior.

> #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes =

You didn't say much about the total size of your database, but for
almost any production situation, this default is too small.

regards, tom lane

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message george young 2006-04-11 14:42:47 Re: How to append records into a file which has serial
Previous Message John DeSoi 2006-04-11 12:37:18 Re: how to encrypt trigger function