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

On Sun, Feb 13, 2022 at 12:04 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Sun, Feb 13, 2022 at 10:12 AM Dilip Kumar <dilipbalaut(at)gmail(dot)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