Re: Speedup of relation deletes during recovery

On Fri, Mar 30, 2018 at 12:18 PM, Tsunakawa, Takayuki
<tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:
> From: Fujii Masao [mailto:masao(dot)fujii(at)gmail(dot)com]
>> When multiple relations are deleted at the same transaction, the files of
>> those relations are deleted by one call to smgrdounlinkall(), which leads
>> to scan whole shared_buffers only one time. OTOH, during recovery,
>> smgrdounlink() (not smgrdounlinkall()) is called for each file to delete,
>> which leads to scan shared_buffers multiple times.
>> Obviously this can cause to increase the WAL replay time very much especially
>> when shared_buffers is huge.
>>
>> To alleviate this situation, I'd like to propose to change the recovery
>> so that it also calls smgrdounlinkall() only one time to delete multiple
>> relation files. Patch attached. Thought?
>
> Nice catch. As Horiguchi-san and Michael already commented, the patch looks good.
>
> As a related improvement, the following proposal is effective for shortening WAL replay time of DROP TABLE (and possibly TRUNCATE as well.) How should we proceed with this?
>
> https://www.postgresql.org/message-id/A1CF58A8CBA14341B3F3AC6A468F18454545E4F3@g01jpexmbyt23
>
>
> 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.

Regards,

--
Fujii Masao