Re: Diagnosing outdated auto analyze dates...

Thank you... so to make it explicit, this table has 15169372 rows,
autovacuum_analyze_scale_factor is .05, and autovacuum_analyze_threshold is
10, so: 15169372 * .05 + 10, which is 758478.60 -- so that means
auto-analyze will only fire when that number of rows has been deleted or
updated?

On Wed, Apr 21, 2021 at 1:48 PM Keith Fiske <keith(dot)fiske(at)crunchydata(dot)com>
wrote:

>
>
> On Wed, Apr 21, 2021 at 4:06 PM Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
> wrote:
>
>> A last_autoanalyze date in pg_stat_all_tables for a table we update every
>> day is 3/26, another is 3/13 -- I feel strongly somehow these tables should
>> be auto-analyzed and auto-vacuumed more often, and am wondering where to
>> look first.
>>
>> My autovacuum_analyze_threshold is 10, my autovacuum_vacuum_threshold is
>> 25.
>>
>> How can I better understand why these tables are not more frequently
>> being auto-analyzed and vacuumed?
>>
>> Thank you.
>>
>> --
>> Wells Oliver
>> wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
>>
>
> The threshold settings are only part of the calculation that determines
> when autovacuum kicks in. There's also autovacuum_vacuum_scale_factor
> and autovacuum_analyze_scale_factor. The scale factors say that when this
> fraction of the table has been changed (updates/deletes) then run the
> relevant operation (vacuum or analyze).
>
> The threshold values are added on top of the scale factor values. So, what
> determines whether your analyze kicks in is actually
>
> (autovacuum_analyze_scale_factor * total row
> count) + autovacuum_analyze_threshold
>
> The reason it's like this is to avoid autovacuum kicking in excessively on
> very small tables when a small % changes. Ex. 10 rows on a 100 row table
> constantly kicking in autovacuum when scale factor is set to .10. So add on
> 100-500 for the threshold so autovacuum kicks in when a good number of rows
> actually changes.
>
> If you want a specific number of row changes to cause autovacuum to kick
> in, you can set the scale factor to zero for that specific table. I only
> really find that necessary myself on rather large tables though (10s of
> millions of rows).
>
> --
> Keith Fiske
> Senior Database Engineer
> Crunchy Data - http://crunchydata.com
>

--
Wells Oliver
wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>