Message-ID: <4C07E103.9020909@emolecules.com>
Date: Thu, 03 Jun 2010 10:06:11 -0700
From: Craig James <craig_james@emolecules.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Weird XFS WAL problem
References: <AANLkTikS_QvlkYfv5_hbsXFH4SsTh1nP_WoTwRqpUUcz@mail.gmail.com>
	<AANLkTikegoyBvJXhqZGLZyTwRVyBs4qCuCQ7d1Vntk4w@mail.gmail.com>
	<AANLkTikjkEvj2hG-D1CMu49PQXXbxHVzpXm9JbLWzMQo@mail.gmail.com>
	<AANLkTimtdk11hrWd-OpvXGLrh-4pukGd9eHEUemEM3OR@mail.gmail.com>
	<AANLkTinAhrwwwQKh6NrX-nnvcpxT1EFFlZrUxhaDB87C@mail.gmail.com>
	<AANLkTinuOUTbuke9FTMtwSObcZBio0uOS1ysWrqFyK3t@mail.gmail.com>
	<4C06E994.2080905@emolecules.com>
	<4C06EC01.3060506@catalyst.net.nz>
In-Reply-To: <4C06EC01.3060506@catalyst.net.nz>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 6/2/10 4:40 PM, Mark Kirkwood wrote:
> On 03/06/10 11:30, Craig James wrote:
>> I'm testing/tuning a new midsize server and ran into an inexplicable
>> problem. With an RAID10 drive, when I move the WAL to a separate RAID1
>> drive, TPS drops from over 1200 to less than 90! I've checked
>> everything and can't find a reason.
>
> Are the 2 new RAID1 disks the same make and model as the 12 RAID10 ones?

Yes.

> Also, are barriers *on* on the RAID1 mount and off on the RAID10 one?

It was the barriers.  "barrier=1" isn't just a bad idea on ext4, it's a disaster.

pgbench -i -s 100 -U test
pgbench -c 10 -t 10000 -U test

Change WAL to barrier=0

     tps = 1463.264981 (including connections establishing)
     tps = 1463.725687 (excluding connections establishing)

Change WAL to noatime, nodiratime, barrier=0

     tps = 1479.331476 (including connections establishing)
     tps = 1479.810545 (excluding connections establishing)

Change WAL to barrier=1

     tps = 82.325446 (including connections establishing)
     tps = 82.326874 (excluding connections establishing)

This is really hard to believe, because the bonnie++ numbers and dd(1) numbers look good (see my original post).  But it's totally repeatable.  It must be some really unfortunate "just missed the next sector going by the write head" problem.

So with ext4, bonnie++ and dd aren't the whole story.

BTW, I also learned that if you edit /etc/fstab and use "mount -oremount" it WON'T change "barrier=0/1" unless it is explicit in the fstab file.  That is, if you put "barrier=0" into /etc/fstab and use the remount, it will change it to no barriers.  But if you then remove it from /etc/fstab, it won't change it back to the default.  You have to actually put "barrier=1" if you want to get it back to the default.  This seems like a bug to me, and it made it really hard to track this down. "mount -oremount" is not the same as umount/mount!

Craig