From: Pierre C <lists@peufeu.com>
To: pgsql-performance@postgresql.org, "Josh Berkus" <josh@agliodbs.com>
Subject: Re: Defaulting wal_sync_method to fdatasync on Linux for 9.1?
Date: Sat, 06 Nov 2010 00:39:05 +0100
Message-ID: <op.vlp1bfnweorkce@apollo13> (raw)
In-Reply-To: <4CD4800D.5050504@agliodbs.com>
References: <AANLkTi=n3zxiLNFhbpiN=JPQPwB=BhXf9iepw1KOEs8U@mail.gmail.com>
<4CCDCAA3.4050805@2ndquadrant.com>
<201011010010.28724.andres@anarazel.de>
<4CD472CC.80006@2ndquadrant.com>
<4CD4800D.5050504@agliodbs.com>
>> Fair enough; I'm so used to bumping wal_buffers up to 16MB nowadays that
>> I forget sometimes that people actually run with the default where this
>> becomes an important consideration.
>
> Do you have any testing in favor of 16mb vs. lower/higher?
From some tests I had done some time ago, using separate spindles (RAID1)
for xlog, no battery, on 8.4, with stuff that generates lots of xlog
(INSERT INTO SELECT) :
When using a small wal_buffers, there was a problem when switching from
one xlog file to the next. Basically a fsync was issued, but most of the
previous log segment was still not written. So, postgres was waiting for
the fsync to finish. Of course, the default 64 kB of wal_buffers is
quickly filled up, and all writes wait for the end of this fsync. This
caused hiccups in the xlog traffic, and xlog throughput wassn't nearly as
high as the disks would allow. Sticking a sthetoscope on the xlog
harddrives revealed a lot more random accesses that I would have liked
(this is a much simpler solution than tracing the IOs, lol)
I set wal writer delay to a very low setting (I dont remember which,
perhaps 1 ms) so the walwriter was in effect constantly flushing the wal
buffers to disk. I also used fdatasync instead of fsync. Then I set
wal_buffers to a rather high value, like 32-64 MB. Throughput and
performance were a lot better, and the xlog drives made a much more
"linear-access" noise.
What happened is that, since wal_buffers was larger than what the drives
can write in 1-2 rotations, it could absorb wal traffic during the time
postgres waits for fdatasync / wal segment change, so the inserts would
not have to wait. And lowering the walwriter delay made it write something
on each disk rotation, so that when a COMMIT or segment switch came, most
of the time, the WAL was already synced and there was no wait.
Just my 2 c ;)
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: lists@peufeu.com, josh@agliodbs.com
Subject: Re: Defaulting wal_sync_method to fdatasync on Linux for 9.1?
In-Reply-To: <op.vlp1bfnweorkce@apollo13>
* 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