Re: wal_level=archive gives better performance than minimal - why?

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