Thread: What is pg_stat_user_tables Showing NULL for last_autoanalyze &last_autovacuum
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?
----------------
Thank you
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 orregular 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
Re: What is pg_stat_user_tables Showing NULL for last_autoanalyze &last_autovacuum
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
RE: What is pg_stat_user_tables Showing NULL for last_autoanalyze&last_autovacuum
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: Justin Pryzby
Sent: Wednesday, February 27, 2019 11:15 AM
To: Fd Habash
Cc: pgsql-performance@postgresql.org
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