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-14T05:13:43Z
Lists: pgsql-hackers 
On Sun, Feb 13, 2022 at 12:04 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> On Sun, Feb 13, 2022 at 10:12 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:

> 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.

I have done tests with the pgbench as well.  So basically I did not
notice any significant difference in the TPS, I was expecting there
should be some difference due to the checkpoint on the head so maybe I
need to test with more backend maybe.  And createdb time there is a
huge difference. I think this is because template1 db is very small so
patch is getting completed in no time whereas head is taking huge time
because of high dirty shared buffers (due to concurrent pgbench).

config:
echo "logging_collector=on" >> data/postgresql.conf
echo "port = 5432" >> data/postgresql.conf
echo "max_wal_size=64GB" >> data/postgresql.conf
echo "checkpoint_timeout=15min" >> data/postgresql.conf
echo "shared_buffers=32GB" >> data/postgresql.conf

Test:
./pgbench -i -s 1000 postgres
./pgbench -c 32 -j 32 -T 1200 -M prepared postgres >> result.txt
-- Concurrently run below script every 1 mins
CREATE DATABASE mydb log_copied_blocks=true/false;

Results:
- Pgbench TPS: Did not observe any difference head vs patch
- Create db time(very small template):
head:  21000 ms to 42000 ms (at different time)
patch: 80 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.