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@xxxxxxxxxxxxx> wrote: > Josh, > > On 7/20/07 4:26 PM, "Josh Berkus" <josh@xxxxxxxxxxxx> 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 > ---------------------------(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
