User-Agent: Microsoft-Entourage/11.3.6.070618
Date: Sun, 22 Jul 2007 09:43:47 -0700
Subject: Re: Postgres configuration for 64 CPUs, 128 GB RAM...
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Josh Berkus" <josh@agliodbs.com>,
	pgsql-performance@postgresql.org
cc: "Marc Mamin" <M.Mamin@intershop.de>
Message-ID: <C2C8D753.39643%llonergan@greenplum.com>
Thread-Topic: [PERFORM] Postgres configuration for 64 CPUs, 128 GB
 RAM...
Thread-Index: AcfMf3k4t7tYYDhyEdyXawAbY5GgAQ==
In-Reply-To: <200707201626.08281.josh@agliodbs.com>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

Josh,

On 7/20/07 4:26 PM, "Josh Berkus" <josh@agliodbs.com> wrote:

> There are some specific tuning parameters you need for ZFS or performance
> is going to suck.
> 
> http://www.solarisinternals.com/wiki/index.php/ZFS_Best_Practices_Guide
> (scroll down to "PostgreSQL")
> http://www.sun.com/servers/coolthreads/tnb/applications_postgresql.jsp
> http://bugs.opensolaris.org/view_bug.do?bug_id=6437054
> 
> You also don't say anything about what kind of workload you're running.


I think we're assuming that the workload is OLTP when putting these tuning
guidelines forward.  Note that the ZFS tuning guidance referred to in this
bug article recommend "turning vdev prefetching off" for "random I/O
(databases)".  This is exactly the opposite of what we should do for OLAP
workloads.

Also, the lore that setting recordsize on ZFS is mandatory for good database
performance is similarly not appropriate for OLAP work.

If the workload is OLAP / Data Warehousing, I'd suggest ignoring all of the
tuning information from Sun that refers generically to "database".  The
untuned ZFS performance should be far better in those cases.  Specifically,
these three should be ignored:
- (ignore this) limit ARC memory use
- (ignore this) set recordsize to 8K
- (ignore this) turn off vdev prefetch

- Luke