Re: Eager page freeze criteria clarification - Mailing list pgsql-hackers

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