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

Hi Dilip,

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

Is it possible to see the WAL size generated by these two statements:
UPDATE 70% of the tuple in the base table (dirty 70% of the shared
buffers) && CREATE database using template DB (Actual test target).
Just wanted to know if it can exceed the max_wal_size of 64GB. Also,
is it possible to try with minimal wal_level? Sorry for asking you
this, I could try it myself but I don't have any high level system to
try it.

--
With Regards,
Ashutosh Sharma.