Re: fallocate / posix_fallocate for new WAL file creation (etc...)

On Tue, Jun 11, 2013 at 11:08 AM, Stefan Drees <stefan@drees.name> wrote:
> On 2013-11.06 17:28, Jon Nelson wrote:
>>
>> There hasn't been much activity here recently.  I'm curious, then, if
>> there are questions that I can answer.
>> It may be useful to summarize some things here:
>>
>> - the purpose of the patch is to use posix_fallocate when creating new
>> WAL files, because it's (usually) much quicker
>> - using posix_fallocate is *one* system call versus 2048 calls to write(2)
>> - additionally, using posix_fallocate /guarantees/ that the filesystem
>> has space for the WAL file (by spec)
>> - reportedly (difficult to test or prove), using posix_fallocate *may*
>> reduce file fragmentation
>> - the (limited) testing I've done bears this out: the more new WAL
>> file creation there is, the more the improvement. Once the number of
>> WAL files reaches a constant point, there does not appear to be either
>> a positive or a negative performance impact. This is as expected.
>> - a test program (C) was also written and used which creates,
>> allocates, and then writes to files as fast as possible. This test
>> program also shows the expected performance benefits.
>> - the performance benefits range from a few percent up to about 15 percent
>
>
> tried the test program of the patch at least a bit.
>
> Retrieved it from:
> http://www.postgresql.org/message-id/attachment/29088/test_fallocate.c
>
> on an oldish linux box (Kernel 2.6.32, x86_64) following
>     $ gcc -o test_fallocate test_fallocate.c
> a short
>     $ ./test_fallocate foo 1 1
> yields:
> without posix_fallocate: 1 open/close iterations, 1 rewrite in 26.1993s
> with posix_fallocate: 1 open/close iterations, 1 rewrite in 13.3299s
>
> on another box (Kernel 3.2.0, x86_64) same procedure yields:
> without posix_fallocate: 1 open/close iterations, 1 rewrite in 19.1972s
> with posix_fallocate: 1 open/close iterations, 1 rewrite in 9.9280s
>
> Note, when trying gcc -O2 test_fallocate.c fails to compile with:
>
> In file included from /usr/include/fcntl.h:252:0,
>                  from test_fallocate.c:3:
> In function ‘open’,
>     inlined from ‘main’ at test_fallocate.c:68:16:
> /usr/include/x86_64-linux-gnu/bits/fcntl2.h:51:24: error: call to
> ‘__open_missing_mode’ declared with attribute error: open with O_CREAT in
> second argument needs 3 arguments

It's understood that posix_fallocate is faster at this -- the question
on the table is 'does this matter in context of postgres?'.
Personally I think this patch should go in regardless -- the concerns
made IMNSHO are specious.

merlin