Trigger more frequent autovacuums

From: wenhui qiu <qiuwenhuifx(at)gmail(dot)com>
To: Melanie Plageman <melanieplageman(at)gmail(dot)com>, Nathan Bossart <nathandbossart(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Japin Li <japinli(at)hotmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>
Subject: Trigger more frequent autovacuums
Date: 2025-03-07 06:22:54
Message-ID: CAGjGUAKKUDBBYoJjY2AuynGL-P09rAdr6CTuDMfgpa__oPTcig@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

HI Nathan Bossart Melanie Plageman

Firstly, congratulations on the submission of this path:
https://commitfest.postgresql.org/patch/5320/

vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor *
reltuples;
These three calculations have already been optimised for two of them, and
with this patch, we have the key data pcnt_unfrozen, I think we can also
consider applying it to the vacthresh and anlthresh calculations, and I've
added a new pcnt_unrelallvisible parameter with reference to pcnt_unfrozen,
so I'm not sure if it's a good idea for me to use it. I'd like to hear your
opinions on this.
#Here's a simple test I did
test=# select count(*) from join1;
count
---------
2289001
(1 row)

test=# update join1 set name=md5(now()::text) where id<=20000;
UPDATE 70001
test=#

2025-03-07 14:03:33.968 +08,,,607191,,67ca8c35.943d7,2,,2025-03-07 14:03:33
+08,2005/2,0,DEBUG,00000,"vacthresh: 222674.750000,anlthresh: 11371.118164,
the j
oin1 has 2291275.000000 reltuples, pcnt_unfrozen: 0.485810,
pcnt_unrelallvisible: 0.049410 ",,,,,,,,,"","autovacuum worker",,0

Attachment Content-Type Size
Optimising-the-vacuum-algorithm.diff application/octet-stream 2.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message RECHTÉ Marc 2025-03-07 06:39:33 Re: Logical replication timeout
Previous Message Andrei Lepikhov 2025-03-07 06:03:53 Re: Removing unneeded self joins