Horton

Re: [Proposal] Fully WAL logged CREATE DATABASE - No Checkpoints

Andres Freund <andres@anarazel.de>

View thread View on postgr.es
From: Andres Freund <andres@anarazel.de>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Dilip Kumar <dilipbalaut@gmail.com>, Ashutosh Sharma <ashu.coek88@gmail.com>, Maciek Sakrejda <m.sakrejda@gmail.com>, Bruce Momjian <bruce@momjian.us>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Andrew Dunstan <andrew@dunslane.net>, Heikki Linnakangas <hlinnaka@iki.fi>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-03-30T01:17:57Z
Lists: pgsql-hackers 
Hi,

On 2022-03-29 11:55:05 -0400, Robert Haas wrote:
> I committed v6 instead.

Just noticed that it makes initdb a bit slower / the cluster a bit bigger,
because now there's WAL traffic from creating the databases.  There's an
optimization (albeit insufficient) to reduce WAL traffic in bootstrap mode,
but not for single user mode when the CREATE DATABASEs happen.

In an optimized build, with wal-segsize 1 (the most extreme case) using
FILE_COPY vs WAL_LOG:

perf stat ~/build/postgres/dev-optimize/install/bin/initdb /tmp/initdb/ --wal-segsize=1
WAL_LOG:

            487.58 msec task-clock                #    0.848 CPUs utilized
             2,874      context-switches          #    5.894 K/sec
                 0      cpu-migrations            #    0.000 /sec
            10,209      page-faults               #   20.938 K/sec
     1,550,483,095      cycles                    #    3.180 GHz
     2,537,618,094      instructions              #    1.64  insn per cycle
       492,780,121      branches                  #    1.011 G/sec
         7,384,884      branch-misses             #    1.50% of all branches

       0.575213800 seconds time elapsed

       0.349812000 seconds user
       0.133225000 seconds sys

FILE_COPY:

            476.54 msec task-clock                #    0.854 CPUs utilized
             3,005      context-switches          #    6.306 K/sec
                 0      cpu-migrations            #    0.000 /sec
            10,050      page-faults               #   21.090 K/sec
     1,516,058,200      cycles                    #    3.181 GHz
     2,504,126,907      instructions              #    1.65  insn per cycle
       488,042,856      branches                  #    1.024 G/sec
         7,327,364      branch-misses             #    1.50% of all branches

       0.557934976 seconds time elapsed

       0.360473000 seconds user
       0.112109000 seconds sys


the numbers are similar if repeated.

du -s /tmp/initdb/
WAL_LOG: 35112
FILE_COPY: 29288

So it seems we should specify a strategy in initdb? It kind of makes sense -
we're not going to read anything from those database. And because of the
ringbuffer of 256kB, we'll not even reduce IO meaningfully.

- Andres

Commits

  1. When using the WAL-logged CREATE DATABASE strategy, bulk extend.

  2. Avoid using a fake relcache entry to own an SmgrRelation.

  3. Fix data-corruption hazard in WAL-logged CREATE DATABASE.

  4. initdb: When running CREATE DATABASE, use STRATEGY = WAL_COPY.

  5. Simplify a needlessly-complicated regular expression.

  6. In 020_createdb.pl, change order of command-line arguments.

  7. Add new block-by-block strategy for CREATE DATABASE.

  8. Fix replay of create database records on standby

  9. Refactor code for reading and writing relation map files.

  10. Replace RelationOpenSmgr() with RelationGetSmgr().

  11. Refactor the fsync queue for wider use.