Re: 9.4 regression

On Thu, Aug 8, 2013 at 4:24 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Thu, Aug 8, 2013 at 04:12:06PM -0500, Jon Nelson wrote:
>> On Thu, Aug 8, 2013 at 2:50 PM, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:
>> > On 08/08/2013 05:28 PM, Jon Nelson wrote:
>> ...
>>
>> > Just an idea - can you check if using a fillfactor different form 100
>> > changes anything
>> >
>> > pgbench -s 20 -p 54320 -d pgb -F 90 -i
>> >
>> >
>> >> pgbench -j 80 -c 80 -T 120 -p 54320 pgb
>> >> pg_ctl -D tt -w stop
>> >
>> > That is, does extending tables and indexes to add updated tuples play
>> > any role here
>>
>> I gave it a go - it didn't make any difference at all.
>> At this point I'm convinced that the issue is a pathological case in
>> ext4. The performance impact disappears as soon as the unwritten
>> extent(s) are written to with real data. Thus, even though allocating
>> files with posix_fallocate is - frequently - orders of magnitude
>> quicker than doing it with write(2), the subsequent re-write can be
>> more expensive. At least, that's what I'm gathering from the various
>> threads. Why this issue didn't crop up in earlier testing and why I
>> can't seem to make test_fallocate do it (even when I modify
>> test_fallocate to write to the newly-allocated file in a mostly-random
>> fashion) has me baffled. Should this feature be reconsidered?
>
> How much slower would it be if we wrote it with zeros after
> posix_fallocate() --- that would still give use single extents. Has
> anyone tested to see if the write without test_fallocate() still gives
> us one extent?

Actually, I did that test - I found that there was no longer a
performance drop and possibly a slight performance benefit.
I couldn't quite parse your last sentence, I assume you mean "does
skipping the posix_fallocate altogether still produce one extent?"
Sadly, I can't really test that properly as the ext4 filesystem I'm
testing on is quite fresh and has lots and lots of free space.

--
Jon