pgsql: Make DROP DATABASE command generate less WAL records.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

pgsql: Make DROP DATABASE command generate less WAL records.

Fujii Masao-3
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@...

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(-)

Reply | Threaded
Open this post in threaded view
|

Re: pgsql: Make DROP DATABASE command generate less WAL records.

Michael Paquier-2
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

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: pgsql: Make DROP DATABASE command generate less WAL records.

Fujii Masao-2
On Thu, Nov 21, 2019 at 9:17 PM Michael Paquier <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: pgsql: Make DROP DATABASE command generate less WAL records.

Michael Paquier-2
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

signature.asc (849 bytes) Download Attachment