DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=V+mVzQObRsrAQvxcYReyDijoB44a1YDM9r0iIdZpTq5pxBwerm8fdCcuY+8qwrSGE7MuJFWjywT99+MxKZJ/XzBjcfGQwdGSY1NGvneItSz+UN9KLBxdLe3VwMgrhbXWOHWF/0ADyURyk+0fU2PRdIaMBufwvn/MuKeYZjaNZxw=
Message-ID: <5482c80a0707301426o3103541fs190080fc5a572afa@mail.gmail.com>
Date: Mon, 30 Jul 2007 23:26:03 +0200
From: Dimitri <dimitrik.fr@gmail.com>
To: "Luke Lonergan" <llonergan@greenplum.com>
Subject: Re: Postgres configuration for 64 CPUs, 128 GB RAM...
Cc: "Josh Berkus" <josh@agliodbs.com>, pgsql-performance@postgresql.org,
	"Marc Mamin" <M.Mamin@intershop.de>
In-Reply-To: <C2C8D753.39643%llonergan@greenplum.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <200707201626.08281.josh@agliodbs.com>
	<C2C8D753.39643%llonergan@greenplum.com>

Luke,

ZFS tuning is not coming from general suggestion ideas, but from real
practice...

So,
  - limit ARC is the MUST for the moment to keep your database running
comfortable (specially DWH!)
  - 8K blocksize is chosen to read exactly one page when PG ask to
read one page - don't mix it with prefetch! when prefetch is detected,
ZFS will read next blocks without any demand from PG; but otherwise
why you need to read more  pages each time PG asking only one?...
  - prefetch of course not needed for OLTP, but helps on OLAP/DWH, agree :)

Rgds,
-Dimitri


On 7/22/07, Luke Lonergan <llonergan@greenplum.com> wrote:
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>