Re: Recovery performance of DROP DATABASE with many tablespaces

On Thu, Jul 5, 2018 at 5:15 PM Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On 4 June 2018 at 17:46, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> > Hi,
> >
> > My colleague encountered the problem that WAL replay took a long time
> > in the standby with large shared_buffers when he dropped the database
> > using many tablespaces. As far as I read the code, this happens because
> > DROP DATABASE generates as many XLOG_DBASE_DROP WAL records as
> > the number of tablespaces that the database to drop uses,
> > and then WAL replay of one XLOG_DBASE_DROP record causes full scan of
> > shared_buffers. That is, DROP DATABASE causes the scans of shared_buffers
> > as many times as the number of the tablespaces during recovery.
> >
> > Since the first scan caused by the first XLOG_DBASE_DROP record invalidates
> > all the pages related to the database to drop, in shared_buffers,
> > the subsequent scans by the subsequent records seem basically useless.
> > So I'd like to change the code so that we can avoid such subsequent
> > unnecessary scans, to reduce the recovery time of DROP DATABASE.
>
> +1
>
> > Generally the recovery performance of DROP DATABASE is not critical
> > for many users. But unfortunately my colleague's project might need to
> > sometimes drop the database using multiple tablespaces, for some reasons.
> > So, if the fix is not so complicated, I think that it's worth applying that.
>
> Agreed
>
> > The straight approach to avoid such unnecessary scans is to change
> > DROP DATABASE so that it generates only one XLOG_DBASE_DROP record,
> > and register the information of all the tablespace into it. Then, WAL replay
> > of XLOG_DBASE_DROP record scans shared_buffers once and deletes
> > all tablespaces. POC patch is attached.
>
> Seems clear on read of patch, but not tested it.
>
> Please replace tablespace_num with ntablespaces so its clearer and
> consistent with other other WAL records

Thanks for the review! I changed the patch that way. Also I rebased it
on master.
Attached is the latest version of the patch.

I will add this patch to next CommitFest.

Regards,

--
Fujii Masao