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