Re: Eager page freeze criteria clarification

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