From: Craig James <craig_james@emolecules.com>
To: pgsql-performance@postgresql.org
Subject: Re: Weird XFS WAL problem
Date: Thu, 03 Jun 2010 10:06:11 -0700
Message-ID: <4C07E103.9020909@emolecules.com> (raw)
In-Reply-To: <4C06EC01.3060506@catalyst.net.nz>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig_james@emolecules.com
Subject: Re: Weird XFS WAL problem
In-Reply-To: <4C07E103.9020909@emolecules.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox