From owner-freebsd-fs@FreeBSD.ORG Thu Jan 10 16:39:10 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 02FC36B5 for ; Thu, 10 Jan 2013 16:39:10 +0000 (UTC) (envelope-from patrick_dkt@yahoo.com.hk) Received: from nm31.bullet.mail.sg3.yahoo.com (nm31.bullet.mail.sg3.yahoo.com [106.10.151.26]) by mx1.freebsd.org (Postfix) with ESMTP id 4B56E884 for ; Thu, 10 Jan 2013 16:39:08 +0000 (UTC) Received: from [106.10.166.61] by nm31.bullet.mail.sg3.yahoo.com with NNFMP; 10 Jan 2013 16:39:07 -0000 Received: from [106.10.151.234] by tm18.bullet.mail.sg3.yahoo.com with NNFMP; 10 Jan 2013 16:39:07 -0000 Received: from [127.0.0.1] by omp1018.mail.sg3.yahoo.com with NNFMP; 10 Jan 2013 16:39:07 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 360600.65498.bm@omp1018.mail.sg3.yahoo.com Received: (qmail 90375 invoked by uid 60001); 10 Jan 2013 16:39:07 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com.hk; s=s1024; t=1357835947; bh=E0ialZj6ymbqngvJSaPH1ZS8SL3zYxT1Y8JrCXX1yG0=; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:Message-ID:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=CAKWb9TRa4oWkqYx9TNWBMI/H/flfj0esh5c6HaXI1Z+Kk2ylAnsO9HUgAQY8tJw/EVLLNbYsvA5J4XOlNti+wEvlz1HeS0VOF5st+NpoYf0WCETYgVovxwEmU7HwuNykLM/LlyuZHbthVgMA4pP5mJn07EiUUJuog4ZuYUMUR4= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.hk; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:Message-ID:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=PS6sDnUS8mxcysE5az9uKqu/qCHVYtYO09HhSW/3tzX3vVqFM0c5R/+28PVGVpv+bWDcivHlHtWYJDaozWK01WXsnBg9EdeqCoGUzP1cavXCx0xfjNv1AtRkSBSVqQij9d0Cjb82q8xlizaZVBa+LXYdimeZE/Om5dZUkrFQpVo=; X-YMail-OSG: 5OxI6q0VM1m_nDuxP5Me3r1fr6eefs.XXVXNOhQQNLZnGNF UJYhHnxSlsYb2On9TZF.ynPshyvSrd6Hd9jVp2vsDQ3apBhLbgG_b08GDdkJ AKFlB8Ia5HKopA2yoSApqqZHMwyYE1dumKBAv_5BKSvbCD2zCJPY20NMGYvq IGR_L0AA6fbLD_aFI6qB63JiYTzXh9jDtl5CM3rpqKz3YKZ0mGwNgj0krYNi RMXry1t43D74Iut_g4habDSb6inbAU6bQebsrEt4Wx4AcuKull4A3TQP5m44 .oSeH.73gUqXbekvcNi.baamCwCsH0b8rxzCcgwfdymGacpkLG68uiuXrTyp rRgWNcfOhvp7j3ox1hm25H73IhOdkqplFC0vnFR2f871N4xlYDbsZn1Kzgzl FnhpjgnU9CiA2sIt2MJljB2ntve5HWtHXNB1hdztStExJRS9jbZYuLySxLYP Eqk3mSYjFFz1cxYH.LZOwLnGSPVD6.bYruTpcNm88ug655_eZHfpswhM0Rc1 539Vqn00KYscia2j07gxb6CbSFvjJS_9q8kyAfxlg2YTG0IuyPrRz9l4Sl9I dEOJsuXhYkup9DovAwPDcN8.zjNIz6WOXuqDKsMm31NTeSb5Ni1GZAqijNCz 5wnOj8lSsIICcB.ey1Iek7HRumBxzKoKJV_TSUE_pgULLyrvsySQ.daf_Xf1 G2Go3pNBFO0lsKLvlDDSEg8VMiJbxr3O5a9cL0ETVwet8G69vjkE4DHXkLWV O5fm5cuNklTkNbmvEZ08- Received: from [61.15.240.116] by web190806.mail.sg3.yahoo.com via HTTP; Fri, 11 Jan 2013 00:39:07 SGT X-Rocket-MIMEInfo: 001.001, SGkgQXJ0ZW0sDQoNClRoYW5rcyBmb3IgcmVwbHkuDQoNCkkgaGF2ZSB0cmllZCBzb21lIHRlc3RzLCBnb29kIGFuZCBiYWQgcmVzdWx0IGlzIGluIGJlbG93Li4uLi4uLi4uLg0KDQpsb2diaWFzPXRocm91Z2hwdXQsIHByaW1hcnljYWNoZT1hbGwsIHN5bmM9c3RhbmRhcmQNClJlc3VsdDogU1FMDQpLZXkgwqDCoCDCoFZhbHVlIMKgwqAgwqBUaW1lIMKgwqAgwqBDb21tZW50DQpJbnNlcnQgVGltZTogwqDCoCDCoDEwMDAwIMKgwqAgwqAyOCBzIDotKCDCoMKgIMKgU2hvdWxkIG5vdCB0YWtlIG1vcmUgdGhhbiABMAEBAQE- X-Mailer: YahooMailClassic/15.1.2 YahooMailWebService/0.8.130.494 Message-ID: <1357835947.77658.YahooMailClassic@web190806.mail.sg3.yahoo.com> Date: Fri, 11 Jan 2013 00:39:07 +0800 (SGT) From: Patrick Dung Subject: Re: ZFS sub-optimal performance with default setting To: Artem Belevich In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Content-Filtered-By: Mailman/MimeDel 2.1.14 Cc: 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 16:39:10 -0000 Hi Artem, Thanks for reply. I have tried some tests, good and bad result is in below.......... 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, 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 =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 Regards, Patrick --- On Thu, 1/10/13, Artem Belevich wrote: From: Artem Belevich Subject: Re: ZFS sub-optimal performance with default setting To: "Patrick Dung" Cc: "freebsd-fs" Date: Thursday, January 10, 2013, 9:37 AM On Wed, Jan 9, 2013 at 6:31 AM, Patrick Dung wro= te: > 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. 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-z= fs-wp-167894.pdf You may also check if disabling prefetching (via vfs.zfs.prefetch_disable=3D1 tunable in loader.conf) helps your workload. --Artem