On Thu, Sep 29, 2022 at 2:36 AM Robert Haas <robertmhaas@gmail.com> wrote:
> 2. WAL Size. Block references in the WAL are by RelFileLocator, so if
> you make RelFileLocators bigger, WAL gets bigger. We'd have to test
> the exact impact of this, but it seems a bit scary
I have done some testing around this area to see the impact on WAL
size especially when WAL sizes are smaller, with a very simple test
with insert/update/delete I can see around an 11% increase in WAL size
[1] then I did some more test with pgbench with smaller scale
factor(1) there I do not see a significant increase in the WAL size
although it increases WAL size around 1-2%. [2].
[1]
checkpoint;
do $$
declare
lsn1 pg_lsn;
lsn2 pg_lsn;
diff float;
begin
select pg_current_wal_lsn() into lsn1;
CREATE TABLE test(a int);
for counter in 1..1000 loop
INSERT INTO test values(1);
UPDATE test set a=a+1;
DELETE FROM test where a=1;
end loop;
DROP TABLE test;
select pg_current_wal_lsn() into lsn2;
select pg_wal_lsn_diff(lsn2, lsn1) into diff;
raise notice '%', diff/1024;
end; $$;
wal generated head: 66199.09375 kB
wal generated patch: 73906.984375 kB
wal-size increase: 11%
[2]
./pgbench -i postgres
./pgbench -c1 -j1 -t 30000 -M prepared postgres
wal generated head: 30780 kB
wal generated patch: 31284 kB
wal-size increase: ~1-2%
I have done further analysis to know why on pgbench workload the wal
size is increasing by 1-2%. So with waldump I could see that wal size
per transaction size increased from 566 (on head) to 590 (with patch),
so that is around 4% but when we see total wal size difference after
30k transaction then it is just 1-2%
and I think that is because there would be other records which are not
impacted like FPI
Conclusion: So as suspected with very small WAL sizes with a very
targeted test case we can see a significant 11% increase in WAL size
but with pgbench kind of workload the increase in WAL size is very
less.
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
