Date: Fri, 11 Jan 2013 00:54:22 +0800 (SGT) From: Patrick Dung <patrick_dkt@yahoo.com.hk> To: Artem Belevich <art@freebsd.org>, Charles Sprickman <spork@bway.net> Cc: freebsd-fs <freebsd-fs@freebsd.org> Subject: Re: ZFS sub-optimal performance with default setting Message-ID: <1357836862.81267.YahooMailClassic@web190806.mail.sg3.yahoo.com> In-Reply-To: <76DE1383-BAAA-4EFD-ABA6-A9328D79D5B3@bway.net>
next in thread | previous in thread | raw e-mail | index | archive | help
I have tried some tests, good and bad result is in below.......... I am sure there is some bottleneck, and the root cause is still unknown. logbias=3Dthroughput, primarycache=3Dall, sync=3Dstandard Result: SQL Key =A0=A0 =A0Value =A0=A0 =A0Time =A0=A0 =A0Comment Insert Time: =A0=A0 =A010000 =A0=A0 =A028 s :-( =A0=A0 =A0Should not take m= ore than 5's on an average system. Update Time: =A0=A0 =A010000 =A0=A0 =A029 s :-( =A0=A0 =A0Should not take m= ore than 9's on an average system. Select Time: =A0=A0 =A010000 =A0=A0 =A08 s :-( =A0=A0 =A0Should not take mo= re than 6's on an average system. Delete Time: =A0=A0 =A010000 =A0=A0 =A024 s :-( =A0=A0 =A0Should not take m= ore than 5's on an average system.=20 logbias=3Dlatency, primarycache=3Dall,=0A sync=3Dstandard Result: SQL Key =A0=A0 =A0Value =A0=A0 =A0Time =A0=A0 =A0Comment Insert Time: =A0=A0 =A010000 =A0=A0 =A09 s :-( =A0=A0 =A0Should not take mo= re than 5's on an average system. Update Time: =A0=A0 =A010000 =A0=A0 =A010 s :-( =A0=A0 =A0Should not take m= ore than 9's on an average system. Select Time: =A0=A0 =A010000 =A0=A0 =A04 s :-) =A0=A0 =A0Looks fine! Delete Time: =A0=A0 =A010000 =A0=A0 =A08 s :-( =A0=A0 =A0Should not take mo= re than 5's on an average system.=20 logbias=3Dlatency, primarycache=3Dall, sync=3Ddisabled Result: SQL Key =A0=A0 =A0Value =A0=A0 =A0Time =A0=A0 =A0Comment Insert Time: =A0=A0 =A010000 =A0=A0 =A03 s :-) =A0=A0 =A0Looks fine! Update Time: =A0=A0 =A010000 =A0=A0 =A03 s :-) =A0=A0=0A =A0Looks fine! Select Time: =A0=A0 =A010000 =A0=A0 =A03 s :-) =A0=A0 =A0Looks fine! Delete Time: =A0=A0 =A010000 =A0=A0 =A03 s :-) =A0=A0 =A0Looks fine!=20 Thanks, Patrick --- On Thu, 1/10/13, Charles Sprickman <spork@bway.net> wrote: From: Charles Sprickman <spork@bway.net> Subject: Re: ZFS sub-optimal performance with default setting To: "Artem Belevich" <art@freebsd.org> Cc: "Patrick Dung" <patrick_dkt@yahoo.com.hk>, "freebsd-fs" <freebsd-fs@fre= ebsd.org> Date: Thursday, January 10, 2013, 10:03 AM On Jan 9, 2013, at 8:37 PM, Artem Belevich wrote: > On Wed, Jan 9, 2013 at 6:31 AM, Patrick Dung <patrick_dkt@yahoo.com.hk> w= rote: >> Hi freebsd-fs! >>=20 >> I have my the original question in: >> http://archives.postgresql.org/pgsql-performance/2013-01/msg00044.php >> But later it was found out the bottleneck seems to be the ZFS with out a= fast ZIL. >> Please give some advise, thanks. >=20 > For database storage on ZFS it may be necessary to change ZFS record > size to match database page size. At least that's what one of the > things Oracle recommends for oracle database: > http://www.oracle.com/technetwork/server-storage/solaris10/config-solaris= -zfs-wp-167894.pdf >=20 > You may also check if disabling prefetching (via > vfs.zfs.prefetch_disable=3D1 tunable in loader.conf) helps your > workload. PostgreSQL has a ton of tunables, and the performance list is a good place = to start.=A0 The archives there are full of information on zfs. A few things off the top of my head: -set recordsize to 8k on the pg dataset (this has to be done before you wri= te data) -set "full_page_writes =3D off" (safe on zfs, not necessarily so on ufs) -leave RAM available for PG by limiting the max ARC size in loader.conf, te= ll PG how much it has left after ARC plus some slop with "effective_cache_s= ize" (for example, if you have 64GB of RAM, maybe limit ARC to 32GB, then s= et effective_cache_size to 30GB or so) -turn off atime updates As far as general PG setup, pgtune will put you in a better place than the = default config: https://github.com/gregs1104/pgtune Actually the stock config file is really terrible, if you haven't touched i= t, you're almost guaranteed to have lousy performance. Lastly, this book is amazing - there's lots of general information that's q= uite useful outside of PG and general db tuning: http://www.2ndquadrant.com/en/postgresql-90-high-performance/ If you have $200 or so laying around, slap in two Intel 320 SSDs (these sur= vive power-loss without corruption), and make a mirrored ZIL for zfs.=A0 Th= at will up your TPS on write-heavy loads into at least the 20K realm. Charles >=20 > --Artem > _______________________________________________ > freebsd-fs@freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-fs > To unsubscribe, send any mail to "freebsd-fs-unsubscribe@freebsd.org"
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?1357836862.81267.YahooMailClassic>