What is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

What is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum

Fd Habash

I have been able to locate four google search results with the same inquiry. What’ve been able to understand is …

 

  1. If auto-vaccum is working as expected, stats collector does not nullify these values as part of a startup sequence or regular Maitenance. If a relation gets auto[vacuumed|analyzed], the timestamps should remain.
  2. A database engine crash or restart with ‘immediate’ option will cause the timestamps to nullify.
  3. Table never qualified for vacuuming based on auto-vacuum settings.

 

I can rule out all three scenarios above, but I still see null values. What else could be at play here?

 

 

 

----------------
Thank you

 

Reply | Threaded
Open this post in threaded view
|

Re: What is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum

Justin Pryzby
On Wed, Feb 27, 2019 at 09:47:13AM -0500, Fd Habash wrote:
> I have been able to locate four google search results with the same inquiry. What’ve been able to understand is …
>
> 1. If auto-vaccum is working as expected, stats collector does not nullify these values as part of a startup sequence or regular Maitenance. If a relation gets auto[vacuumed|analyzed], the timestamps should remain.
> 2. A database engine crash or restart with ‘immediate’ option will cause the timestamps to nullify.
> 3. Table never qualified for vacuuming based on auto-vacuum settings.

Can you give an example ?

If it's an empty inheritence parent (relkind=r), then it won't trigger
autovacuum/analyze thresholds (but you should analyze it manually).

Note that relkind=p "partitioned" tables don't have entries at all.
https://www.postgresql.org/message-id/flat/20180503141430.GA28019%40telsasoft.com

If it's never DELETEd from, then it won't trigger autovacuum (but may trigger
autoanalyze).

Justin

Reply | Threaded
Open this post in threaded view
|

Re: What is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum

Laurenz Albe
In reply to this post by Fd Habash
On Wed, 2019-02-27 at 09:47 -0500, Fd Habash wrote:
> I have been able to locate four google search results with the same inquiry. What’ve been able to understand is …
>  
> If auto-vaccum is working as expected, stats collector does not nullify these values as part of a
> startup sequence or regular Maitenance. If a relation gets auto[vacuumed|analyzed], the timestamps should remain.
> A database engine crash or restart with ‘immediate’ option will cause the timestamps to nullify.
> Table never qualified for vacuuming based on auto-vacuum settings.
>  
> I can rule out all three scenarios above, but I still see null values. What else could be at play here?

The obvious suspicion is that autovacuum starts, but cannot finish because it either
cannot keep up with the change rate or gives up because it is blocking a concurrent
session.

What is "n_live_tup" and "n_dead_tup" in "pg_stat_user_tables" for these tables?
Are there any autovacuum workers running currently?

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com


Reply | Threaded
Open this post in threaded view
|

RE: What is pg_stat_user_tables Showing NULL for last_autoanalyze &last_autovacuum

Fd Habash
In reply to this post by Justin Pryzby

Thank you …

 

Are the calculations for triggering autovacuum dependent upon statistics generated by auto-anaylyze. In other words, if autoanalyze does not run at all, will autovac be able to run its math for threshold (updates & deletes) & scale factor (table rows) to do its thing?

 

My understanding from the documentation is that it does not need autoanalyze stats.

 

Thanks

 

----------------
Thank you

 

From: [hidden email]
Sent: Wednesday, February 27, 2019 11:15 AM
To: [hidden email]
Cc: [hidden email]
Subject: Re: What is pg_stat_user_tables Showing NULL for last_autoanalyze &last_autovacuum

 

On Wed, Feb 27, 2019 at 09:47:13AM -0500, Fd Habash wrote:

> I have been able to locate four google search results with the same inquiry. What’ve been able to understand is …

>

> 1. If auto-vaccum is working as expected, stats collector does not nullify these values as part of a startup sequence or regular Maitenance. If a relation gets auto[vacuumed|analyzed], the timestamps should remain.

> 2. A database engine crash or restart with ‘immediate’ option will cause the timestamps to nullify.

> 3. Table never qualified for vacuuming based on auto-vacuum settings.

 

Can you give an example ?

 

If it's an empty inheritence parent (relkind=r), then it won't trigger

autovacuum/analyze thresholds (but you should analyze it manually).

 

Note that relkind=p "partitioned" tables don't have entries at all.

https://www.postgresql.org/message-id/flat/20180503141430.GA28019%40telsasoft.com

 

If it's never DELETEd from, then it won't trigger autovacuum (but may trigger

autoanalyze).

 

Justin