Thread: Statistics tables not being updated anymore
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?
Thanks
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
Thanks
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
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,