Message-ID: <4F2D5AB4.9060909@fuzzy.cz>
Date: Sat, 04 Feb 2012 17:20:04 +0100
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:9.0) Gecko/20111222 Thunderbird/9.0.1
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: wal_level=archive gives better performance than minimal
 - why?
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>
In-Reply-To: 
 <CAF6yO=0dwwyq_SAShcdq8MYB-5fXjXyaWtS5GEK=Wo5YOcRh9A@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On 4.2.2012 17:04, C=E9dric Villemain wrote:
> Le 3 f=E9vrier 2012 19:48, Robert Haas <robertmhaas@gmail.com> a =E9cri=
t :
>> 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.
>=20
> 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