Re: Recovery performance of DROP DATABASE with many tablespaces

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

Cheers

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services