Thread: pgsql: Make DROP DATABASE command generate less WAL records.
Make DROP DATABASE command generate less WAL records. Previously DROP DATABASE generated as many XLOG_DBASE_DROP WAL records as the number of tablespaces that the database to drop uses. This caused the scans of shared_buffers as many times as the number of the tablespaces during recovery because WAL replay of one XLOG_DBASE_DROP record needs that full scan. This could make the recovery time longer especially when shared_buffers is large. This commit changes DROP DATABASE so that it generates only one XLOG_DBASE_DROP record, and registers the information of all the tablespaces into it. Then, WAL replay of XLOG_DBASE_DROP record needs full scan of shared_buffers only once, and which may improve the recovery performance. Author: Fujii Masao Reviewed-by: Kirk Jamison, Simon Riggs Discussion: https://postgr.es/m/CAHGQGwF8YwNH0ZaL+2wjZPkj+ji9UhC+Z4ScnG97WKtVY5L9iw@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/e6d8069522c8bde8239dd1fedfb4984efa4b3a1a Modified Files -------------- src/backend/access/rmgrdesc/dbasedesc.c | 7 +++- src/backend/commands/dbcommands.c | 67 +++++++++++++++++++++++---------- src/include/commands/dbcommands_xlog.h | 5 ++- 3 files changed, 56 insertions(+), 23 deletions(-)
On Thu, Nov 21, 2019 at 12:11:58PM +0000, Fujii Masao wrote: > Make DROP DATABASE command generate less WAL records. > > Previously DROP DATABASE generated as many XLOG_DBASE_DROP WAL records > as the number of tablespaces that the database to drop uses. This caused > the scans of shared_buffers as many times as the number of the tablespaces > during recovery because WAL replay of one XLOG_DBASE_DROP record needs > that full scan. This could make the recovery time longer especially > when shared_buffers is large. > > This commit changes DROP DATABASE so that it generates only one > XLOG_DBASE_DROP record, and registers the information of all the tablespaces > into it. Then, WAL replay of XLOG_DBASE_DROP record needs full scan of > shared_buffers only once, and which may improve the recovery performance. Fujii-san, you have forgotten to bump XLOG_PAGE_MAGIC in xlog_internal.h? -- Michael
Attachment
On Thu, Nov 21, 2019 at 9:17 PM Michael Paquier <michael@paquier.xyz> wrote: > > On Thu, Nov 21, 2019 at 12:11:58PM +0000, Fujii Masao wrote: > > Make DROP DATABASE command generate less WAL records. > > > > Previously DROP DATABASE generated as many XLOG_DBASE_DROP WAL records > > as the number of tablespaces that the database to drop uses. This caused > > the scans of shared_buffers as many times as the number of the tablespaces > > during recovery because WAL replay of one XLOG_DBASE_DROP record needs > > that full scan. This could make the recovery time longer especially > > when shared_buffers is large. > > > > This commit changes DROP DATABASE so that it generates only one > > XLOG_DBASE_DROP record, and registers the information of all the tablespaces > > into it. Then, WAL replay of XLOG_DBASE_DROP record needs full scan of > > shared_buffers only once, and which may improve the recovery performance. > > Fujii-san, you have forgotten to bump XLOG_PAGE_MAGIC in > xlog_internal.h? Thanks for pointing out that! Since the format of XLOG_DBASE_DROP WAL record was changed, XLOG_PAGE_MAGIC must be bumped. Will do that. Regards, -- Fujii Masao
On Thu, Nov 21, 2019 at 10:03:27PM +0900, Fujii Masao wrote: > Thanks for pointing out that! Since the format of XLOG_DBASE_DROP WAL > record was changed, XLOG_PAGE_MAGIC must be bumped. Will do that. Yep, thanks for fixing. -- Michael