Date: Wed, 9 Jan 2013 21:03:08 -0500 From: Charles Sprickman <spork@bway.net> To: Artem Belevich <art@freebsd.org> Cc: Patrick Dung <patrick_dkt@yahoo.com.hk>, freebsd-fs <freebsd-fs@freebsd.org> Subject: Re: ZFS sub-optimal performance with default setting Message-ID: <76DE1383-BAAA-4EFD-ABA6-A9328D79D5B3@bway.net> In-Reply-To: <CAFqOu6gE2C1FoqZWVKKcw9=sKYagrwT5LpVo-jipbxTyLmjFVA@mail.gmail.com> References: <1357741879.56011.YahooMailClassic@web190806.mail.sg3.yahoo.com> <CAFqOu6gE2C1FoqZWVKKcw9=sKYagrwT5LpVo-jipbxTyLmjFVA@mail.gmail.com>
next in thread | previous in thread | raw e-mail | index | archive | help
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> wrote: >> 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. 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 = write 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, = tell PG how much it has left after ARC plus some slop with = "effective_cache_size" (for example, if you have 64GB of RAM, maybe = limit ARC to 32GB, then set 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 it, you're almost guaranteed to have lousy performance. Lastly, this book is amazing - there's lots of general information = that's quite 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 = survive power-loss without corruption), and make a mirrored ZIL for zfs. = That 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?76DE1383-BAAA-4EFD-ABA6-A9328D79D5B3>