Statistics tables not being updated anymore

On 7/1/19 10:27 AM, Ron wrote:

Hi.

v9.6.9

Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore.
Specifically, all counter fields are 0, and date fields are blank.

The first thing I checked was postgresql.conf (but it hasn't been
modified since December 2018), and track_activities is turned on. Also,
I connect as user "postgres", so it's not a privileges problem.

Where else should I look?

Is it an autovacuum problem?

What happens if you do a manual ANALYZE?

What shows up in the pg_stats view?

track_activities                    | on
track_activity_query_size           | 1024
track_commit_timestamp              | off
track_counts                        | on
track_functions                     | none
track_io_timing                     | off

Thanks

--
Angular momentum makes the world go 'round.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 7/1/19 1:07 PM, Adrian Klaver wrote:

On 7/1/19 10:27 AM, Ron wrote:

Hi.

v9.6.9

Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore.
Specifically, all counter fields are 0, and date fields are blank.

The first thing I checked was postgresql.conf (but it hasn't been
modified since December 2018), and track_activities is turned on. Also, I
connect as user "postgres", so it's not a privileges problem.

Where else should I look?

Is it an autovacuum problem?

What happens if you do a manual ANALYZE?

We run manual ANALYZE jobs every day, and still the columns are blank and zero,

What shows up in the pg_stats view?

Only the pg_catalog tables have values in, for example, elem_count_histogram.

track_activities                    | on
track_activity_query_size           | 1024
track_commit_timestamp              | off
track_counts                        | on
track_functions                     | none
track_io_timing                     | off

Thanks

--
Angular momentum makes the world go 'round.

--
Angular momentum makes the world go 'round.

Ron <ronljohnsonjr@gmail.com> writes:

Hi.

v9.6.9

Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
pg_statio_*_tables and pg_statio_*_indexes aren't being updated
anymore. Specifically, all counter fields are 0, and date fields are
blank.

Perhaps your stats collector is dead, blocked or dropping packets.

Check your server logs for anything related.

The first thing I checked was postgresql.conf (but it hasn't been
modified since December 2018), and track_activities is turned on.
Also, I connect as user "postgres", so it's not a privileges problem.

Where else should I look?

track_activities                    | on           
track_activity_query_size           | 1024         
track_commit_timestamp              | off          
track_counts                        | on           
track_functions                     | none         
track_io_timing                     | off          

Thanks

--
Angular momentum makes the world go 'round.

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net

Ron <ronljohnsonjr@gmail.com> writes:

Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore.
Specifically, all counter fields are 0, and date fields are blank.

Does anything show up in the postmaster log when you try to query
one of those views?

It seems like something must be wedged either in the stats collector
process or in backends' communication with that process. Hard to say
what on the basis of this evidence though.

If you can do a postmaster restart without too much trouble, that
might resolve the issue.

regards, tom lane

On 7/1/19 11:24 AM, Ron wrote:

On 7/1/19 1:07 PM, Adrian Klaver wrote:

On 7/1/19 10:27 AM, Ron wrote:

Hi.

v9.6.9

Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
pg_statio_*_tables and pg_statio_*_indexes aren't being updated
anymore. Specifically, all counter fields are 0, and date fields are
blank.

The first thing I checked was postgresql.conf (but it hasn't been
modified since December 2018), and track_activities is turned on.
Also, I connect as user "postgres", so it's not a privileges problem.

Where else should I look?

Is it an autovacuum problem?

What happens if you do a manual ANALYZE?

We run manual ANALYZE jobs every day, and still the columns are blank
and zero,

What shows up in the pg_stats view?

Only the pg_catalog tables have values in, for example,
elem_count_histogram.

Suggestions:

1) For starters I would go back to the source pg_statistic and see if
the values actually change.

2) Make sure that some code is not issuing a SET that is overriding the
postgresql.conf settings. Or that someone has not changed an include file.

3) Look at pg_stat_activity to see if there is actually any activity
recorded.

track_activities                    | on
track_activity_query_size           | 1024
track_commit_timestamp              | off
track_counts                        | on
track_functions                     | none
track_io_timing                     | off

Thanks

--
Angular momentum makes the world go 'round.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 7/1/19 1:48 PM, Tom Lane wrote:

Ron <ronljohnsonjr@gmail.com> writes:

Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore.
Specifically, all counter fields are 0, and date fields are blank.

Does anything show up in the postmaster log when you try to query
one of those views?

No.

It seems like something must be wedged either in the stats collector
process or in backends' communication with that process. Hard to say
what on the basis of this evidence though.

If you can do a postmaster restart without too much trouble, that
might resolve the issue.

I'll try and get that approved.

--
Angular momentum makes the world go 'round.

On 7/1/19 1:48 PM, Adrian Klaver wrote:

On 7/1/19 11:24 AM, Ron wrote:

On 7/1/19 1:07 PM, Adrian Klaver wrote:

On 7/1/19 10:27 AM, Ron wrote:

Hi.

v9.6.9

Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
pg_statio_*_tables and pg_statio_*_indexes aren't being updated
anymore. Specifically, all counter fields are 0, and date fields are
blank.

The first thing I checked was postgresql.conf (but it hasn't been
modified since December 2018), and track_activities is turned on. Also,
I connect as user "postgres", so it's not a privileges problem.

Where else should I look?

Is it an autovacuum problem?

What happens if you do a manual ANALYZE?

We run manual ANALYZE jobs every day, and still the columns are blank and
zero,

What shows up in the pg_stats view?

Only the pg_catalog tables have values in, for example,
elem_count_histogram.

Suggestions:

1) For starters I would go back to the source pg_statistic and see if the
values actually change.

I don't see any values in it, either.

2) Make sure that some code is not issuing a SET that is overriding the
postgresql.conf settings. Or that someone has not changed an include file.

None.

3) Look at pg_stat_activity to see if there is actually any activity
recorded.

We do see that being updated.

--
Angular momentum makes the world go 'round.

On 7/1/19 12:30 PM, Ron wrote:

On 7/1/19 1:48 PM, Tom Lane wrote:

Ron <ronljohnsonjr@gmail.com> writes:

Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore.
Specifically, all counter fields are 0, and date fields are blank.

Does anything show up in the postmaster log when you try to query
one of those views?

No.

Have you gone through the logs looking for errors/warnings about the
stats collector?

It seems like something must be wedged either in the stats collector
process or in backends' communication with that process.  Hard to say
what on the basis of this evidence though.

If you can do a postmaster restart without too much trouble, that
might resolve the issue.

I'll try and get that approved.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 7/1/19 2:43 PM, Adrian Klaver wrote:

On 7/1/19 12:30 PM, Ron wrote:

On 7/1/19 1:48 PM, Tom Lane wrote:

Ron <ronljohnsonjr@gmail.com> writes:

Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore.
Specifically, all counter fields are 0, and date fields are blank.

Does anything show up in the postmaster log when you try to query
one of those views?

No.

Have you gone through the logs looking for errors/warnings about the stats
collector?

Yes, but there's nothing.

--
Angular momentum makes the world go 'round.

On 7/1/19 1:38 PM, Ron wrote:

On 7/1/19 2:43 PM, Adrian Klaver wrote:

On 7/1/19 12:30 PM, Ron wrote:

On 7/1/19 1:48 PM, Tom Lane wrote:

Ron <ronljohnsonjr@gmail.com> writes:

Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
pg_statio_*_tables and pg_statio_*_indexes aren't being updated
anymore.
Specifically, all counter fields are 0, and date fields are blank.

Does anything show up in the postmaster log when you try to query
one of those views?

No.

Have you gone through the logs looking for errors/warnings about the
stats collector?

Yes, but there's nothing.

And ps ax | grep postgres shows?:

postgres: stats collector process

If so then I guess you are down to the suggestions upstream that
something is stuck in the stats collectors craw.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 7/1/19 5:20 PM, Adrian Klaver wrote:

On 7/1/19 1:38 PM, Ron wrote:

On 7/1/19 2:43 PM, Adrian Klaver wrote:

On 7/1/19 12:30 PM, Ron wrote:

On 7/1/19 1:48 PM, Tom Lane wrote:

Ron <ronljohnsonjr@gmail.com> writes:

Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore.
Specifically, all counter fields are 0, and date fields are blank.

Does anything show up in the postmaster log when you try to query
one of those views?

No.

Have you gone through the logs looking for errors/warnings about the
stats collector?

Yes, but there's nothing.

And ps ax | grep postgres shows?:

postgres: stats collector process

Yup, it exists.

If so then I guess you are down to the suggestions upstream that something
is stuck in the stats collectors craw.

We're going to try and restart it tonight.

--
Angular momentum makes the world go 'round.

Ron <ronljohnsonjr@gmail.com> writes:

On 7/1/19 2:43 PM, Adrian Klaver wrote:

Have you gone through the logs looking for errors/warnings about the stats
collector?

Yes, but there's nothing.

One theory as to what broke is that somebody messed with your system's
packet filtering (firewall) rules, and now the kernel is discarding
statistics packets that backends are trying to send to the stats
collector. If that's the case, then when you restart the postmaster
there will be some bleats in the log about it, because the stats
collector checks for this problem at startup (but never again :-().

This theory doesn't completely explain your problem, because it
only explains why no new stats data is appearing, not why you
can't still see the stats state as it was before data transmission
stopped.

The latter part might be explained if you'd done pg_stat_reset()
in hopes of clearing the problem --- except that I think the
transmission of the reset command is done over the same IP socket
that stats data goes through, so how'd it get through if that's
being blocked?

Anyway, bottom line is to pay close attention to the postmaster
log when you restart.

regards, tom lane

Ron wrote:

v9.6.9

Statistics views like pg_stat_*_tables, pg_stat_*_indexes, pg_statio_*_tables and pg_statio_*_indexes
aren't being updated anymore. Specifically, all counter fields are 0, and date fields are blank.

The first thing I checked was postgresql.conf (but it hasn't been modified since December 2018),
and track_activities is turned on. Also, I connect as user "postgres", so it's not a privileges problem.

Where else should I look?

track_activities | on
track_activity_query_size | 1024
track_commit_timestamp | off
track_counts | on
track_functions | none
track_io_timing | off

I have seen something like that before, and described the incident in
https://www.cybertec-postgresql.com/en/stale-statistics-cause-table-bloat/

What happened there was that during system startup, PostgreSQL was started
and created the statistics collector UDP socket on IPv6 localhost.

Later in the boot sequence, IPv6 was disabled, so no more statistics
could be collected. Since it is an UDP socket, there were no errors.

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

On 7/1/19 1:48 PM, Tom Lane wrote:

Ron <ronljohnsonjr@gmail.com> writes:

Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore.
Specifically, all counter fields are 0, and date fields are blank.

Does anything show up in the postmaster log when you try to query
one of those views?

It seems like something must be wedged either in the stats collector
process or in backends' communication with that process. Hard to say
what on the basis of this evidence though.

If you can do a postmaster restart without too much trouble, that
might resolve the issue.

That did, in fact, solve the problem.

--
Angular momentum makes the world go 'round.

On 7/2/19 6:28 AM, Ron wrote:

On 7/1/19 1:48 PM, Tom Lane wrote:

Ron <ronljohnsonjr@gmail.com> writes:

Statistics views like pg_stat_*_tables, pg_stat_*_indexes,
pg_statio_*_tables and pg_statio_*_indexes aren't being updated anymore.
Specifically, all counter fields are 0, and date fields are blank.

Does anything show up in the postmaster log when you try to query
one of those views?

It seems like something must be wedged either in the stats collector
process or in backends' communication with that process.  Hard to say
what on the basis of this evidence though.

If you can do a postmaster restart without too much trouble, that
might resolve the issue.

That did, in fact, solve the problem.

It got it working again, it did not actually answer what caused the
issue. The problem could still reappear.

--
Adrian Klaver
adrian.klaver@aklaver.com

Hello,

FYI, check_pgactivity has a service to detect frozen stat collector:
https://github.com/OPMDG/check_pgactivity#user-content-stat_snapshot_age-9.5

We added this service after a customer has deactivated IPv6 that broke stat
collector.

Regards,