On Thu, Jul 5, 2018 at 5:15 PM Simon Riggs <si...@2ndquadrant.com> wrote: > > 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
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
xlog_dbase_drop_v2.patch
Description: Binary data
