From owner-freebsd-fs@FreeBSD.ORG Wed Jan 9 15:00:05 2013 Return-Path: Delivered-To: freebsd-fs@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:1900:2254:206a::19:1]) by hub.freebsd.org (Postfix) with ESMTP id 1F136ED for ; Wed, 9 Jan 2013 15:00:05 +0000 (UTC) (envelope-from ronald-freebsd8@klop.yi.org) Received: from smarthost1.greenhost.nl (smarthost1.greenhost.nl [195.190.28.78]) by mx1.freebsd.org (Postfix) with ESMTP id ADA3B69F for ; Wed, 9 Jan 2013 15:00:04 +0000 (UTC) Received: from smtp.greenhost.nl ([213.108.104.138]) by smarthost1.greenhost.nl with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:32) (Exim 4.69) (envelope-from ) id 1Tsx8F-0000jP-69 for freebsd-fs@freebsd.org; Wed, 09 Jan 2013 15:59:56 +0100 Received: from [81.21.138.17] (helo=ronaldradial.versatec.local) by smtp.greenhost.nl with esmtpsa (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:32) (Exim 4.72) (envelope-from ) id 1Tsx8F-00038g-By for freebsd-fs@freebsd.org; Wed, 09 Jan 2013 15:59:55 +0100 Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes To: freebsd-fs@freebsd.org Subject: Re: ZFS sub-optimal performance with default setting References: <1357741879.56011.YahooMailClassic@web190806.mail.sg3.yahoo.com> Date: Wed, 09 Jan 2013 15:59:56 +0100 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit From: "Ronald Klop" Message-ID: In-Reply-To: <1357741879.56011.YahooMailClassic@web190806.mail.sg3.yahoo.com> User-Agent: Opera Mail/12.12 (Win32) X-Virus-Scanned: by clamav at smarthost1.samage.net X-Spam-Level: / X-Spam-Score: 0.8 X-Spam-Status: No, score=0.8 required=5.0 tests=BAYES_50 autolearn=disabled version=3.3.1 X-Scan-Signature: d58e29c6f4e42f76447094ef3ccb23d2 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: Wed, 09 Jan 2013 15:00:05 -0000 On Wed, 09 Jan 2013 15:31:19 +0100, Patrick Dung wrote: > Hi freebsd-fs! > > 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. > > Details: > > Postgresql 9.2.2 (complied by gcc) is installed in FreeBSD 9.1 i386. > The pgsql base directory is in a ZFS dataset. > > I have noticed the performance is sub-optimal, but I know the default > setting should be the most safest one to be use (concern about data > integrity). > > a) I use OTRS ticketing system version 3.1, the backend is PostgreSQL. > The user interactive response is not slow (switching web pages or create > a change). > > b) There is a benchmark in the support module of OTRS. > It tested insert,update,select and delete performance. > The response time is slow (>10 sec), except select. > > I have done some research on web, with below settings (just one change, > not both), the performance returned to normal: > > 1) Disabled sync in the pgsql dataset in ZFS > zfs set sync=disabled mydata/pgsql > or > 2) In > postgresql.conf, set synchronous_commit from on to off > > I know the above setting would lead to data loss (e.g.power goes off), > any comments? > > PS: > 1) I have tried to use primarycache/secondarycache=metadata/none, it do > not seem to help. > > 2) > I have tried the default setting on Linux too: > RHEL 6.3, ext4, stock postgresql 8.x, OTRS 3.1. > The web site is responsive and the benchmark result is more or less the > same as FreeBSD with the 'sync' turned off. > > 3) > For FreeBSD, same setting with Postgresql on UFS: > The performance is between ZFS (default, sync enabled) and ZFS (sync > disabled). > > Thanks, > Patrick As you might have read on the internet ZFS does some kind of journaling and postgresql also. Postgresql expects the FS to not do the journaling (because it also works on ext2, ffs, etc.) so you can disable it for ZFS and postgresql will make sure the data is consistent. Ronald.