Hi Dilip, On Sun, Feb 13, 2022 at 12:04 PM Dilip Kumar <dilipbal...@gmail.com> wrote: > > On Sun, Feb 13, 2022 at 10:12 AM Dilip Kumar <dilipbal...@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 >
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.
