Skip site navigation (1) Skip section navigation (2)

Re: Postgres configuration for 64 CPUs, 128 GB RAM...

From: Dimitri <dimitrik(dot)fr(at)gmail(dot)com>
To: "Luke Lonergan" <llonergan(at)greenplum(dot)com>
Cc: "Josh Berkus" <josh(at)agliodbs(dot)com>, pgsql-performance(at)postgresql(dot)org, "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de>
Subject: Re: Postgres configuration for 64 CPUs, 128 GB RAM...
Date: 2007-07-30 21:26:03
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

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

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


On 7/22/07, Luke Lonergan <llonergan(at)greenplum(dot)com> wrote:
> Josh,
> On 7/20/07 4:26 PM, "Josh Berkus" <josh(at)agliodbs(dot)com> wrote:
> > There are some specific tuning parameters you need for ZFS or performance
> > is going to suck.
> >
> >
> > (scroll down to "PostgreSQL")
> >
> >
> >
> > 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

In response to

pgsql-performance by date

Next:From: Luke LonerganDate: 2007-07-30 21:41:38
Subject: Re: Postgres configuration for 64 CPUs, 128 GB RAM...
Previous:From: Ron MayerDate: 2007-07-30 20:40:31
Subject: Re: Questions on Tags table schema

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group