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

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