pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhat is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum
4+ messages / 3 participants
[nested] [flat]
* What is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum
@ 2019-02-27 14:47 Fd Habash <fmhabash@gmail.com>
2019-02-27 16:15 ` Re: What is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum Justin Pryzby <pryzby@telsasoft.com>
2019-02-28 08:44 ` Re: What is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 4+ messages in thread
From: Fd Habash @ 2019-02-27 14:47 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum
2019-02-27 14:47 What is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum Fd Habash <fmhabash@gmail.com>
@ 2019-02-27 16:15 ` Justin Pryzby <pryzby@telsasoft.com>
2019-02-28 13:34 ` RE: What is pg_stat_user_tables Showing NULL for last_autoanalyze &last_autovacuum Fd Habash <fmhabash@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Justin Pryzby @ 2019-02-27 16:15 UTC (permalink / raw)
To: Fd Habash <fmhabash@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: What is pg_stat_user_tables Showing NULL for last_autoanalyze &last_autovacuum
2019-02-27 14:47 What is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum Fd Habash <fmhabash@gmail.com>
2019-02-27 16:15 ` Re: What is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum Justin Pryzby <pryzby@telsasoft.com>
@ 2019-02-28 13:34 ` Fd Habash <fmhabash@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Fd Habash @ 2019-02-28 13:34 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum
2019-02-27 14:47 What is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum Fd Habash <fmhabash@gmail.com>
@ 2019-02-28 08:44 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2019-02-28 08:44 UTC (permalink / raw)
To: Fd Habash <fmhabash@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-02-28 13:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-02-27 14:47 What is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum Fd Habash <fmhabash@gmail.com>
2019-02-27 16:15 ` Justin Pryzby <pryzby@telsasoft.com>
2019-02-28 13:34 ` RE: What is pg_stat_user_tables Showing NULL for last_autoanalyze &last_autovacuum Fd Habash <fmhabash@gmail.com>
2019-02-28 08:44 ` Laurenz Albe <laurenz.albe@cybertec.at>
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