Hi,
On 2023-09-07 21:45:22 -0700, Andres Freund wrote:
> In contrast to that, freezing will almost always trigger an FPI (except for
> empty pages, but we imo ought to stop setting empty pages all frozen [1]).
>
>
> Yep, a quick experiment confirms that:
>
> DROP TABLE IF EXISTS foo;
> CREATE TABLE foo AS SELECT generate_series(1, 10000000);
> CHECKPOINT;
> VACUUM (VERBOSE) foo;
>
> checksums off: WAL usage: 44249 records, 3 full page images, 2632091 bytes
> checksums on: WAL usage: 132748 records, 44253 full page images, 388758161
> bytes
>
>
> I initially was confused by the 3x wal records - I was expecting 2x. The
> reason is that with checksums on, we emit an FPI during the visibility check,
> which then triggers the current heuristic for opportunistic freezing. The
> saving grace is that WAL volume is completely dominated by the FPIs:
>
> Type N (%) Record
> size (%) FPI size (%) Combined size (%)
> ---- - ---
> ----------- --- -------- --- -------------
> ---
> XLOG/FPI_FOR_HINT 44253 ( 33.34)
> 2168397 ( 7.84) 361094232 (100.00) 363262629 ( 93.44)
> Transaction/INVALIDATION 1 ( 0.00)
> 78 ( 0.00) 0 ( 0.00) 78 ( 0.00)
> Standby/INVALIDATIONS 1 ( 0.00)
> 90 ( 0.00) 0 ( 0.00) 90 ( 0.00)
> Heap2/FREEZE_PAGE 44248 ( 33.33)
> 22876120 ( 82.72) 0 ( 0.00) 22876120 ( 5.88)
> Heap2/VISIBLE 44248 ( 33.33)
> 2610642 ( 9.44) 16384 ( 0.00) 2627026 ( 0.68)
> Heap/INPLACE 1 ( 0.00)
> 188 ( 0.00) 0 ( 0.00) 188 ( 0.00)
> --------
> -------- -------- --------
> Total 132752
> 27655515 [7.11%] 361110616 [92.89%] 388766131 [100%]
>
> In realistic tables, where rows are wider than a single int, FPI_FOR_HINT
> dominates even further, as the FREEZE_PAGE would be smaller if there weren't
> 226 tuples on each page...
The above is not a great demonstration of the overhead of setting all-visible,
as the FPIs are triggered via FPI_FOR_HINTs, independent of setting
all-visible. Adding "SELECT count(*) FROM foo" before the checkpoint sets them
earlier and results in:
checksum off:
WAL usage: 44249 records, 3 full page images, 2627915 bytes
Type N (%) Record size
(%) FPI size (%) Combined size (%)
---- - --- -----------
--- -------- --- ------------- ---
Transaction/INVALIDATION 1 ( 0.00) 78
( 0.00) 0 ( 0.00) 78 ( 0.00)
Standby/INVALIDATIONS 1 ( 0.00) 90
( 0.00) 0 ( 0.00) 90 ( 0.00)
Heap2/VISIBLE 44248 ( 99.99) 2610642
( 99.99) 16384 ( 95.15) 2627026 ( 99.96)
Heap/INPLACE 1 ( 0.00) 53
( 0.00) 836 ( 4.85) 889 ( 0.03)
-------- --------
-------- --------
Total 44251 2610863
[99.34%] 17220 [0.66%] 2628083 [100%]
checksums on:
WAL usage: 44252 records, 44254 full page images, 363935830 bytes
Type N (%) Record size
(%) FPI size (%) Combined size (%)
---- - --- -----------
--- -------- --- ------------- ---
XLOG/FPI_FOR_HINT 3 ( 0.01) 147
( 0.01) 24576 ( 0.01) 24723 ( 0.01)
Transaction/INVALIDATION 1 ( 0.00) 78
( 0.00) 0 ( 0.00) 78 ( 0.00)
Standby/INVALIDATIONS 1 ( 0.00) 90
( 0.00) 0 ( 0.00) 90 ( 0.00)
Heap2/VISIBLE 44248 ( 99.99) 2831882
( 99.99) 361078336 ( 99.99) 363910218 ( 99.99)
Heap/INPLACE 1 ( 0.00) 53
( 0.00) 836 ( 0.00) 889 ( 0.00)
-------- --------
-------- --------
Total 44254 2832250
[0.78%] 361103748 [99.22%] 363935998 [100%]
Moving the hint bit setting to before the checkpoint also "avoids" the
freezing.
Greetings,
Andres Freund
