Re: Eager page freeze criteria clarification - Mailing list pgsql-hackers
From | Peter Geoghegan |
---|---|
Subject | Re: Eager page freeze criteria clarification |
Date | |
Msg-id | CAH2-WznmFZ0mx-JmPVpWmQ2ng3-dfLHCXCZM5JDGpO8T4wO-ZA@mail.gmail.com Whole thread Raw |
In response to | Re: Eager page freeze criteria clarification (Andres Freund <andres@anarazel.de>) |
Responses |
Re: Eager page freeze criteria clarification
|
List | pgsql-hackers |
On Thu, Sep 7, 2023 at 9:45 PM Andres Freund <andres@anarazel.de> wrote: > I.e. setting an, otherwise unmodified, page all-visible won't trigger an FPI > if checksums are disabled, but will FPI with checksums enabled. I think that's > a substantial difference in WAL volume for insert-only workloads... Note that all RDS Postgres users get page-level checksums. Overall, the FPI trigger mechanism is going to noticeably improve performance characteristics for many users. Simple and crude though it is. > 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... If FREEZE_PAGE WAL volume is really what holds back further high level improvements in this area, then it can be worked on directly -- it's not a fixed cost. It wouldn't be particularly difficult, in fact. These are records that still mostly consist of long runs of contiguous page offset numbers. They're ideally suited for compression using some kind of simple variant of run length encoding. The freeze plan deduplication stuff in 16 made a big difference, but it's still not very hard to improve matters here. -- Peter Geoghegan
pgsql-hackers by date: