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

Le 3 février 2012 19:48, Robert Haas <robertmhaas(at)gmail(dot)com> a écrit :
> 2012/1/22 Tomas Vondra <tv(at)fuzzy(dot)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.

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation