From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: wal_level=archive gives better performance than minimal - why?
Date: Sat, 04 Feb 2012 17:20:04 +0100
Message-ID: <4F2D5AB4.9060909@fuzzy.cz> (raw)
In-Reply-To: <CAF6yO=0dwwyq_SAShcdq8MYB-5fXjXyaWtS5GEK=Wo5YOcRh9A@mail.gmail.com>
References: <4F0F6A02.3000403@fuzzy.cz>
<4F14A649.5060501@2ndQuadrant.com>
<4F14C101.60607@fuzzy.cz>
<4F1C9695.4000304@fuzzy.cz>
<CA+TgmobJygfBe89oqqhP45Wk=Z0u=sNReXzcUK4KJRXpUDTdqA@mail.gmail.com>
<CAF6yO=0dwwyq_SAShcdq8MYB-5fXjXyaWtS5GEK=Wo5YOcRh9A@mail.gmail.com>
On 4.2.2012 17:04, Cédric Villemain wrote:
> Le 3 février 2012 19:48, Robert Haas <robertmhaas@gmail.com> a écrit :
>> 2012/1/22 Tomas Vondra <tv@fuzzy.cz>:
>>> That's suspiciously similar to the checkpoint timeout (which was set to
>>> 4 minutes), but why should this matter for minimal WAL level and not for
>>> archive?
>>
>> I went through and looked at all the places where we invoke
>> XLogIsNeeded(). When XLogIsNeeded(), we:
>>
>> 1. WAL log creation of the _init fork of an unlogged table or an index
>> on an unlogged table (otherwise, an fsync is enough)
>> 2. WAL log index builds
>> 3. WAL log changes to max_connections, max_prepared_xacts,
>> max_locks_per_xact, and/or wal_level
>> 4. skip calling posix_fadvise(POSIX_FADV_DONTNEED) when closing a WAL file
>> 5. skip supplying O_DIRECT when writing WAL, if wal_sync_method is
>> open_sync or open_datasync
>> 6. refuse to create named restore points
>> 7. WAL log CLUSTER
>> 8. WAL log COPY FROM into a newly created/truncated relation
>> 9. WAL log ALTER TABLE .. SET TABLESPACE
>> 9. WAL log cleanup info before doing an index vacuum (this one should
>> probably be changed to happen only in HS mode)
>> 10. WAL log SELECT INTO
>>
>> It's hard to see how generating more WAL could cause a performance
>> improvement, unless there's something about full page flushes being
>> more efficient than partial page flushes or something like that. But
>> none of the stuff above looks likely to happen very often anyway. But
>> items #4 and #5 on that list like things that could potentially be
>> causing a problem - if WAL files are being reused regularly, then
>> calling POSIX_FADV_DONTNEED on them could represent a regression. It
>> might be worth compiling with POSIX_FADV_DONTNEED undefined and see
>> whether that changes anything.
>
> it should be valuable to have the kernel version and also confirm the
> same behavior happens with XFS.
The kernel is 3.1.5, more precisely the "uname -a" gives this:
Linux rimmer 3.1.5-gentoo #1 SMP PREEMPT Sun Dec 25 14:11:19 CET 2011
x86_64 Intel(R) Core(TM) i5-2500K CPU @ 3.30GHz GenuineIntel GNU/Linux
I plan to rerun the test with various settings, I'll add there XFS
results (so far everything was on EXT4) and I'll post an update to this
thread.
Tmoas
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: tv@fuzzy.cz
Subject: Re: wal_level=archive gives better performance than minimal - why?
In-Reply-To: <4F2D5AB4.9060909@fuzzy.cz>
* 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