Re: faster ETL / bulk data load for heap tables

On 02-01-2021 08:36, Amit Kapila wrote:
> On Fri, Jan 1, 2021 at 7:37 PM Luc Vlaming <luc(at)swarm64(dot)com> wrote:
>>
>> Hi,
>>
>> In an effort to speed up bulk data loading/transforming I noticed that
>> considerable time is spent in the relation extension lock.
>>
>
> We already do extend the relation in bulk when there is a contention
> on relation extension lock via RelationAddExtraBlocks. I wonder why is
> that not able to address this kind of workload. On a quick look at
> your patch, it seems you are always trying to extend the relation by
> 128 blocks for copy operation after acquiring the lock whereas the
> current mechanism has some smarts where it decides based on the number
> of waiters. Now, it is possible that we should extend by a larger
> number of blocks as compared to what we are doing now but using some
> ad-hoc number might lead to space wastage. Have you tried to fiddle
> with the current scheme of bulk-extension to see if that addresses
> some of the gains you are seeing? I see that you have made quite a few
> other changes that might be helping here but still, it is better to
> see how much bottleneck is for relation extension lock and if that can
> be addressed with the current mechanism rather than changing the
> things in a different way.
>

Hi,

Thanks for looking at the patch!

Yes I tried that. I guess I should have also shared all other things I
have tried before I ended up with these patches.
I've tried to improve RelationAddExtraBlocks to extend the mechanism to
more aggresively allocate blocks, to not put them in the FSM immediately
as this also seemed like a point of contention, extending ReadBufferBI
to allocate several pages in a loop, and a few more variants like this.
To be sure I just tried again a few variants where I made e.g.
extraBlocks=128, 256, etc, and disabled e.g. the FSM code. None of those
grant very big performance gains or actually make it slower, suggesting
the code is parameterized quite well already for the current design.

The main problem is that the relation extension lock is taken to extend
one block at a time, whilst doing (expensive) syscalls like pwrite().
Even though we then put these blocks immediately in the FSM and such,
the bottleneck stays the extension of the file itself, no matter how
many blocks we allocate in a loop in RelationAddExtraBlocks.

Therefore what I've set out to do is:
- make the relation extension lock taken as short as possible.
- reduce the time spent on syscalls as much as possible.

This resulted in a design which hands out blocks to a specific backend
so that everything after the file extension can be done safely without
locks. Given that the current API did not allow this to be specified
properly for now I added extra functions which have just as purpose to
extend the relation so that they could be purpose built for this. I did
not want to suggest this is however the best API there could be. The
current state of this patch is in that sense still very crude. I just
wrote the code I needed to be able to do performance testing. So it is
as you rightfully pointed out quite ad-hoc and not very generic, nor the
right design, has quite some code duplication, etc.

I was at this point mostly looking for feedback on the design/approach.
If this requires a (much) cleaner and more productified patch then I can
arrange that. However I thought to first find out if this approach makes
sense at all before doing more testing to make this more generic.

Kind regards,
Luc