Re: Postgres Config/Tuning problem

Hello all.

((Apologies if this appears twice, I sent it two days ago and it doesn't
appear to have turned up on the list, so I chopped a lot of the tech
stuff in case it was a length of email issue))

I have a problem with what I _think_ is PSQL tuning. I am _not_ a
postgres expert and have inherited the current settings which to me seem
wrong.

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 database acts as a front end cache of data held elsewhere. At the
start of day all the tables are dropped and recreated. As the day goes
on, an update file is sent at regular intervals to update the table with
the latest information. The update file is cumulative, slowly increasing
in size ( up to 1876183 bytes) throughout the day. The problem is that
the machine starts to grind to a halt processing the update files. The
database is not that big, the important tables have only 198,000 rows.
The processors are all showing 80-95% idle, but vmstat shows 20,000+
context switches and is dropping processes from the run queue.

From what I understand this machine should not be floored by such a
trivial task.

So, the question is, can someone look at these settings and give an
opinion on whether they are approximately right or need serious
tweaking.

--From postgresql.conf---

#-----------------------------------------------------------------------
----

# RESOURCE USAGE (except WAL)

#-----------------------------------------------------------------------
----

# - Memory -

shared_buffers = 128000 # min 16, at least max_connections*2,
8KB each

sort_mem = 8192 # min 64, size in KB

vacuum_mem = 65536 # min 1024, size in KB

# - Free Space Map -

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

#max_fsm_relations = 1000 # min 100, ~50 bytes each

# - Kernel Resource Usage -

#max_files_per_process = 1000 # min 25

#preload_libraries = ''

#-----------------------------------------------------------------------
----

# WRITE AHEAD LOG

#-----------------------------------------------------------------------
----

# - Settings -

#fsync = true # turns forced synchronization on or off

#wal_sync_method = fsync # the default varies across platforms:

# fsync, fdatasync, open_sync, or
open_datasync

wal_buffers = 64 # min 4, 8KB each

# - Checkpoints -

#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each

#checkpoint_timeout = 300 # range 30-3600, in seconds

#checkpoint_warning = 30 # 0 is off, in seconds

#commit_delay = 0 # range 0-100000, in microseconds

#commit_siblings = 5 # range 1-1000

--

Andrew Walmsley

Software Development

Travel Technology Systems Ltd.

2 Nile Close, Riversway

Preston, PR2 2XU

Tel: 01772 766800

Fax: 01772 766801

(please note the new address and contact number)

This Email may contain information of a confidential and/or privileged
nature.

The information transmitted is intended only for the benefit of the
person or entity to which it is addressed and must not be copied or
forwarded without the sender's express permission.

This Email does not reflect the views or opinions of Travel Technology
Systems Ltd.

This Email is without prejudice.

This Email does not constitute an agreement either explicitly or
implicitly with Travel Technology Systems Ltd.

"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