Re: 8.4 open item: copy performance regression?

Tom Lane wrote:
> Just eyeing the code ... another thing we changed since 8.3 is to enable
> posix_fadvise() calls for WAL. Any of the complaints want to try diking
> out this bit of code (near line 2580 in src/backend/access/transam/xlog.c)?
>
> #if defined(USE_POSIX_FADVISE) && defined(POSIX_FADV_DONTNEED)
> if (!XLogArchivingActive() &&
> (get_sync_bit(sync_method) & PG_O_DIRECT) == 0)
> (void) posix_fadvise(openLogFile, 0, 0, POSIX_FADV_DONTNEED);
> #endif

ok after a bit of bisecting I'm happy to announce the winner of the contest:

http://archives.postgresql.org/pgsql-committers/2008-11/msg00054.php

this patch causes a 25-30% performance regression for WAL logged copy,
however in the WAL bypass case (maybe that was what got tested?) it
results in a 20% performance increase.

the raw numbers using the upthread posted minimal postgresql.conf are:

post patch/wal logged: 4min10s/4min19/4min12
post patch/wal bypass: 1m55s/1m58s/2m00
prepatch/wal logged: 2m55s/3min00/2m59
prepatch/wal bypass: 2m22s/2m18s/2m20s

Stefan