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

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