Thread: Statistics tables not being updated anymore

Statistics tables not being updated anymore

From
Ron
Date:
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?

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.

Re: Statistics tables not being updated anymore

From
Adrian Klaver
Date:
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



Re: Statistics tables not being updated anymore

From
Ron
Date:
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.



Re: Statistics tables not being updated anymore

From
Jerry Sievers
Date:
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

Re: Statistics tables not being updated anymore

From
Tom Lane
Date:
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



Re: Statistics tables not being updated anymore

From
Adrian Klaver
Date:
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



Re: Statistics tables not being updated anymore

From
Ron
Date:
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.



Re: Statistics tables not being updated anymore

From
Ron
Date:
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.



Re: Statistics tables not being updated anymore

From
Adrian Klaver
Date:
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



Re: Statistics tables not being updated anymore

From
Ron
Date:
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.



Re: Statistics tables not being updated anymore

From
Adrian Klaver
Date:
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



Re: Statistics tables not being updated anymore

From
Ron
Date:
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.



Re: Statistics tables not being updated anymore

From
Tom Lane
Date:
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



Re: Statistics tables not being updated anymore

From
Laurenz Albe
Date:
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




Re: Statistics tables not being updated anymore

From
Ron
Date:
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.



Re: Statistics tables not being updated anymore

From
Adrian Klaver
Date:
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



Re: Statistics tables not being updated anymore

From
Adrien Nayrat
Date:
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,


Attachment