Re: Speedup of relation deletes during recovery - Mailing list pgsql-hackers

From Fujii Masao
Subject Re: Speedup of relation deletes during recovery
Date
Msg-id CAHGQGwFD4Xb7jvQ5k9=bwfQ+rm3H=uu=75SBX+ovWCev0zbCLw@mail.gmail.com
Whole thread Raw
In response to RE: Speedup of relation deletes during recovery  ("Jamison, Kirk" <k.jamison@jp.fujitsu.com>)
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: New vacuum option to do only freezing
Next
From: Tomas Vondra
Date:
Subject: Re: Zedstore - compressed in-core columnar storage