Thread: Trigger more frequent autovacuums

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

    

Hi 

   The more accurate data I've found is tabentry->live_tuples; provides the second version

#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<1000000;
UPDATE 1938700
test=# select 1938700/2289001;
 ?column?
----------
        0
(1 row)

test=# select 1938700/2289001::float;
      ?column?
--------------------
 0.8469633696097119
(1 row)

test=#



test=# select count(*) from join1;
  count
---------
 2289001
(1 row)
test=# update join1 set name=md5(now()::text) where id<=80000;
UPDATE 159901
test=# select 159901/2289001::float;
      ?column?
---------------------
 0.06985623859491542
(1 row)


test=# select * from pg_stat_all_tables where relname='join1';
-[ RECORD 1 ]----------+------------------------------
relid                  | 16385
schemaname             | public
relname                | join1
seq_scan               | 17
last_seq_scan          | 2025-03-07 15:34:02.793659+08
seq_tup_read           | 14994306
idx_scan               | 7
last_idx_scan          | 2025-03-07 15:34:23.404788+08
idx_tup_fetch          | 500281
n_tup_ins              | 2289001
n_tup_upd              | 2268604
n_tup_del              | 0
n_tup_hot_upd          | 399
n_tup_newpage_upd      | 2268205
n_live_tup             | 2286701
n_dead_tup             | 159901
n_mod_since_analyze    | 159901
n_ins_since_vacuum     | 0
last_vacuum            | 2025-03-06 18:18:11.318419+08
last_autovacuum        | 2025-03-07 15:25:53.055576+08
last_analyze           | 2025-03-06 18:18:11.424253+08
last_autoanalyze       | 2025-03-07 15:25:53.456656+08
vacuum_count           | 3
autovacuum_count       | 3
analyze_count          | 2
autoanalyze_count      | 4
total_vacuum_time      | 205
total_autovacuum_time  | 2535
total_analyze_time     | 203
total_autoanalyze_time | 1398

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


test=# \x
Expanded display is on.
test=# select (n_live_tup)/(n_live_tup+n_dead_tup)::float from pg_stat_all_tables where relname='join1';
-[ RECORD 1 ]----------------
?column? | 0.8774142777358045

test=# select *  from pg_stat_all_tables where relname='join1';
-[ RECORD 1 ]----------+------------------------------
relid                  | 16385
schemaname             | public
relname                | join1
seq_scan               | 17
last_seq_scan          | 2025-03-07 15:34:02.793659+08
seq_tup_read           | 14994306
idx_scan               | 8
last_idx_scan          | 2025-03-07 15:46:38.331795+08
idx_tup_fetch          | 660182
n_tup_ins              | 2289001
n_tup_upd              | 2428505
n_tup_del              | 0
n_tup_hot_upd          | 424
n_tup_newpage_upd      | 2428081
n_live_tup             | 2289001
n_dead_tup             | 319802
n_mod_since_analyze    | 0
n_ins_since_vacuum     | 0
last_vacuum            | 2025-03-06 18:18:11.318419+08
last_autovacuum        | 2025-03-07 15:25:53.055576+08
last_analyze           | 2025-03-06 18:18:11.424253+08
last_autoanalyze       | 2025-03-07 15:47:35.950932+08
vacuum_count           | 3
autovacuum_count       | 3
analyze_count          | 2
autoanalyze_count      | 5
total_vacuum_time      | 205
total_autovacuum_time  | 2535
total_analyze_time     | 203
total_autoanalyze_time | 1770

test=#
tail -n 1000 postgresql-Fri_17.csv |grep join1
2025-03-07 17:30:12.782 +08,,,755739,,67cabca4.b881b,3,,2025-03-07 17:30:12 +08,2017/2,0,DEBUG,00000,"vacthresh: 457850.218750,anlthresh: 228950.109375, the join1 has 2289001.000000 reltuples, pcnt_unfrozen: 1.000000, pcnt_visibletuples: 0.877414 ",,,,,,,,,"","autovacuum worker",,0
2025-03-07 17:31:12.803 +08,,,756028,,67cabce0.b893c,3,,2025-03-07 17:31:12 +08,2003/4,0,DEBUG,00000,"vacthresh: 457850.218750,anlthresh: 228950.109375, the join1 has 2289001.000000 reltuples, pcnt_unfrozen: 1.000000, pcnt_visibletuples: 0.877414 ",,,,,,,,,"","autovacuum worker",,0
2025-03-07 17:32:12.822 +08,,,756405,,67cabd1c.b8ab5,3,,2025-03-07 17:32:12 +08,2006/4,0,DEBUG,00000,"vacthresh: 457850.218750,anlthresh: 228950.109375, the join1 has 2289001.000000 reltuples, pcnt_unfrozen: 1.000000, pcnt_visibletuples: 0.877414 ",,,,,,,,,"","autovacuum worker",,0
2025-03-07 17:33:12.842 +08,,,757026,,67cabd58.b8d22,3,,2025-03-07 17:33:12 +08,2009/4,0,DEBUG,00000,"vacthresh: 457850.218750,anlthresh: 228950.109375, the join1 has 2289001.000000 reltuples, pcnt_unfrozen: 1.000000, pcnt_visibletuples: 0.877414 ",,,,,,,,,"","autovacuum worker",,0

On Fri, Mar 7, 2025 at 2:22 PM wenhui qiu <qiuwenhuifx@gmail.com> wrote:

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