On Friday, September 20, 2024 11:59 AM Hou, Zhijie/侯 志杰 wrote: > > On Friday, September 20, 2024 10:55 AM Zhijie Hou (Fujitsu) > <houzj.f...@fujitsu.com> wrote: > > On Friday, September 20, 2024 2:49 AM Masahiko Sawada > <sawada.m...@gmail.com> wrote: > > > > > > > > > I think that such a time-based configuration parameter would be a > > > reasonable solution. The current concerns are that it might affect > > > vacuum performance and lead to a similar bug we had with > > vacuum_defer_cleanup_age. > > > > Thanks for the feedback! > > > > I am working on the POC patch and doing some initial performance tests > > on this idea. > > I will share the results after finishing.
Here is a POC patch for vacuum_committs_age idea. The patch adds a GUC
vacuum_committs_age to prevent dead rows from being removed if the age of the
delete transaction (xmax) has not exceeded the vacuum_committs_age threshold.
E.g. , it ensures the row is retained if now() - commit_timestamp_of_xmax <
vacuum_committs_age.
However, please note that the patch is still unfinished due to a few
issues that need to be addressed. For instance: We need to prevent
relfrozenxid/datfrozenxid from being advanced in both aggressive and
non-aggressive vacuum modes. Otherwise, the commit timestamp data is cleaned
up after advancing frozenxid, and we won’t be able to compute the age of a
tuple.
Additionally, the patch has a noticeable performance impact on vacuum
operations when rows in a table are deleted by multiple transactions. Here are
the results of VACUUMing a table after deleting each row in a separate
transaction (total of 10000000 dead rows) and the xmax ages of all the dead
tuples have exceeded the vacuum_committs_age in patched tests (see attachment
for the basic configuration of the tests):
HEAD: Time: 848.637 ms
patched, SLRU 8MB: Time: 1423.915 ms
patched, SLRU 1G: Time: 1310.869 ms
Since we have discussed about an alternative approach that can reliably retain
dead tuples without modifying vacuum process. We plan to shift our focus to
this new approach [1]. I am currently working on another POC patch based on this
new approach and will share it later.
[1]
https://www.postgresql.org/message-id/CAD21AoD%3Dm-YHceYMpsdu0HnGCaezeyVhaCPFxDLHU7aN0wgzqg%40mail.gmail.com
Best Regards,
Hou zj
perftest.conf
Description: perftest.conf
0001-try-to-add-vacuum_committs_age.patch
Description: 0001-try-to-add-vacuum_committs_age.patch
