Horton

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

Dilip Kumar <dilipbalaut@gmail.com>

View thread View on postgr.es
From: Dilip Kumar <dilipbalaut@gmail.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Robert Haas <robertmhaas@gmail.com>, Andrew Dunstan <andrew@dunslane.net>, Heikki Linnakangas <hlinnaka@iki.fi>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-02-13T06:34:16Z
Lists: pgsql-hackers 
On Sun, Feb 13, 2022 at 10:12 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>

I have done performance testing with different template DB sizes and
different amounts of dirty shared buffers and I think as expected the
bigger the dirty shared buffer the checkpoint approach becomes costly
and OTOH the larger the template DB size the WAL log approach takes
more time.

I think it is very common to have larger shared buffers and of course,
if somebody has configured such a large shared buffer then a good % of
it will be dirty most of the time.  So IMHO in the future, the WAL log
approach is going to be more usable in general.  However, this is just
my opinion, and others may have completely different thoughts and
anyhow we are keeping options for both the approaches so no worry.

Next, I am planning to do some more tests, where we are having pgbench
running and concurrently we do CREATEDB maybe every 1 minute and see
what is the CREATEDB time as well as what is the impact on pgbench
performance.  Because currently I have only measured CREATEDB time but
we must be knowing the impact of createdb on the other system as well.

Test setup:
max_wal_size=64GB
checkpoint_timeout=15min
- CREATE base TABLE of size of Shared Buffers
- CREATE template database and table in it of varying sizes (as per test)
- CHECKPOINT (write out dirty buffers)
- UPDATE 70% of tuple in base table (dirty 70% of shared buffers)
- CREATE database using template db. (Actual test target)

test1:
1 GB shared buffers, template DB size = 6MB, dirty shared buffer=70%
Head: 2341.665 ms
Patch: 85.229 ms

test2:
1 GB shared buffers, template DB size = 1GB, dirty shared buffer=70%
Head: 4044 ms
Patch: 8376 ms

test3:
8 GB shared buffers, template DB size = 1GB, dirty shared buffer=70%
Head: 21398 ms
Patch: 9834 ms

test4:
8 GB shared buffers, template DB size = 10GB, dirty shared buffer=95%
Head: 38574 ms
Patch: 77160 ms

test4:
32 GB shared buffers, template DB size = 10GB, dirty shared buffer=70%
Head: 47656 ms
Patch: 79767 ms

test5:
64 GB shared buffers, template DB size = 1GB, dirty shared buffer=70%
Head: 59151 ms
Patch: 8742 ms

test6:
64 GB shared buffers, template DB size = 50GB, dirty shared buffer=50%
Head: 171614 ms
Patch: 406040 ms

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

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.