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

Hi Luke,

On the same page of Solaris internals wiki you may find links to the
study with db_STRESS benchmark (done on UFS and ZFS with PostgreSQL,
MySQL and Oracle (well, Oracle results are removed, but at least I may
say it entered into the same tuning as PgSQL). Tests were done on
Sol10u3 (as well you may find any other platform details in report
document)...

Also, if block size adjustment is less or more transparent (don't
read 32K if you need only 8K - with huge data volume you'll simply
waste your cache; in case you're doing full scan - leave prefetch
algorithm to work for you); probably ARC (cache) limitation need more
light. Well, I even cannot say there is any problem, etc. with it - it
just has too much aggressive implementation :)) If all your running
programs fitting into 1GB of RAM - you may leave ARC size by default
(leaves 1GB free of system RAM). Otherwise, you should limit ARC to
keep your workload execution comfortable: ARC allocating memory very
quickly and every time your program need more RAM - it entering into
concurrency with ARC... In my tests I observed short workload freezes
during such periods and I did not like it too much :)) specially with
high connection numbers :))

well, we may spend hours to discuss :) (sorry to be short, I have a
very limited mail access for the moment)...

However, ZFS is improving all the time and works better and better
with every Solaris release, so probably all current tuning will be
different or obsolete at the end of this year :))

BTW, forgot to mention, you'll need Solaris 10u4 or at least 10u3 but
with all recent patches applied to run M8000 on full power.

Best regards!
-Dimitri

On 7/30/07, Luke Lonergan <LLonergan(at)greenplum(dot)com> wrote:
> Hi Dimitri,
>
> Can you post some experimental evidence that these settings matter?
>
> At this point we have several hundred terabytes of PG databases running on
> ZFS, all of them setting speed records for data warehouses.
>
> We did testing on these settings last year on S10U2, perhaps things have
> changed since then.
>
> - Luke
>
> Msg is shrt cuz m on ma treo
>
> -----Original Message-----
> From: Dimitri [mailto:dimitrik(dot)fr(at)gmail(dot)com]
> Sent: Monday, July 30, 2007 05:26 PM Eastern Standard Time
> To: Luke Lonergan
> Cc: Josh Berkus; pgsql-performance(at)postgresql(dot)org; Marc Mamin
> Subject: Re: [PERFORM] Postgres configuration for 64 CPUs, 128 GB RAM...
>
> 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(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.
> > >
> > > 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
> >
>