Re: Trigger more frequent autovacuums

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(at)gmail(dot)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
>
>