Re: faster ETL / bulk data load for heap tables - Mailing list pgsql-hackers

On 02-01-2021 08:36, Amit Kapila wrote:
> On Fri, Jan 1, 2021 at 7:37 PM Luc Vlaming <luc@swarm64.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