Postgres Config/Tuning problem - Mailing list pgsql-novice

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.