Re: Parallel Inserts in CREATE TABLE AS

On Thu, May 27, 2021 at 7:37 PM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>
> On Thu, May 27, 2021 at 9:53 AM Bharath Rupireddy
> <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> > > One idea to find this out could be that we have three counters for
> > > each worker which counts the number of times each worker extended the
> > > relation in bulk, the number of times each worker extended the
> > > relation by one block, the number of times each worker gets the page
> > > from FSM. It might be possible that with this we will be able to
> > > figure out why there is a difference between your and Hou-San's
> > > results.
> >
> > Yeah, that helps. And also, the time spent in
> > LockRelationForExtension, ConditionalLockRelationForExtension,
> > GetPageWithFreeSpace and RelationAddExtraBlocks too can give some
> > insight.
> >
> > My plan is to have a patch with above info added in (which I will
> > share it here so that others can test and see the results too) and run
> > the "case 4" where there's a regression seen on my system.
>
> I captured below information with the attached patch
> 0001-test-times-and-block-counts.patch applied on top of CTAS v23
> patch set. Testing details are attached in the file named "test".
> Total time spent in LockRelationForExtension
> Total time spent in GetPageWithFreeSpace
> Total time spent in RelationAddExtraBlocks
> Total number of times extended the relation in bulk
> Total number of times extended the relation by one block
> Total number of blocks added in bulk extension
> Total number of times getting the page from FSM
>

In your results, the number of pages each process is getting from FSM
is not matching with the number of blocks added. I think we need to
increment 'fsm_hit_count' in RecordAndGetPageWithFreeSpace as well
because that is also called and the process can get a free page via
the same. The other thing to check via debugger is when one worker
adds the blocks in bulk does another parallel worker gets all those
blocks. You can achieve that by allowing one worker (say worker-1) to
extend the relation in bulk and then let it wait and allow another
worker (say worker-2) to proceed and see if it gets all the pages
added by worker-1 from FSM. You need to keep the leader also waiting
or not perform any operation.

--
With Regards,
Amit Kapila.