From owner-freebsd-fs@FreeBSD.ORG Thu Jan 10 16:54:25 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 D1AEED33 for ; Thu, 10 Jan 2013 16:54:25 +0000 (UTC) (envelope-from patrick_dkt@yahoo.com.hk) Received: from nm1-vm3.bullet.mail.sg3.yahoo.com (nm1-vm3.bullet.mail.sg3.yahoo.com [106.10.148.74]) by mx1.freebsd.org (Postfix) with ESMTP id AF98594F for ; Thu, 10 Jan 2013 16:54:24 +0000 (UTC) Received: from [106.10.166.119] by nm1.bullet.mail.sg3.yahoo.com with NNFMP; 10 Jan 2013 16:54:23 -0000 Received: from [106.10.151.123] by tm8.bullet.mail.sg3.yahoo.com with NNFMP; 10 Jan 2013 16:54:22 -0000 Received: from [127.0.0.1] by omp1005.mail.sg3.yahoo.com with NNFMP; 10 Jan 2013 16:54:22 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 962291.15377.bm@omp1005.mail.sg3.yahoo.com Received: (qmail 99395 invoked by uid 60001); 10 Jan 2013 16:54:22 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com.hk; s=s1024; t=1357836862; bh=6EnG/c91LVw13UBtAltjVOY/cOP5AZww9kbeL/uFRCE=; 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=tKoFWpLvGzA/uB/KmdYIYUZu2lfsXCuA7fTaKKll93P8G27MGABs3wy1VcCNBpx+4YMruvlNFBBcubtlGEhq34KI3iXSTbZAVI6Tg9n64EgwXGkzdFqP3Td1r1/NSVMXrmvxXKdDIX1a1m93kyxTWXXiXZ+E3SYmUPZX0vpXMl0= 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=wt6eZKjjQ3NeueS5fafXfvTCVIjD1rlYuEzW3ugSI+Z29m5tRpFwRgUX5ITfXtua1bjRK6MgQuHacq4qTopON4GvJrsHhQ9eFqqSjkJYAE4Dr5pX5SPjGEkd/Aa+JXt0BsnlmGuEnWNd9v1TN8upwkO+gxxNujH2X3Iv5ax3DAE=; X-YMail-OSG: je2bYXAVM1kFX3DJXyGAOMxnXGvs9bkO6o7nAjuaIT0_p2e kYcuWb9iIXSl37LVw8pIiGyx94P2qdLYLRcC7H.s_5Ek1cLCjJaxT0dhH6eb 1p657ml1nwO2IXkzg4ThfcfI0QYVHlhHbB7tLI4zAXYPCSVwd3QvagRwXjvp IDvoBEP28wVkeHPYMFAWYtB1W.YgByvzox2OARcWELV7Ez9Ou_ZjK3wfd6X7 43fLeeJyd8P3p8JnQFr4RCE9sZl64VaD1oagVOksgdk9NDjayuadShw2o3du rRsVsbC6Cn05p04S3dMRriiX8Ox1X2L_jAPF5fjSuXm3Dol4UcF0tSTzMlgp 42WctK6JU3CC30QjCGEkcoB.HIT5EVCiW9pi4c2gqv94v83ytNGRdLLZ2psl 47L_q4hnhFB_79hB8aL1NVKYcfnH02E6gLZvBIzZG4DFkDr1pKqVtr5svWon D.tUCoTE4Eac92yB.D2hzeEj4DeCJX_w2CISwCytVPkTG91kE8eCBizdJRxr uLI0FRkWLj0xOwoqxhlQYPw8y9Xyp9cjUeN57ATomeujhbMpKeOWcaFukKSR 7qJ1X3.pfvi1oW5BjrWwGNUdV_W3_DdoK.EKU3XVZxQRWH0p.LURxMrDdibE xkD6K3BLrA_sgwubO4Se9ymwgv1BdM_QSBluM0hlHli281dVqsFZTKQmCveu 0s1YshchR_.SrdwN_ATK8lukE0vm6nbglvXVCxv_oHaMCxoQuLJ9B_YA4P_J I.aWV4YJqQBJAwTHZzMC84bLvMKxRLbVW.viz3_yn64DOEgySo6GSlUUHzno 4oQyms13VyysYqZIScsivALbqtFEPe5HxmYjUkpLRQUCEHsQAW5Ssf34r5X6 9X_lQKsl3g74gNOCxzejELtaM15whNfjhSDvgLUPHrizSjIL9BGXzm9XZGG5 yIg-- Received: from [61.15.240.116] by web190806.mail.sg3.yahoo.com via HTTP; Fri, 11 Jan 2013 00:54:22 SGT X-Rocket-MIMEInfo: 001.001, SSBoYXZlIHRyaWVkIHNvbWUgdGVzdHMsIGdvb2QgYW5kIGJhZCByZXN1bHQgaXMgaW4gYmVsb3cuLi4uLi4uLi4uDQpJIGFtIHN1cmUgdGhlcmUgaXMgc29tZSBib3R0bGVuZWNrLCBhbmQgdGhlIHJvb3QgY2F1c2UgaXMgc3RpbGwgdW5rbm93bi4NCg0KbG9nYmlhcz10aHJvdWdocHV0LCBwcmltYXJ5Y2FjaGU9YWxsLCBzeW5jPXN0YW5kYXJkDQpSZXN1bHQ6IFNRTA0KS2V5IMKgwqAgwqBWYWx1ZSDCoMKgIMKgVGltZSDCoMKgIMKgQ29tbWVudA0KSW5zZXJ0IFRpbWU6IMKgwqAgwqAxMDAwMCDCoMKgIMKgMjgBMAEBAQE- X-Mailer: YahooMailClassic/15.1.2 YahooMailWebService/0.8.130.494 Message-ID: <1357836862.81267.YahooMailClassic@web190806.mail.sg3.yahoo.com> Date: Fri, 11 Jan 2013 00:54:22 +0800 (SGT) From: Patrick Dung Subject: Re: ZFS sub-optimal performance with default setting To: Artem Belevich , Charles Sprickman In-Reply-To: <76DE1383-BAAA-4EFD-ABA6-A9328D79D5B3@bway.net> 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:54:26 -0000 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 wrote: From: Charles Sprickman Subject: Re: ZFS sub-optimal performance with default setting To: "Artem Belevich" Cc: "Patrick Dung" , "freebsd-fs" 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 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"