Обсуждение: Autovacuum not running properly

-- Find current setting (this is at database level)

select * from pg_settings  where name in ('autovacuum','autovacuum_analyze_scale_factor','autovacuum_analyze_threshold','autovacuum_vacuum_scale_factor');

select current_setting('autovacuum_vacuum_scale_factor') as "analyze_scale_factor",current_setting('autovacuum_vacuum_threshold') as "vacuum_threshold";

select current_setting('autovacuum_analyze_scale_factor') as "analyze_scale_factor", current_setting('autovacuum_analyze_threshold') as "analyze_threshold";

-- Note: The smaller number = more aggressive = vacuum more frequence

-- Current:

-- autovacuum_analyze_scale_factor = 0.05    

-- autovacuum_vacuum_scale_factor = 0.1      

-- Fine Tune at table level = ALTER TABLE mytable SET (autovacuum_analyze_scale_factor = 0.02);

ALTER TABLE your_schema.your_table SET (autovacuum_enabled = true,autovacuum_analyze_scale_factor = 0.002,autovacuum_vacuum_scale_factor = 0.001);

-- Put it back to use global setting

ALTER TABLE your_schema.your_table RESET (autovacuum_enabled,autovacuum_analyze_scale_factor,autovacuum_vacuum_scale_factor);

 

From: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Sent: Sunday, August 27, 2023 4:09 AM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Autovacuum not running properly

 

Hi

 

Why Autovacuum is not running on specific tables while it works on other tables and how to resolve this.

 

And for some tables the last autovacuum done is 2days ago whereas most are till today.

 

 

How to analyze autovacuum and take action?

On Fri, Sep 1, 2023 at 6:32 PM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
>
> In that case how to be sure of autovacuum is not necessary for this table? On what basis? Do we have any calculation

The idea should be the number of "manipulated tuples" exceed the
thresholds. For example, in the case of insert workload, something
like:

SELECT current_setting( 'autovacuum_vacuum_insert_threshold' )::numeric
+ current_setting( 'autovacuum_vacuum_insert_scale_factor' )::numeric
* reltuples
FROM pg_class
WHERE relname = <your relation> and relkind = 'r';

will give you the amount of tuples that, once inserted, will trigger
an autovacuum. Therefore, unless your table receive more tuples than
the above, autovacuum will not consider vacuuming the table (thn
there's autoanalyze, that is another story but the reasoning is
similar).

Long story short: if your table is pretty much static, than it is
clear that autovacuum is not goint to vacuum over an over the same
data.

Luca