Message-ID: <42FB4ABB.1010305@archonet.com>
Date: Thu, 11 Aug 2005 13:55:23 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
MIME-Version: 1.0
To: paul@oxton.com
Cc: pgsql-performance@postgresql.org
Subject: Re: PG8 Tuning
References: <1536.217.45.209.171.1123763001.squirrel@www.gradwell.com>
In-Reply-To: <1536.217.45.209.171.1123763001.squirrel@www.gradwell.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Paul Johnson wrote:
> 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.

OK

> 2) Increase work_mem from 1,024 to 524,288.

Don't forget you can use multiples of this in a single query. Might want 
to reign it back a bit. I *think* you can set it per-query if you want 
anyway.

> 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)?

Well, normally you'd want to keep a fair bit for the O.S. to cache data. 
One quarter of your RAM seems very high. Did you try 5000,10000,50000 
too or go straight to the top end?

> 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?

The key limitation will be one commit per rotation of the disk. Multiple 
spindles, or better still with a battery-backed write-cache will give 
you peak transactions.

> 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?

Well, I think the advice then is actually "get 2 external arrays..."

--
   Richard Huxton
   Archonet Ltd