pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovacuum not running properly
7+ messages / 3 participants
[nested] [flat]
* Autovacuum not running properly
@ 2023-08-27 11:08 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Rajesh Kumar @ 2023-08-27 11:08 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Autovacuum not running properly
@ 2023-08-28 13:43 Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Wetmore, Matthew (CTR) @ 2023-08-28 13:43 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
-- 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?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Autovacuum not running properly
@ 2023-08-28 16:34 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
0 siblings, 0 replies; 7+ messages in thread
From: Rajesh Kumar @ 2023-08-28 16:34 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; +Cc: pgsql-admin@lists.postgresql.org
Thanks a lot. I will check
On Mon, 28 Aug 2023, 19:13 Wetmore, Matthew (CTR), <
Matthew.Wetmore@express-scripts.com> wrote:
> -- 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?
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Autovacuum not running properly
@ 2023-09-01 11:16 Luca Ferrari <fluca1978@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Luca Ferrari @ 2023-09-01 11:16 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Sun, Aug 27, 2023 at 2:16 PM Rajesh Kumar
<rajeshkumar.dba09@gmail.com> wrote:
>
> 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.
I bet such tables do not require autovacuum attention, that is their
overall activity is under the thresholds.
Luca
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Autovacuum not running properly
@ 2023-09-04 08:25 Luca Ferrari <fluca1978@gmail.com>
parent: Luca Ferrari <fluca1978@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Luca Ferrari @ 2023-09-04 08:25 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Autovacuum not running properly
@ 2023-09-04 11:43 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Luca Ferrari <fluca1978@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Rajesh Kumar @ 2023-09-04 11:43 UTC (permalink / raw)
To: Luca Ferrari <fluca1978@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
The result I get "amount of tuples inserted" is not very clear. Let's say I
get 10000 which is 10k , in this case, do you mean autovacuum will trigger
for every 10k (inserts+updates+deletes) ?
On Mon, 4 Sep, 2023, 1:56 PM Luca Ferrari, <fluca1978@gmail.com> wrote:
> 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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Autovacuum not running properly
@ 2023-09-04 14:40 Luca Ferrari <fluca1978@gmail.com>
parent: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Luca Ferrari @ 2023-09-04 14:40 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Mon, Sep 4, 2023 at 1:44 PM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
>
> The result I get "amount of tuples inserted" is not very clear. Let's say I get 10000 which is 10k , in this case, do you mean autovacuum will trigger for every 10k (inserts+updates+deletes) ?
Assuming you are referring to the value
autovacuum_vacuum_insert_threshold +
autovacuum_vacuum_insert_scale_factor * reltuples = 10k, then it means
that under 10k inserts, your table will not be vacuumed. With thed
default configuration, essentially you get vacuum running every time
you insert at least 20% of new tuples, and the same is for delete and
updates.
Please see <https://www.postgresql.org/docs/15/runtime-config-autovacuum.html;.
Luca
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-09-04 14:40 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-27 11:08 Autovacuum not running properly Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-08-28 13:43 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-08-28 16:34 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-09-01 11:16 ` Luca Ferrari <fluca1978@gmail.com>
2023-09-04 08:25 ` Luca Ferrari <fluca1978@gmail.com>
2023-09-04 11:43 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2023-09-04 14:40 ` Luca Ferrari <fluca1978@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox