[no subject]

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.



--Boundary_(ID_EpePzI99TlR0Dh/+EexxvA)
Content-type: message/rfc822; name="[PERFORM] PG8 Tuning"

Return-path: <pgsql-performance-owner+M13863(at)postgresql(dot)org>
Received: from conversion-daemon.bur-mail1.east.sun.com by
 bur-mail1.east.sun.com
 (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003))
 id <0IL200C015ME3X(at)bur-mail1(dot)east(dot)sun(dot)com>
 (original mail from pgsql-performance-owner+M13863(at)postgresql(dot)org)
 for djc(at)bur-mail1(dot)East(dot)Sun(dot)COM; Thu, 11 Aug 2005 08:38:53 -0400 (EDT)
Received: from eastmail2bur.East.Sun.COM
 (eastmail2bur.East.Sun.COM [129.148.13.40]) by bur-mail1.east.sun.com
 (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003))
 with ESMTP id <0IL200MH15STJS(at)bur-mail1(dot)east(dot)sun(dot)com> for
 djc(at)bur-mail1(dot)East(dot)Sun(dot)COM (ORCPT donald(dot)courtney(at)sun(dot)com); Thu,
 11 Aug 2005 08:38:53 -0400 (EDT)
Received: from sunmail1brm.Central.Sun.COM
 (sunmail1brm.Central.Sun.COM [129.147.62.17])	by eastmail2bur.East.Sun.COM
 (8.12.10+Sun/8.12.10/ENSMAIL,v2.2) with ESMTP id j7BCcrlM025431	for
 <donald(dot)courtney(at)east(dot)sun(dot)com>; Thu, 11 Aug 2005 08:38:53 -0400 (EDT)
Received: from nwkea-mail-2.sun.com (nwkea-mail-2.Sun.COM [192.18.42.14])
	by sunmail1brm.Central.Sun.COM (8.11.7p1+Sun/8.11.7/ENSMAIL,v2.2)
 with ESMTP id j7BCcpb07056	for <donald(dot)courtney(at)sun(dot)com>; Thu,
 11 Aug 2005 06:38:52 -0600 (MDT)
Received: from relay23.sun.com
 (relay23.sun.com [192.12.251.54] (may be forged))	by nwkea-mail-2.sun.com
 (8.12.10/8.12.9) with ESMTP id j7BCcp1d027727	for <donald(dot)courtney(at)sun(dot)com>;
 Thu, 11 Aug 2005 05:38:51 -0700 (PDT)
Received: from mms21es.sun.com (mms21es.sun.com [150.143.232.14])
 by relay23.sun.com with ESMTP for donald(dot)courtney(at)sun(dot)com; Thu,
 11 Aug 2005 12:38:51 +0000 (Z)
Received: from relay21.sun.com (relay21.sun.com [192.12.251.14])
 by mms21es.sun.com with ESMTP for donald(dot)courtney(at)sun(dot)com; Thu,
 11 Aug 2005 12:38:50 +0000 (Z)
Received: from hosting.commandprompt.com ([207.173.200.128] [207.173.200.128])
 by relay21.sun.com with ESMTP for donald(dot)courtney(at)sun(dot)com; Thu,
 11 Aug 2005 12:38:50 +0000 (Z)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by hosting.commandprompt.com (8.13.4/8.13.4) with ESMTP id j7BCPn0Q014340;
 Thu, 11 Aug 2005 05:26:04 -0700
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id BCF1752A19	for
 <pgsql-performance-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>; Thu,
 11 Aug 2005 09:23:24 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
 by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
 with ESMTP id 74763-06 for
 <pgsql-performance-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>; Thu,
 11 Aug 2005 12:23:20 +0000 (GMT)
Received: from lon-mail-2.gradwell.net
 (lon-mail-2.gradwell.net [193.111.201.126])	by svr1.postgresql.org (Postfix)
 with ESMTP id 5166052A06	for <pgsql-performance(at)postgresql(dot)org>; Thu,
 11 Aug 2005 09:23:19 -0300 (ADT)
Received: from www.gradwell.com ([193.111.200.100]) by lon-mail-2.gradwell.net
 with smtp (Gradwell gwh-smtpd 1.190) id 42fb4339.14d23.f; Thu,
 11 Aug 2005 13:23:21 +0100 (envelope-sender <paul(at)oxton(dot)com>)
Received: from 217.45.209.171
 (SquirrelMail authenticated user paul(at)pop3(dot)oxton(dot)com)
 by www.gradwell.com with HTTP; Thu, 11 Aug 2005 13:23:21 +0100 (BST)
Date: Thu, 11 Aug 2005 13:23:21 +0100 (BST)
From: Paul Johnson <paul(at)oxton(dot)com>
Subject: [PERFORM] PG8 Tuning
Sender: pgsql-performance-owner(at)postgresql(dot)org
To: pgsql-performance(at)postgresql(dot)org
Reply-to: paul(at)oxton(dot)com
Message-id: <1536(dot)217(dot)45(dot)209(dot)171(dot)1123763001(dot)squirrel(at)www(dot)gradwell(dot)com>
MIME-version: 1.0
Content-type: text/plain; charset=iso-8859-1
Content-transfer-encoding: 8BIT
Importance: Normal
X-Priority: 3
Precedence: bulk
User-Agent: SquirrelMail/1.4.2
X-Original-To: pgsql-performance-postgresql(dot)org(at)localhost(dot)postgresql(dot)org
X-Virus-Scanned: by amavisd-new at hub.org
X-Greylist: Sender IP whitelisted,
 not delayed by milter-greylist-1.6 (hosting.commandprompt.com
 [192.168.1.101]); Thu, 11 Aug 2005 05:26:08 -0700 (PDT)
X-Mailing-List: pgsql-performance
List-Owner: <mailto:pgsql-performance-owner(at)postgresql(dot)org>
List-Post: <mailto:pgsql-performance(at)postgresql(dot)org>
List-Subscribe: <mailto:majordomo(at)postgresql(dot)org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo(at)postgresql(dot)org?body=unsub%20pgsql-performance>
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo(at)postgresql(dot)org?body=help>
List-Id: <pgsql-performance.postgresql.org>
Original-recipient: rfc822;donald(dot)courtney(at)sun(dot)com

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

--Boundary_(ID_EpePzI99TlR0Dh/+EexxvA)--