Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id CE82E6323FF for ; Thu, 3 Jun 2010 10:01:10 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 18037-10 for ; Thu, 3 Jun 2010 13:01:03 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-vw0-f46.google.com (mail-vw0-f46.google.com [209.85.212.46]) by mail.postgresql.org (Postfix) with ESMTP id 1283A6323D2 for ; Thu, 3 Jun 2010 10:01:02 -0300 (ADT) Received: by vws11 with SMTP id 11so113904vws.19 for ; Thu, 03 Jun 2010 06:01:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=x63aEVK0R6iL2FZiDuRfGtbmkG8qoaRpXGZPqFvgTYw=; b=AwPGfhmalWM0RX8j6Bdn2hjAVyhou35+Ztv2tCE78Ze8HqSplZP7WWesHy+IEkHHbn FlEgriFf1mexsNQakeu6jtConXLB+UJO1WJ0GIBATdglIQAW/slKgCY3M6EolFGC6uVQ v6HbzGrvwic1D61bgUhSYCc6+iV0j3zwCZTvY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=C9udiwJdzUGtHNkpYsEylAx6sAyi61sRt3DUulAzHLzONkSHq5tcJZgfTwDS5NfYzG whsZI3ObHOLE0hWnQ2EPX3vQy2cI3DmBok1BcFc3Ka6w8eBkGdTXGBEItZhjWGbEaW1G d9xrMDmotMaQhqF8keakLVt1YA4yk0+gLe0Io= MIME-Version: 1.0 Received: by 10.220.124.214 with SMTP id v22mr6775658vcr.101.1275570061464; Thu, 03 Jun 2010 06:01:01 -0700 (PDT) Received: by 10.220.87.67 with HTTP; Thu, 3 Jun 2010 06:01:01 -0700 (PDT) In-Reply-To: <4C06E994.2080905@emolecules.com> References: <4C06E994.2080905@emolecules.com> Date: Thu, 3 Jun 2010 09:01:01 -0400 Message-ID: Subject: Re: Weird XFS WAL problem From: Merlin Moncure To: Craig James Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.5 tagged_above=-10 required=5 tests=BAYES_05=-0.5, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201006/53 X-Sequence-Number: 38909 On Wed, Jun 2, 2010 at 7:30 PM, Craig James wr= ote: > I'm testing/tuning a new midsize server and ran into an inexplicable > problem. =A0With an RAID10 drive, when I move the WAL to a separate RAID1 > drive, TPS drops from over 1200 to less than 90! =A0 I've checked everyth= ing > and can't find a reason. > > Here are the details. > > 8 cores (2x4 Intel Nehalem 2 GHz) > 12 GB memory > 12 x 7200 SATA 500 GB disks > 3WARE 9650SE-12ML RAID controller with bbu > =A02 disks: RAID1 =A0500GB ext4 =A0blocksize=3D4096 > =A08 disks: RAID10 2TB, stripe size 64K, blocksize=3D4096 (ext4 or xfs - = see > below) > =A02 disks: hot swap > Ubuntu 10.04 LTS (Lucid) > > With xfs or ext4 on the RAID10 I got decent bonnie++ and pgbench results > (this one is for xfs): > > Version 1.03e =A0 =A0 =A0 ------Sequential Output------ --Sequential Inpu= t- > --Random- > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-Per Chr- --Block-- -Rewrite- -Per= Chr- --Block-- > --Seeks-- > Machine =A0 =A0 =A0 =A0Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec= %CP =A0/sec > %CP > argon =A0 =A0 =A0 =A024064M 70491 =A099 288158 =A025 129918 =A016 65296 = =A097 428210 =A023 > 558.9 =A0 1 > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0------Sequential Create------ ----= ----Random > Create-------- > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-Create-- --Read--- -Delete-- -Cre= ate-- --Read--- > -Delete-- > =A0 =A0 =A0 =A0 =A0 =A0 =A0files =A0/sec %CP =A0/sec %CP =A0/sec %CP =A0/= sec %CP =A0/sec %CP =A0/sec > %CP > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 16 23283 =A081 +++++ +++ 13775 =A056 2014= 3 =A074 +++++ +++ 15152 > =A054 > argon,24064M,70491,99,288158,25,129918,16,65296,97,428210,23,558.9,1,16,2= 3283,81,+++++,+++,13775,56,20143\ > ,74,+++++,+++,15152,54 > > pgbench -i -s 100 -U test > pgbench -c 10 -t 10000 -U test > =A0 =A0scaling factor: 100 > =A0 =A0query mode: simple > =A0 =A0number of clients: 10 > =A0 =A0number of transactions per client: 10000 > =A0 =A0number of transactions actually processed: 100000/100000 > =A0 =A0tps =3D 1046.104635 (including connections establishing) > =A0 =A0tps =3D 1046.337276 (excluding connections establishing) > > Now the mystery: I moved the pg_xlog directory to a RAID1 array (same 3WA= RE > controller, two more SATA 7200 disks). =A0Run the same tests and ... > > =A0 =A0tps =3D 82.325446 (including connections establishing) > =A0 =A0tps =3D 82.326874 (excluding connections establishing) > > I thought I'd made a mistake, like maybe I moved the whole database to th= e > RAID1 array, but I checked and double checked. =A0I even watched the ligh= ts > blink - the WAL was definitely on the RAID1 and the rest of Postgres on t= he > RAID10. > > So I moved the WAL back to the RAID10 array, and performance jumped right > back up to the >1200 TPS range. > > Next I check the RAID1 itself: > > =A0dd if=3D/dev/zero of=3D./bigfile bs=3D8192 count=3D2000000 > > which yielded 98.8 MB/sec - not bad. =A0bonnie++ on the RAID1 pair showed= good > performance too: > > Version 1.03e =A0 =A0 =A0 ------Sequential Output------ --Sequential Inpu= t- > --Random- > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-Per Chr- --Block-- -Rewrite- -Per= Chr- --Block-- > --Seeks-- > Machine =A0 =A0 =A0 =A0Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec= %CP =A0/sec > %CP > argon =A0 =A0 =A0 =A024064M 68601 =A099 110057 =A018 46534 =A0 6 59883 = =A090 123053 =A0 7 > 471.3 =A0 1 > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0------Sequential Create------ ----= ----Random > Create-------- > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-Create-- --Read--- -Delete-- -Cre= ate-- --Read--- > -Delete-- > =A0 =A0 =A0 =A0 =A0 =A0 =A0files =A0/sec %CP =A0/sec %CP =A0/sec %CP =A0/= sec %CP =A0/sec %CP =A0/sec > %CP > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 16 +++++ +++ +++++ +++ +++++ +++ +++++ ++= + +++++ +++ +++++ > +++ > argon,24064M,68601,99,110057,18,46534,6,59883,90,123053,7,471.3,1,16,++++= +,+++,+++++,+++,+++++,+++,+++++,\ > +++,+++++,+++,+++++,+++ > > So ... anyone have any idea at all how TPS drops to below 90 when I move = the > WAL to a separate RAID1 disk? =A0Does this make any sense at all? =A0It's > repeatable. It happens for both ext4 and xfs. It's weird. > > You can even watch the disk lights and see it: the RAID10 disks are on > almost constantly when the WAL is on the RAID10, but when you move the WA= L > over to the RAID1, its lights are dim and flicker a lot, like it's barely > getting any data, and the RAID10 disk's lights barely go on at all. *) Is your raid 1 configured writeback cache on the controller? *) have you tried changing wal_sync_method to fdatasync? merlin