Re: Speedup of relation deletes during recovery

On Tue, Apr 16, 2019 at 10:48 AM Jamison, Kirk <k.jamison@jp.fujitsu.com> wrote:
>
> Hello Fujii-san,
>
> On April 18, 2018, Fujii Masao wrote:
>
> > On Fri, Mar 30, 2018 at 12:18 PM, Tsunakawa, Takayuki <tsunakawa.takay@jp.fujitsu.com> wrote:
> >> Furthermore, TRUNCATE has a similar and worse issue.  While DROP TABLE
> >> scans the shared buffers once for each table, TRUNCATE does that for
> >> each fork, resulting in three scans per table.  How about changing the
> >> following functions
> >>
> >> smgrtruncate(SMgrRelation reln, ForkNumber forknum, BlockNumber
> >> nblocks) DropRelFileNodeBuffers(RelFileNodeBackend rnode, ForkNumber forkNum,
> >>                                            BlockNumber firstDelBlock)
> >>
> >> to
> >>
> >> smgrtruncate(SMgrRelation reln, ForkNumber *forknum, BlockNumber
> >> *nblocks, int nforks) DropRelFileNodeBuffers(RelFileNodeBackend rnode, ForkNumber *forkNum,
> >>                                            BlockNumber *firstDelBlock,
> >> int nforks)
> >>
> >> to perform the scan only once per table?  If there doesn't seem to be a problem,
> >> I think I'll submit the patch next month.  I'd welcome if anyone could do that.
> >
> > Yeah, it's worth working on this problem. To decrease the number of scans of
> > shared_buffers, you would need to change the order of truncations of files and WAL
> > logging. In RelationTruncate(), currently WAL is logged after FSM and VM are truncated.
> > IOW, with the patch, FSM and VM would need to be truncated after WAL logging. You would
> > need to check whether this reordering is valid.
>
> I know it's been almost a year since the previous message, but if you could still
> recall your suggestion above, I'd like to ask question.
> Could you explain your idea a bit further on how would the reordering of WAL writing and
> file truncation possibly reduce the number of shared_buffers scan?

Sorry, I forgot the detail of that my comment. But anyway, you want to
modify smgr_redo(info = XLOG_SMGR_TRUNCATE) so that the number of
scans of shared_buffers to be decreased to one. Right?

IMO it's worth thinking to change smgrtruncate(MAIN_FORK),
FreeSpaceMapTruncateRel() and visibilitymap_truncate() so that
they just mark the relation and blocks as to be deleted, and then
so that they scan shared_buffers at once to invalidate the blocks
at the end of smgr_redo().

Regards,

-- 
Fujii Masao