pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWeird XFS WAL problem
25+ messages / 8 participants
[nested] [flat]
* Weird XFS WAL problem
@ 2010-06-02 23:30 Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 13:01 ` Re: Weird XFS WAL problem Merlin Moncure <mmoncure@gmail.com>
2010-06-03 16:52 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
0 siblings, 3 replies; 25+ messages in thread
From: Craig James @ 2010-06-02 23:30 UTC (permalink / raw)
To: pgsql-performance
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.
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
2 disks: RAID1 500GB ext4 blocksize=4096
8 disks: RAID10 2TB, stripe size 64K, blocksize=4096 (ext4 or xfs - see below)
2 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 ------Sequential Output------ --Sequential Input- --Random-
-Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP
argon 24064M 70491 99 288158 25 129918 16 65296 97 428210 23 558.9 1
------Sequential Create------ --------Random Create--------
-Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP
16 23283 81 +++++ +++ 13775 56 20143 74 +++++ +++ 15152 54
argon,24064M,70491,99,288158,25,129918,16,65296,97,428210,23,558.9,1,16,23283,81,+++++,+++,13775,56,20143\
,74,+++++,+++,15152,54
pgbench -i -s 100 -U test
pgbench -c 10 -t 10000 -U test
scaling factor: 100
query mode: simple
number of clients: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
tps = 1046.104635 (including connections establishing)
tps = 1046.337276 (excluding connections establishing)
Now the mystery: I moved the pg_xlog directory to a RAID1 array (same 3WARE controller, two more SATA 7200 disks). Run the same tests and ...
tps = 82.325446 (including connections establishing)
tps = 82.326874 (excluding connections establishing)
I thought I'd made a mistake, like maybe I moved the whole database to the RAID1 array, but I checked and double checked. I even watched the lights blink - the WAL was definitely on the RAID1 and the rest of Postgres on the 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:
dd if=/dev/zero of=./bigfile bs=8192 count=2000000
which yielded 98.8 MB/sec - not bad. bonnie++ on the RAID1 pair showed good performance too:
Version 1.03e ------Sequential Output------ --Sequential Input- --Random-
-Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP
argon 24064M 68601 99 110057 18 46534 6 59883 90 123053 7 471.3 1
------Sequential Create------ --------Random Create--------
-Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP
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? Does this make any sense at all? It'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 WAL 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.
Thanks,
Craig
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
@ 2010-06-02 23:40 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2 siblings, 1 reply; 25+ messages in thread
From: Mark Kirkwood @ 2010-06-02 23:40 UTC (permalink / raw)
To: pgsql-performance
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?
Also, are barriers *on* on the RAID1 mount and off on the RAID10 one?
Cheers
Mark
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
@ 2010-06-03 17:06 ` Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
2010-06-03 18:27 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
0 siblings, 2 replies; 25+ messages in thread
From: Craig James @ 2010-06-03 17:06 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
@ 2010-06-03 17:14 ` Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 1 reply; 25+ messages in thread
From: Matthew Wakeling @ 2010-06-03 17:14 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
On Thu, 3 Jun 2010, Craig James wrote:
>> 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.
This worries me a little. Does your array have a battery-backed cache? If
so, then it should be fast regardless of barriers (although barriers may
make a small difference). If it does not, then it is likely that the fast
speed you are seeing with barriers off is unsafe.
There should be no "just missed the sector going past for write" problem
ever with a battery-backed cache.
Matthew
--
There once was a limerick .sig
that really was not very big
It was going quite fine
Till it reached the fourth line
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
@ 2010-06-03 17:30 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 18:18 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
0 siblings, 1 reply; 25+ messages in thread
From: Kevin Grittner @ 2010-06-03 17:30 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance
Matthew Wakeling <matthew@flymine.org> wrote:
> On Thu, 3 Jun 2010, Craig James wrote:
>>> 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.
>
> This worries me a little. Does your array have a battery-backed
> cache? If so, then it should be fast regardless of barriers
> (although barriers may make a small difference). If it does not,
> then it is likely that the fast speed you are seeing with barriers
> off is unsafe.
I've seen this, too (with xfs). Our RAID controller, in spite of
having BBU cache configured for writeback, waits for actual
persistence on disk for write barriers (unlike for fsync). This
does strike me as surprising to the point of bordering on qualifying
as a bug. It means that you can't take advantage of the BBU cache
and get the benefit of write barriers in OS cache behavior. :-(
-Kevin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-06-03 18:18 ` Greg Smith <greg@2ndquadrant.com>
2010-06-03 18:40 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 15:06 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
0 siblings, 2 replies; 25+ messages in thread
From: Greg Smith @ 2010-06-03 18:18 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Craig James <craig_james@emolecules.com>; Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Kevin Grittner wrote:
> I've seen this, too (with xfs). Our RAID controller, in spite of
> having BBU cache configured for writeback, waits for actual
> persistence on disk for write barriers (unlike for fsync). This
> does strike me as surprising to the point of bordering on qualifying
> as a bug.
Completely intentional, and documented at
http://xfs.org/index.php/XFS_FAQ#Q._Should_barriers_be_enabled_with_storage_which_has_a_persistent_w...
The issue is that XFS will actually send the full "flush your cache"
call to the controller, rather than just the usual fsync call, and that
eliminates the benefit of having a write cache there in the first
place. Good controllers respect that and flush their whole write cache
out. And ext4 has adopted the same mechanism. This is very much a good
thing from the perspective of database reliability for people with
regular hard drives who don't have a useful write cache on their cheap
hard drives. It allows them to keep the disk's write cache on for other
things, while still getting the proper cache flushes when the database
commits demand them. It does mean that everyone with a non-volatile
battery backed cache, via RAID card typically, needs to turn barriers
off manually.
I've already warned on this list that PostgreSQL commit performance on
ext4 is going to appear really terrible to many people. If you
benchmark and don't recognize ext3 wasn't operating in a reliable mode
before, the performance drop now that ext4 is doing the right thing with
barriers looks impossibly bad.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 18:18 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
@ 2010-06-03 18:40 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 19:10 ` Re: Weird XFS WAL problem Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 25+ messages in thread
From: Kevin Grittner @ 2010-06-03 18:40 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Craig James <craig_james@emolecules.com>; Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Greg Smith <greg@2ndquadrant.com> wrote:
> Kevin Grittner wrote:
>> I've seen this, too (with xfs). Our RAID controller, in spite of
>> having BBU cache configured for writeback, waits for actual
>> persistence on disk for write barriers (unlike for fsync). This
>> does strike me as surprising to the point of bordering on
>> qualifying as a bug.
> Completely intentional, and documented at
>
http://xfs.org/index.php/XFS_FAQ#Q._Should_barriers_be_enabled_with_storage_which_has_a_persistent_w...
Yeah, I read that long ago and I've disabled write barriers because
of it; however, it still seems wrong that the RAID controller
insists on flushing to the drives in write-back mode. Here are my
reasons for wishing it was otherwise:
(1) We've had batteries on our RAID controllers fail occasionally.
The controller automatically degrades to write-through, and we get
an email from the server and schedule a tech to travel to the site
and replace the battery; but until we take action we are now exposed
to possible database corruption. Barriers don't automatically come
on when the controller flips to write-through mode.
(2) It precludes any possibility of moving from fsync techniques to
write barrier techniques for ensuring database integrity. If the OS
respected write barriers and the controller considered the write
satisfied when it hit BBU cache, write barrier techniques would
work, and checkpoints could be made smoother. Think how nicely that
would inter-operate with point (1).
So, while I understand it's Working As Designed, I think the design
is surprising and sub-optimal.
-Kevin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 18:18 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
2010-06-03 18:40 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-06-03 19:10 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-06-03 19:17 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 19:31 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
0 siblings, 2 replies; 25+ messages in thread
From: Scott Marlowe @ 2010-06-03 19:10 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Greg Smith <greg@2ndquadrant.com>; Craig James <craig_james@emolecules.com>; Matthew Wakeling <matthew@flymine.org>; pgsql-performance
On Thu, Jun 3, 2010 at 12:40 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
>
> Yeah, I read that long ago and I've disabled write barriers because
> of it; however, it still seems wrong that the RAID controller
> insists on flushing to the drives in write-back mode. Here are my
> reasons for wishing it was otherwise:
I think it's a case of the quickest, simplest answer to semi-new tech.
Not sure what to do with barriers? Just flush the whole cache.
I'm guessing that this will get optimized in the future.
BTW, I'll have LSI Megaraid latest and greatest to test on in a month,
and older Areca 1680s as well. I'll be updating the firmware on the
arecas, and I'll run some tests on the whole barrier behaviour to see
if it's gotten any better lately.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 18:18 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
2010-06-03 18:40 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 19:10 ` Re: Weird XFS WAL problem Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-06-03 19:17 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 25+ messages in thread
From: Kevin Grittner @ 2010-06-03 19:17 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Greg Smith <greg@2ndquadrant.com>; Craig James <craig_james@emolecules.com>; Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Scott Marlowe <scott.marlowe@gmail.com> wrote:
> I think it's a case of the quickest, simplest answer to semi-new
> tech. Not sure what to do with barriers? Just flush the whole
> cache.
>
> I'm guessing that this will get optimized in the future.
Let's hope so.
That reminds me, the write barrier concept is at least on the
horizon as a viable technology; does anyone know if the asynchronous
graphs concept in this (one page) paper ever came to anything? (I
haven't hear anything about it lately.)
http://www.usenix.org/events/fast05/wips/burnett.pdf
-Kevin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 18:18 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
2010-06-03 18:40 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 19:10 ` Re: Weird XFS WAL problem Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-06-03 19:31 ` Greg Smith <greg@2ndquadrant.com>
2010-06-03 19:44 ` Re: Weird XFS WAL problem Scott Marlowe <scott.marlowe@gmail.com>
2010-06-03 20:01 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 09:27 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
1 sibling, 3 replies; 25+ messages in thread
From: Greg Smith @ 2010-06-03 19:31 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Craig James <craig_james@emolecules.com>; Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Scott Marlowe wrote:
> I think it's a case of the quickest, simplest answer to semi-new tech.
> Not sure what to do with barriers? Just flush the whole cache.
>
Well, that really is the only useful thing you can do with regular SATA
drives; the ATA command set isn't any finer grained than that in a way
that's useful for this context. And it's also quite reasonable for a
RAID controller to respond to that "flush the whole cache" call by
flushing its cache. So it's not just the simplest first answer, I
believe it's the only answer until a better ATA command set becomes
available.
I think this can only be resolved usefully for all of us at the RAID
firmware level. If the controller had some logic that said "it's OK to
not flush the cache when that call comes in if my battery is working
fine", that would make this whole problem go away. I don't expect it's
possible to work around the exact set of concerns Kevin listed any other
way, because as he pointed out the right thing to do is very dependent
on the battery health, which the OS also doesn't know (again, would
require some new command set verbage).
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 18:18 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
2010-06-03 18:40 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 19:10 ` Re: Weird XFS WAL problem Scott Marlowe <scott.marlowe@gmail.com>
2010-06-03 19:31 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
@ 2010-06-03 19:44 ` Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 25+ messages in thread
From: Scott Marlowe @ 2010-06-03 19:44 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Craig James <craig_james@emolecules.com>; Matthew Wakeling <matthew@flymine.org>; pgsql-performance
On Thu, Jun 3, 2010 at 1:31 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> Scott Marlowe wrote:
>>
>> I think it's a case of the quickest, simplest answer to semi-new tech.
>> Not sure what to do with barriers? Just flush the whole cache.
>>
>
> Well, that really is the only useful thing you can do with regular SATA
> drives; the ATA command set isn't any finer grained than that in a way
> that's useful for this context. And it's also quite reasonable for a RAID
> controller to respond to that "flush the whole cache" call by flushing its
> cache. So it's not just the simplest first answer, I believe it's the only
> answer until a better ATA command set becomes available.
>
> I think this can only be resolved usefully for all of us at the RAID
> firmware level. If the controller had some logic that said "it's OK to not
> flush the cache when that call comes in if my battery is working fine",
That's what already happens for fsync on a BBU controller, so I don't
think the code to do so would be something fancy and new, just a
simple change of logic on which code path to take.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 18:18 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
2010-06-03 18:40 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 19:10 ` Re: Weird XFS WAL problem Scott Marlowe <scott.marlowe@gmail.com>
2010-06-03 19:31 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
@ 2010-06-03 20:01 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2 siblings, 0 replies; 25+ messages in thread
From: Kevin Grittner @ 2010-06-03 20:01 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Craig James <craig_james@emolecules.com>; Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Greg Smith <greg@2ndquadrant.com> wrote:
> I think this can only be resolved usefully for all of us at the
> RAID firmware level. If the controller had some logic that said
> "it's OK to not flush the cache when that call comes in if my
> battery is working fine", that would make this whole problem go
> away.
That is exactly what I've been trying to suggest. Sorry for not
being more clear about it.
-Kevin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 18:18 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
2010-06-03 18:40 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 19:10 ` Re: Weird XFS WAL problem Scott Marlowe <scott.marlowe@gmail.com>
2010-06-03 19:31 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
@ 2010-06-04 09:27 ` Matthew Wakeling <matthew@flymine.org>
2 siblings, 0 replies; 25+ messages in thread
From: Matthew Wakeling @ 2010-06-04 09:27 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Kevin Grittner <Kevin.Grittner@wicourts.gov>; Craig James <craig_james@emolecules.com>; pgsql-performance
On Thu, 3 Jun 2010, Greg Smith wrote:
> And it's also quite reasonable for a RAID controller to respond to that
> "flush the whole cache" call by flushing its cache.
Remember that the RAID controller is presenting itself to the OS as a
large disc, and hiding the individual discs from the OS. Why should the OS
care what has actually happened to the individual discs' caches, as long
as that "flush the whole cache" command guarantees that the data is
persistent. Taking the RAID array as a whole, that happens when the data
hits the write-back cache.
The only circumstance where you actually need to flush the data to the
individual discs is when you need to take that disc away somewhere else
and read it on another system. That's quite a rare use case for a RAID
array (http://thedailywtf.com/Articles/RAIDing_Disks.aspx
notwithstanding).
> If the controller had some logic that said "it's OK to not flush the
> cache when that call comes in if my battery is working fine", that would
> make this whole problem go away.
The only place this can be properly sorted is the RAID controller.
Anywhere else would be crazy.
Matthew
--
"To err is human; to really louse things up requires root
privileges." -- Alexander Pope, slightly paraphrased
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 18:18 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
@ 2010-06-04 15:06 ` Bruce Momjian <bruce@momjian.us>
2010-06-04 15:16 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 1 reply; 25+ messages in thread
From: Bruce Momjian @ 2010-06-04 15:06 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Craig James <craig_james@emolecules.com>; Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Greg Smith wrote:
> Kevin Grittner wrote:
> > I've seen this, too (with xfs). Our RAID controller, in spite of
> > having BBU cache configured for writeback, waits for actual
> > persistence on disk for write barriers (unlike for fsync). This
> > does strike me as surprising to the point of bordering on qualifying
> > as a bug.
> Completely intentional, and documented at
> http://xfs.org/index.php/XFS_FAQ#Q._Should_barriers_be_enabled_with_storage_which_has_a_persistent_w...
>
> The issue is that XFS will actually send the full "flush your cache"
> call to the controller, rather than just the usual fsync call, and that
> eliminates the benefit of having a write cache there in the first
> place. Good controllers respect that and flush their whole write cache
> out. And ext4 has adopted the same mechanism. This is very much a good
> thing from the perspective of database reliability for people with
> regular hard drives who don't have a useful write cache on their cheap
> hard drives. It allows them to keep the disk's write cache on for other
> things, while still getting the proper cache flushes when the database
> commits demand them. It does mean that everyone with a non-volatile
> battery backed cache, via RAID card typically, needs to turn barriers
> off manually.
>
> I've already warned on this list that PostgreSQL commit performance on
> ext4 is going to appear really terrible to many people. If you
> benchmark and don't recognize ext3 wasn't operating in a reliable mode
> before, the performance drop now that ext4 is doing the right thing with
> barriers looks impossibly bad.
Well, this is depressing. Now that we finally have common
battery-backed cache RAID controller cards, the file system developers
have throw down another roadblock in ext4 and xfs. Do we need to
document this?
On another topic, I am a little unclear on how things behave when the
drive is write-back. If the RAID controller card writes to the drive,
but the data isn't on the platers, how does it know when it can discard
that information from the BBU RAID cache?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 18:18 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
2010-06-04 15:06 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
@ 2010-06-04 15:16 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 15:18 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 25+ messages in thread
From: Kevin Grittner @ 2010-06-04 15:16 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; Bruce Momjian <bruce@momjian.us>; +Cc: Craig James <craig_james@emolecules.com>; Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Bruce Momjian <bruce@momjian.us> wrote:
> On another topic, I am a little unclear on how things behave when
> the drive is write-back. If the RAID controller card writes to the
> drive, but the data isn't on the platers, how does it know when it
> can discard that information from the BBU RAID cache?
The controller waits for the drive to tell it that it has made it to
the platter before it discards it. What made you think otherwise?
-Kevin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 18:18 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
2010-06-04 15:06 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
2010-06-04 15:16 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-06-04 15:18 ` Bruce Momjian <bruce@momjian.us>
2010-06-04 15:23 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 25+ messages in thread
From: Bruce Momjian @ 2010-06-04 15:18 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Greg Smith <greg@2ndquadrant.com>; Craig James <craig_james@emolecules.com>; Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Kevin Grittner wrote:
> Bruce Momjian <bruce@momjian.us> wrote:
>
> > On another topic, I am a little unclear on how things behave when
> > the drive is write-back. If the RAID controller card writes to the
> > drive, but the data isn't on the platers, how does it know when it
> > can discard that information from the BBU RAID cache?
>
> The controller waits for the drive to tell it that it has made it to
> the platter before it discards it. What made you think otherwise?
Because a write-back drive cache says it is on the drive before it hits
the platters, which I think is the default for SATA drive. Is that
inaccurate?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 18:18 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
2010-06-04 15:06 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
2010-06-04 15:16 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 15:18 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
@ 2010-06-04 15:23 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 15:30 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 25+ messages in thread
From: Kevin Grittner @ 2010-06-04 15:23 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Greg Smith <greg@2ndquadrant.com>; Craig James <craig_james@emolecules.com>; Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Bruce Momjian <bruce@momjian.us> wrote:
> Kevin Grittner wrote:
>> The controller waits for the drive to tell it that it has made it
>> to the platter before it discards it. What made you think
>> otherwise?
>
> Because a write-back drive cache says it is on the drive before it
> hits the platters, which I think is the default for SATA drive.
> Is that inaccurate?
Any decent RAID controller will ensure that the drives themselves
aren't using write-back caching. When we've mentioned write-back
versus write-through on this thread we've been talking about the
behavior of the *controller*. We have our controllers configured to
use write-back through the BBU cache as long as the battery is good,
but to automatically switch to write-through if the battery goes
bad.
-Kevin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 18:18 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
2010-06-04 15:06 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
2010-06-04 15:16 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 15:18 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
2010-06-04 15:23 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-06-04 15:30 ` Bruce Momjian <bruce@momjian.us>
2010-06-04 15:35 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 25+ messages in thread
From: Bruce Momjian @ 2010-06-04 15:30 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Greg Smith <greg@2ndquadrant.com>; Craig James <craig_james@emolecules.com>; Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Kevin Grittner wrote:
> Bruce Momjian <bruce@momjian.us> wrote:
> > Kevin Grittner wrote:
>
> >> The controller waits for the drive to tell it that it has made it
> >> to the platter before it discards it. What made you think
> >> otherwise?
> >
> > Because a write-back drive cache says it is on the drive before it
> > hits the platters, which I think is the default for SATA drive.
> > Is that inaccurate?
>
> Any decent RAID controller will ensure that the drives themselves
> aren't using write-back caching. When we've mentioned write-back
> versus write-through on this thread we've been talking about the
> behavior of the *controller*. We have our controllers configured to
> use write-back through the BBU cache as long as the battery is good,
> but to automatically switch to write-through if the battery goes
> bad.
OK, good, but when why would a BBU RAID controller flush stuff to disk
with a flush-all command? I thought the whole goal of BBU was to avoid
such flushes. What is unique about the command ext4/xfs is sending?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 18:18 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
2010-06-04 15:06 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
2010-06-04 15:16 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 15:18 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
2010-06-04 15:23 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 15:30 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
@ 2010-06-04 15:35 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 15:41 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
2010-06-05 22:50 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
0 siblings, 2 replies; 25+ messages in thread
From: Kevin Grittner @ 2010-06-04 15:35 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Greg Smith <greg@2ndquadrant.com>; Craig James <craig_james@emolecules.com>; Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Bruce Momjian <bruce@momjian.us> wrote:
> Kevin Grittner wrote:
>> Any decent RAID controller will ensure that the drives themselves
>> aren't using write-back caching. When we've mentioned write-back
>> versus write-through on this thread we've been talking about the
>> behavior of the *controller*. We have our controllers configured
>> to use write-back through the BBU cache as long as the battery is
>> good, but to automatically switch to write-through if the battery
>> goes bad.
>
> OK, good, but when why would a BBU RAID controller flush stuff to
> disk with a flush-all command? I thought the whole goal of BBU
> was to avoid such flushes.
That has been *precisely* my point.
I don't know at the protocol level; I just know that write barriers
do *something* which causes our controllers to wait for actual disk
platter persistence, while fsync does not.
The write barrier concept seems good to me, and I wish it could be
used at the OS level without killing performance. I blame the
controller, for not treating it the same as fsync (i.e., as long as
it's in write-back mode it should treat data as persisted as soon as
it's in BBU cache).
-Kevin
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 18:18 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
2010-06-04 15:06 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
2010-06-04 15:16 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 15:18 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
2010-06-04 15:23 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 15:30 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
2010-06-04 15:35 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-06-04 15:41 ` Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 25+ messages in thread
From: Bruce Momjian @ 2010-06-04 15:41 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Greg Smith <greg@2ndquadrant.com>; Craig James <craig_james@emolecules.com>; Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Kevin Grittner wrote:
> Bruce Momjian <bruce@momjian.us> wrote:
> > Kevin Grittner wrote:
>
> >> Any decent RAID controller will ensure that the drives themselves
> >> aren't using write-back caching. When we've mentioned write-back
> >> versus write-through on this thread we've been talking about the
> >> behavior of the *controller*. We have our controllers configured
> >> to use write-back through the BBU cache as long as the battery is
> >> good, but to automatically switch to write-through if the battery
> >> goes bad.
> >
> > OK, good, but when why would a BBU RAID controller flush stuff to
> > disk with a flush-all command? I thought the whole goal of BBU
> > was to avoid such flushes.
>
> That has been *precisely* my point.
>
> I don't know at the protocol level; I just know that write barriers
> do *something* which causes our controllers to wait for actual disk
> platter persistence, while fsync does not.
>
> The write barrier concept seems good to me, and I wish it could be
> used at the OS level without killing performance. I blame the
> controller, for not treating it the same as fsync (i.e., as long as
> it's in write-back mode it should treat data as persisted as soon as
> it's in BBU cache).
Yeah. I wonder if it honors the cache flush because it might think it
is replacing disks or something odd. I think we are going to have to
document this in 9.0 because obviously you have seen it already.
Is this an issue with SAS cards/drives as well?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 18:18 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
2010-06-04 15:06 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
2010-06-04 15:16 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 15:18 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
2010-06-04 15:23 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 15:30 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
2010-06-04 15:35 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-06-05 22:50 ` Greg Smith <greg@2ndquadrant.com>
2010-07-07 14:42 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 25+ messages in thread
From: Greg Smith @ 2010-06-05 22:50 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Bruce Momjian <bruce@momjian.us>; Craig James <craig_james@emolecules.com>; Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Kevin Grittner wrote:
> I don't know at the protocol level; I just know that write barriers
> do *something* which causes our controllers to wait for actual disk
> platter persistence, while fsync does not
It's in the docs now:
http://www.postgresql.org/docs/9.0/static/wal-reliability.html
FLUSH CACHE EXT is the ATAPI-6 call that filesystems use to enforce
barriers on that type of drive. Here's what the relevant portion of the
ATAPI spec says:
"This command is used by the host to request the device to flush the
write cache. If there is data in the write
cache, that data shall be written to the media.The BSY bit shall remain
set to one until all data has been
successfully written or an error occurs."
SAS systems have a similar call named SYNCHRONIZE CACHE.
The improvement I actually expect to arrive here first is a reliable
implementation of O_SYNC/O_DSYNC writes. Both SAS and SATA drives that
capable of doing Native Command Queueing support a write type called
"Force Unit Access", which is essentially just like a direct write that
cannot be cached. When we get more kernels with reliable sync writing
that maps under the hood to FUA, and can change wal_sync_method to use
them, the need to constantly call fsync for every write to the WAL will
go away. Then the "blow out the RAID cache when barriers are on"
behavior will only show up during checkpoint fsyncs, which will make
things a lot better (albeit still not ideal).
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Re: Weird XFS WAL problem Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 18:18 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
2010-06-04 15:06 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
2010-06-04 15:16 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 15:18 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
2010-06-04 15:23 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 15:30 ` Re: Weird XFS WAL problem Bruce Momjian <bruce@momjian.us>
2010-06-04 15:35 ` Re: Weird XFS WAL problem Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-05 22:50 ` Re: Weird XFS WAL problem Greg Smith <greg@2ndquadrant.com>
@ 2010-07-07 14:42 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 25+ messages in thread
From: Bruce Momjian @ 2010-07-07 14:42 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Craig James <craig_james@emolecules.com>; Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Greg Smith wrote:
> Kevin Grittner wrote:
> > I don't know at the protocol level; I just know that write barriers
> > do *something* which causes our controllers to wait for actual disk
> > platter persistence, while fsync does not
>
> It's in the docs now:
> http://www.postgresql.org/docs/9.0/static/wal-reliability.html
>
> FLUSH CACHE EXT is the ATAPI-6 call that filesystems use to enforce
> barriers on that type of drive. Here's what the relevant portion of the
> ATAPI spec says:
>
> "This command is used by the host to request the device to flush the
> write cache. If there is data in the write
> cache, that data shall be written to the media.The BSY bit shall remain
> set to one until all data has been
> successfully written or an error occurs."
>
> SAS systems have a similar call named SYNCHRONIZE CACHE.
>
> The improvement I actually expect to arrive here first is a reliable
> implementation of O_SYNC/O_DSYNC writes. Both SAS and SATA drives that
> capable of doing Native Command Queueing support a write type called
> "Force Unit Access", which is essentially just like a direct write that
> cannot be cached. When we get more kernels with reliable sync writing
> that maps under the hood to FUA, and can change wal_sync_method to use
> them, the need to constantly call fsync for every write to the WAL will
> go away. Then the "blow out the RAID cache when barriers are on"
> behavior will only show up during checkpoint fsyncs, which will make
> things a lot better (albeit still not ideal).
Great information! I have added the attached documentation patch to
explain the write-barrier/BBU interaction. This will appear in the 9.0
documentation.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
Attachments:
/rtmp/diff (text/x-diff, 4.4K) download
Index: doc/src/sgml/wal.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/wal.sgml,v
retrieving revision 1.66
diff -c -c -r1.66 wal.sgml
*** doc/src/sgml/wal.sgml 13 Apr 2010 14:15:25 -0000 1.66
--- doc/src/sgml/wal.sgml 7 Jul 2010 13:55:58 -0000
***************
*** 48,68 ****
some later time. Such caches can be a reliability hazard because the
memory in the disk controller cache is volatile, and will lose its
contents in a power failure. Better controller cards have
! <firstterm>battery-backed</> caches, meaning the card has a battery that
maintains power to the cache in case of system power loss. After power
is restored the data will be written to the disk drives.
</para>
<para>
And finally, most disk drives have caches. Some are write-through
! while some are write-back, and the
! same concerns about data loss exist for write-back drive caches as
! exist for disk controller caches. Consumer-grade IDE and SATA drives are
! particularly likely to have write-back caches that will not survive a
! power failure, though <acronym>ATAPI-6</> introduced a drive cache
! flush command (FLUSH CACHE EXT) that some file systems use, e.g. <acronym>ZFS</>.
! Many solid-state drives (SSD) also have volatile write-back
! caches, and many do not honor cache flush commands by default.
To check write caching on <productname>Linux</> use
<command>hdparm -I</>; it is enabled if there is a <literal>*</> next
to <literal>Write cache</>; <command>hdparm -W</> to turn off
--- 48,74 ----
some later time. Such caches can be a reliability hazard because the
memory in the disk controller cache is volatile, and will lose its
contents in a power failure. Better controller cards have
! <firstterm>battery-backed unit</> (<acronym>BBU</>) caches, meaning
! the card has a battery that
maintains power to the cache in case of system power loss. After power
is restored the data will be written to the disk drives.
</para>
<para>
And finally, most disk drives have caches. Some are write-through
! while some are write-back, and the same concerns about data loss
! exist for write-back drive caches as exist for disk controller
! caches. Consumer-grade IDE and SATA drives are particularly likely
! to have write-back caches that will not survive a power failure,
! though <acronym>ATAPI-6</> introduced a drive cache flush command
! (<command>FLUSH CACHE EXT</>) that some file systems use, e.g.
! <acronym>ZFS</>, <acronym>ext4</>. (The SCSI command
! <command>SYNCHRONIZE CACHE</> has long been available.) Many
! solid-state drives (SSD) also have volatile write-back caches, and
! many do not honor cache flush commands by default.
! </para>
!
! <para>
To check write caching on <productname>Linux</> use
<command>hdparm -I</>; it is enabled if there is a <literal>*</> next
to <literal>Write cache</>; <command>hdparm -W</> to turn off
***************
*** 83,88 ****
--- 89,113 ----
</para>
<para>
+ Many file systems that use write barriers (e.g. <acronym>ZFS</>,
+ <acronym>ext4</>) internally use <command>FLUSH CACHE EXT</> or
+ <command>SYNCHRONIZE CACHE</> commands to flush data to the platers on
+ write-back-enabled drives. Unfortunately, such write barrier file
+ systems behave suboptimally when combined with battery-backed unit
+ (<acronym>BBU</>) disk controllers. In such setups, the synchronize
+ command forces all data from the BBU to the disks, eliminating much
+ of the benefit of the BBU. You can run the utility
+ <filename>src/tools/fsync</> in the PostgreSQL source tree to see
+ if you are effected. If you are effected, the performance benefits
+ of the BBU cache can be regained by turning off write barriers in
+ the file system or reconfiguring the disk controller, if that is
+ an option. If write barriers are turned off, make sure the battery
+ remains active; a faulty battery can potentially lead to data loss.
+ Hopefully file system and disk controller designers will eventually
+ address this suboptimal behavior.
+ </para>
+
+ <para>
When the operating system sends a write request to the storage hardware,
there is little it can do to make sure the data has arrived at a truly
non-volatile storage area. Rather, it is the
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Re: Weird XFS WAL problem Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Re: Weird XFS WAL problem Craig James <craig_james@emolecules.com>
@ 2010-06-03 18:27 ` Greg Smith <greg@2ndquadrant.com>
1 sibling, 0 replies; 25+ messages in thread
From: Greg Smith @ 2010-06-03 18:27 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Craig James wrote:
> 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.
Commit performance is a separate number to measure that is not reflected
in any benchmark that tests sequential performance. I consider it the
fourth axis of disk system performance (seq read, seq write, random
IOPS, commit rate), and directly measure it with the sysbench fsync test
I recommended already. (You can do it with the right custom pgbench
script too).
You only get one commit per rotation on a drive, which is exactly what
you're seeing: a bit under the 120 spins/second @ 7200 RPM. Attempts
to time things just right to catch more than one sector per spin are
extremely difficult to accomplish, I spent a week on that once without
making any good progress. You can easily get 100MB/s on reads and
writes but only manage 100 commits/second.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
@ 2010-06-03 13:01 ` Merlin Moncure <mmoncure@gmail.com>
2 siblings, 0 replies; 25+ messages in thread
From: Merlin Moncure @ 2010-06-03 13:01 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
On Wed, Jun 2, 2010 at 7:30 PM, Craig James <craig_james@emolecules.com> 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.
>
> 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
> 2 disks: RAID1 500GB ext4 blocksize=4096
> 8 disks: RAID10 2TB, stripe size 64K, blocksize=4096 (ext4 or xfs - see
> below)
> 2 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 ------Sequential Output------ --Sequential Input-
> --Random-
> -Per Chr- --Block-- -Rewrite- -Per Chr- --Block--
> --Seeks--
> Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec
> %CP
> argon 24064M 70491 99 288158 25 129918 16 65296 97 428210 23
> 558.9 1
> ------Sequential Create------ --------Random
> Create--------
> -Create-- --Read--- -Delete-- -Create-- --Read---
> -Delete--
> files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec
> %CP
> 16 23283 81 +++++ +++ 13775 56 20143 74 +++++ +++ 15152
> 54
> argon,24064M,70491,99,288158,25,129918,16,65296,97,428210,23,558.9,1,16,23283,81,+++++,+++,13775,56,20143\
> ,74,+++++,+++,15152,54
>
> pgbench -i -s 100 -U test
> pgbench -c 10 -t 10000 -U test
> scaling factor: 100
> query mode: simple
> number of clients: 10
> number of transactions per client: 10000
> number of transactions actually processed: 100000/100000
> tps = 1046.104635 (including connections establishing)
> tps = 1046.337276 (excluding connections establishing)
>
> Now the mystery: I moved the pg_xlog directory to a RAID1 array (same 3WARE
> controller, two more SATA 7200 disks). Run the same tests and ...
>
> tps = 82.325446 (including connections establishing)
> tps = 82.326874 (excluding connections establishing)
>
> I thought I'd made a mistake, like maybe I moved the whole database to the
> RAID1 array, but I checked and double checked. I even watched the lights
> blink - the WAL was definitely on the RAID1 and the rest of Postgres on the
> 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:
>
> dd if=/dev/zero of=./bigfile bs=8192 count=2000000
>
> which yielded 98.8 MB/sec - not bad. bonnie++ on the RAID1 pair showed good
> performance too:
>
> Version 1.03e ------Sequential Output------ --Sequential Input-
> --Random-
> -Per Chr- --Block-- -Rewrite- -Per Chr- --Block--
> --Seeks--
> Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec
> %CP
> argon 24064M 68601 99 110057 18 46534 6 59883 90 123053 7
> 471.3 1
> ------Sequential Create------ --------Random
> Create--------
> -Create-- --Read--- -Delete-- -Create-- --Read---
> -Delete--
> files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec
> %CP
> 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? Does this make any sense at all? It'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 WAL
> 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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Weird XFS WAL problem
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
@ 2010-06-03 16:52 ` Greg Smith <greg@2ndquadrant.com>
2 siblings, 0 replies; 25+ messages in thread
From: Greg Smith @ 2010-06-03 16:52 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
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!
Normally <100 TPS means that the write cache on the WAL drive volume is
disabled (or set to write-through instead of write-back). When things
in this area get fishy, I will usually download sysbench and have it
specifically test how many fsync calls can happen per second.
http://projects.2ndquadrant.com/talks , "Database Hardware
Benchmarking", page 28 has an example of the right incantation for that.
Also, make sure you run 3ware's utilities and confirm all the disks have
finished their initialization and verification stages. If you just
adjusted disk layout that and immediate launched into benchmarks, those
are useless until the background cleanup is done.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 25+ messages in thread
end of thread, other threads:[~2010-07-07 14:42 UTC | newest]
Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-02 23:30 Weird XFS WAL problem Craig James <craig_james@emolecules.com>
2010-06-02 23:40 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2010-06-03 17:06 ` Craig James <craig_james@emolecules.com>
2010-06-03 17:14 ` Matthew Wakeling <matthew@flymine.org>
2010-06-03 17:30 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 18:18 ` Greg Smith <greg@2ndquadrant.com>
2010-06-03 18:40 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 19:10 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-06-03 19:17 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-03 19:31 ` Greg Smith <greg@2ndquadrant.com>
2010-06-03 19:44 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-06-03 20:01 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 09:27 ` Matthew Wakeling <matthew@flymine.org>
2010-06-04 15:06 ` Bruce Momjian <bruce@momjian.us>
2010-06-04 15:16 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 15:18 ` Bruce Momjian <bruce@momjian.us>
2010-06-04 15:23 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 15:30 ` Bruce Momjian <bruce@momjian.us>
2010-06-04 15:35 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-04 15:41 ` Bruce Momjian <bruce@momjian.us>
2010-06-05 22:50 ` Greg Smith <greg@2ndquadrant.com>
2010-07-07 14:42 ` Bruce Momjian <bruce@momjian.us>
2010-06-03 18:27 ` Greg Smith <greg@2ndquadrant.com>
2010-06-03 13:01 ` Merlin Moncure <mmoncure@gmail.com>
2010-06-03 16:52 ` Greg Smith <greg@2ndquadrant.com>
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