From owner-freebsd-fs@FreeBSD.ORG Thu Jan 10 02:11:57 2013 Return-Path: Delivered-To: freebsd-fs@freebsd.org Received: from mx1.freebsd.org (mx1.FreeBSD.org [8.8.178.115]) by hub.freebsd.org (Postfix) with ESMTP id 271E3572; Thu, 10 Jan 2013 02:11:57 +0000 (UTC) (envelope-from spork@bway.net) Received: from smtp1.bway.net (smtp1.bway.net [216.220.96.27]) by mx1.freebsd.org (Postfix) with ESMTP id A9409DB3; Thu, 10 Jan 2013 02:11:56 +0000 (UTC) Received: from frankentosh.sporklab.com (foon.sporktines.com [96.57.144.66]) (using TLSv1 with cipher AES128-SHA (128/128 bits)) (No client certificate requested) (Authenticated sender: spork@bway.net) by smtp1.bway.net (Postfix) with ESMTPSA id 1FD8295868; Wed, 9 Jan 2013 21:03:09 -0500 (EST) Subject: Re: ZFS sub-optimal performance with default setting Mime-Version: 1.0 (Apple Message framework v1085) Content-Type: text/plain; charset=us-ascii From: Charles Sprickman In-Reply-To: Date: Wed, 9 Jan 2013 21:03:08 -0500 Content-Transfer-Encoding: quoted-printable Message-Id: <76DE1383-BAAA-4EFD-ABA6-A9328D79D5B3@bway.net> References: <1357741879.56011.YahooMailClassic@web190806.mail.sg3.yahoo.com> To: Artem Belevich X-Mailer: Apple Mail (2.1085) Cc: Patrick Dung , freebsd-fs X-BeenThere: freebsd-fs@freebsd.org X-Mailman-Version: 2.1.14 Precedence: list List-Id: Filesystems List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 10 Jan 2013 02:11:57 -0000 On Jan 9, 2013, at 8:37 PM, Artem Belevich wrote: > On Wed, Jan 9, 2013 at 6:31 AM, Patrick Dung = 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"