Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset? - Mailing list pgsql-general

From Rihad
Subject Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset?
Date
Msg-id db75baff-dbc7-ee53-6b06-73455428358d@gmail.com
Whole thread Raw
In response to Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset?  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset?
List pgsql-general
On 8/21/23 00:15, Adrian Klaver wrote:
On 8/20/23 12:10, Rihad wrote:
On 8/20/23 20:22, Adrian Klaver wrote:
On 8/18/23 22:35, Rihad wrote:
On 8/17/23 13:01, rihad wrote:

Hi, all. After calling pg_stat_reset all statistics used by autovacuum got zeroed, and started accumulating from scratch. Some tables get acted upon properly, some don't.


Self-replying: yup, it seems there's an arbitrary limit of 100K of n_live_tup after which autovac/analyze kicks in, or it seems so.

To know rather then guess read:

https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM


Sure, I read it before asking.

Taking the first table in the list as an example:


         relname          | n_live_tup | n_dead_tup |   left   | n_mod_since_analyze |  left
--------------------------+------------+------------+----------+---------------------+--------
fooooooooooo             |      32781 |     240663 |  -234057 |               513265 | -509937


n_dead_tup (not the actual value, but some time after calling pg_stat_reset) is much larger than 20% of n_live_tup 32781, and n_mod_since_analyze is much larger than 10% of it.

Yet it is kept unvacuumed and unanalyzed for a long time.

autovacuum_(vacuum|analyze)_threshold is 50.

What am I missing?

Hard to say without seeing the actual settings in postgresql.conf that match:

https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-SCALE-FACTOR


Most importantly:

autovacuum

and

track_counts

https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-COUNTS


They are both on and set as per default. Autovac/analyze continue running on some tables after pg_stat_reset. Just not on all of them, even thought they should judging by live/dead tuples calculation.


foo=> show track_counts;
track_counts  
--------------
on
(1 row)

foo=> show autovacuum;    
autovacuum  
------------
on
(1 row)






There are still many tables waiting for their turn, which is long due.

Although there are some tables having only 60-70 (not 60-70K) n_live_tup that have had autovacuum run on them. Weird.








pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset?
Next
From: Anthony Apollis
Date:
Subject: Loops and Case Statements Involving Dates