Postgres Config/Tuning problem

From: "Andrew Walmsley" <andrew(dot)walmsley(at)ttsltd(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Postgres Config/Tuning problem
Date: 2006-04-11 09:12:20
Message-ID: B67C1491364AE5468606C90374E790F31C0DCC@cerberus.preston.traveltech.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message John DeSoi 2006-04-11 12:37:18 Re: how to encrypt trigger function
Previous Message Christoph Della Valle 2006-04-11 05:42:35 Re: PostgreSQL a slow DB?