Re: Trigger more frequent autovacuums

Sorry ,A wrong version of debug pcnt_visibletuples ，kindly please check the
v3 attachment

On Fri, Mar 7, 2025 at 5:37 PM wenhui qiu <qiuwenhuifx(at)gmail(dot)com> wrote:

> 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
>>
>>
>