From: Paul Johnson <paul@oxton.com>
To: pgsql-performance@postgresql.org
Subject: PG8 Tuning
Date: Thu, 11 Aug 2005 13:23:21 +0100 (BST)
Message-ID: <1536.217.45.209.171.1123763001.squirrel@www.gradwell.com> (raw)
Hi all, we're running PG8 on a Sun V250 with 8GB RAM and 2*1.3GHz SPARC
CPUs running Solaris 10. The DB cluster is on an external fibre-attached
Sun T3 array that has 9*36GB drives configured as a single RAID5 LUN.
The system is for the sole use of a couple of data warehouse developers,
hence we are keen to use 'aggressive' tuning options to maximise
performance.
So far we have made the following changes and measured the impact on our
test suite:
1) Increase checkpoint_segments from 3 to 64. This made a 10x improvement
in some cases.
2) Increase work_mem from 1,024 to 524,288.
3) Increase shared_buffers from 1,000 to 262,143 (2 GB). This required
setting SHMMAX=4294967295 (4 GB) in /etc/system and re-booting the box.
Question - can Postgres only use 2GB RAM, given that shared_buffers can
only be set as high as 262,143 (8K pages)?
So far so good...
4) Move /pg_xlog to an internal disk within the V250. This has had a
severe *negative* impact on performance. Copy job has gone from 2 mins to
12 mins, simple SQL job gone from 1 min to 7 mins. Not even run long SQL
jobs.
I'm guessing that this is because pg_xlog has gone from a 9 spindle LUN to
a single spindle disk?
In cases such as this, where an external storage array with a hardware
RAID controller is used, the normal advice to separate the data from the
pg_xlog seems to come unstuck, or are we missing something?
Cheers,
Paul Johnson.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: paul@oxton.com
Subject: Re: PG8 Tuning
In-Reply-To: <1536.217.45.209.171.1123763001.squirrel@www.gradwell.com>
References: <1536.217.45.209.171.1123763001.squirrel@www.gradwell.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox