On 4 June 2018 at 17:46, Fujii Masao <masao.fu...@gmail.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