Subject: Postgres Stale Statistics

From: Nikhil Shetty <[email-masked]>
Date: Wed, 27 Apr 2022 16:38:59 +0530

Hi,

We are getting below WARNING on one of the standby instances. Not sure what
caused it but to resolve it we tried restarting the database instances but
it is still not working

WARNING - using stale statistics instead of current ones because stats
collector is not responding

Postgresql version - 11.7

Any other option to resolve this? We are thinking of building the standby
again but what if the WARNING is for a primary database instance and a
restart won't solve it?

Thanks and Regards,

Nikhil

---

From: Adelino Silva <[email-masked]>
Date: Wed, 27 Apr 2022 12:46:43 +0000

Hi,

Found this thread that explains the warning.
using stale statistics instead of current ones because stats collector is not responding
https://www.postgresql.org/message-id/[email-masked]
[https://www.postgresql.org/media-archives/img/about/press/elephant.png]<https://www.postgresql.org/message-id/[email-masked]>
PostgreSQL: Re: using stale statistics instead of current ones because stats collector is not responding<https://www.postgresql.org/message-id/[email-masked]>
Hi, On Tue, 2016-03-08 at 16:18 -0800, Tory M Blue wrote: > No hits on the intratubes on this. > …
www.postgresql.org

Regards,

Adelino Silva

________________________________
From: Nikhil Shetty <[email-masked]>
Sent: Wednesday, April 27, 2022 12:08 PM
To: Pgsql-admin <[email-masked]>
Subject: [EXTERNAL] Postgres Stale Statistics

Hi, We are getting below WARNING on one of the standby instances. Not sure what caused it but to resolve it we tried restarting the database instances but it is still not working WARNING - using stale statistics instead of current ones because
ZjQcmQRYFpfptBannerStart
This Message Is From an External Sender
This message came from outside your organization.

ZjQcmQRYFpfptBannerEnd
Hi,

We are getting below WARNING on one of the standby instances. Not sure what caused it but to resolve it we tried restarting the database instances but it is still not working

WARNING - using stale statistics instead of current ones because stats collector is not responding

Any other option to resolve this? We are thinking of building the standby again but what if the WARNING is for a primary database instance and a restart won't solve it?

Thanks and Regards,

Nikhil

---

From: Nikhil Shetty <[email-masked]>
Date: Wed, 27 Apr 2022 19:19:51 +0530

Hi Adelino,

I had gone through that thread before, we cannot move the stats to RAM as
of now.

Thanks,
Nikhil

On Wed, Apr 27, 2022 at 6:16 PM Adelino Silva <[email-masked]>
wrote:

> <https://www.postgresql.org/message-id/[email-masked]>
> PostgreSQL: Re: using stale statistics instead of current ones because
> stats collector is not responding
> <https://www.postgresql.org/message-id/[email-masked]>
> ------------------------------
> *From:* Nikhil Shetty <[email-masked]>
> *Sent:* Wednesday, April 27, 2022 12:08 PM
> *To:* Pgsql-admin <[email-masked]>
> *Subject:* [EXTERNAL] Postgres Stale Statistics
>
> Hi, We are getting below WARNING on one of the standby instances. Not sure
> what caused it but to resolve it we tried restarting the database instances
> but it is still not working WARNING - using stale statistics instead of
> current ones because
> ZjQcmQRYFpfptBannerStart
> We are getting below WARNING on one of the standby instances. Not sure
> what caused it but to resolve it we tried restarting the database instances
> but it is still not working
>
>

---

From: Jeff Janes <[email-masked]>
Date: Wed, 27 Apr 2022 10:07:30 -0400

On Wed, Apr 27, 2022 at 7:09 AM Nikhil Shetty <[email-masked]>
wrote:

> Any other option to resolve this?
>

If it is always present (not just when IO is overwhelmed) maybe it has an
internal firewall blocking UDP on 127.0.0.1.

Cheers,

Jeff

---

From: Adelino Silva <[email-masked]>
Date: Wed, 27 Apr 2022 14:55:56 +0000

One possible cause for this problem is that the statistics collector process is bound to an IP:port which is not responding.
See the following thread discussion.

https://stackoverflow.com/questions/46008372/using-stale-statistics-instead-of-current-ones
[https://cdn.sstatic.net/Sites/stackoverflow/Img/[email-masked]?v=73d79a89bded]<https://stackoverflow.com/questions/46008372/using-stale-statistics-instead-of-current-ones>
Using stale statistics instead of current ones - Stack Overflow<https://stackoverflow.com/questions/46008372/using-stale-statistics-instead-of-current-ones>
Teams. Q&A for work. Connect and share knowledge within a single location that is structured and easy to search. Learn more
stackoverflow.com

Regards,

Adelino Silva

________________________________
From: Nikhil Shetty <[email-masked]>
Sent: Wednesday, April 27, 2022 2:49 PM
To: Adelino Silva <[email-masked]>
Cc: Pgsql-admin <[email-masked]>
Subject: [EXTERNAL] Re: Postgres Stale Statistics

Hi Adelino, I had gone through that thread before, we cannot move the stats to RAM as of now. Thanks, Nikhil On Wed, Apr 27, 2022 at 6:16 PM Adelino Silva <[email-masked]> wrote: Hi, Found this thread that explains the warning.
ZjQcmQRYFpfptBannerStart
This Message Is From an External Sender
This message came from outside your organization.

ZjQcmQRYFpfptBannerEnd
Hi Adelino,

I had gone through that thread before, we cannot move the stats to RAM as of now.

Thanks,
Nikhil

On Wed, Apr 27, 2022 at 6:16 PM Adelino Silva <[email-masked]<mailto:[email-masked]>> wrote:
Hi,

Found this thread that explains the warning.
using stale statistics instead of current ones because stats collector is not responding
https://www.postgresql.org/message-id/[email-masked]<https://www.postgresql.org/message-id/[email-masked]>
[https://www.postgresql.org/media-archives/img/about/press/elephant.png]<https://www.postgresql.org/message-id/[email-masked]>
PostgreSQL: Re: using stale statistics instead of current ones because stats collector is not responding<https://www.postgresql.org/message-id/[email-masked]>
Hi, On Tue, 2016-03-08 at 16:18 -0800, Tory M Blue wrote: > No hits on the intratubes on this. > …
www.postgresql.org<http://www.postgresql.org>

Regards,

Adelino Silva

________________________________
From: Nikhil Shetty <[email-masked]<mailto:[email-masked]>>
Sent: Wednesday, April 27, 2022 12:08 PM
To: Pgsql-admin <[email-masked]<mailto:[email-masked]>>
Subject: [EXTERNAL] Postgres Stale Statistics

ZjQcmQRYFpfptBannerEnd
Hi,

Thanks and Regards,

Nikhil

---

From: Nikhil Shetty <[email-masked]>
Date: Wed, 27 Apr 2022 21:06:44 +0530

Hi Adelino,

I went through the article and I see there is no issue with IPv6 in our
case, it is using IPv4.

I used strace and found 'Resource temporarily unavailable' error though,
not sure what this means, does this mean there is an issue with disk I/O?

strace: Process 5134 attached

epoll_wait(3, [{EPOLLIN, {u32=31860224, u64=31860224}}], 1, -1) = 1

close(3)                                = 0

recvfrom(10, "\2\0\0\0\230\0\0\0\7@\0\0\1\0\0\0\5\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"...,
1000, 0, NULL, NULL) = 152

recvfrom(10, 0x7ffeeb967fa0, 1000, 0, NULL, NULL) = -1 EAGAIN (Resource
temporarily unavailable)

epoll_create1(EPOLL_CLOEXEC)            = 3

epoll_ctl(3, EPOLL_CTL_ADD, 11, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=31860176,
u64=31860176}}) = 0

epoll_ctl(3, EPOLL_CTL_ADD, 7, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=31860200,
u64=31860200}}) = 0

epoll_ctl(3, EPOLL_CTL_ADD, 10, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=31860224,
u64=31860224}}) = 0

epoll_wait(3, [{EPOLLIN, {u32=31860224, u64=31860224}}], 1, -1) = 1

close(3)                                = 0

recvfrom(10, "\2\0\0\0\250\3\0\0\7@\0\0\10\0\0\0\1\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"...,
1000, 0, NULL, NULL) = 936

recvfrom(10,
"\2\0\0\0\250\3\0\0\0\0\0\0\10\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"...,
1000, 0, NULL, NULL) = 936

recvfrom(10, "\2\0\0\0x\1\0\0\7@\0\0\3\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"...,
1000, 0, NULL, NULL) = 376

recvfrom(10, 0x7ffeeb967fa0, 1000, 0, NULL, NULL) = -1 EAGAIN (Resource
temporarily unavailable)

epoll_create1(EPOLL_CLOEXEC)            = 3

epoll_ctl(3, EPOLL_CTL_ADD, 11, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=31860176,
u64=31860176}}) = 0

epoll_ctl(3, EPOLL_CTL_ADD, 7, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=31860200,
u64=31860200}}) = 0

epoll_ctl(3, EPOLL_CTL_ADD, 10, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=31860224,
u64=31860224}}) = 0

epoll_wait(3, [{EPOLLIN, {u32=31860224, u64=31860224}}], 1, -1) = 1

close(3)

Regards,

Nikhil

On Wed, Apr 27, 2022 at 8:25 PM Adelino Silva <[email-masked]>
wrote:

> One possible cause for this problem is that the statistics collector
> process is bound to an IP:port which is not responding.
> <https://stackoverflow.com/questions/46008372/using-stale-statistics-instead-of-current-ones>
> Using stale statistics instead of current ones - Stack Overflow
> <https://stackoverflow.com/questions/46008372/using-stale-statistics-instead-of-current-ones>
> Teams. Q&A for work. Connect and share knowledge within a single location
> that is structured and easy to search. Learn more
> stackoverflow.com
>
> Regards,
>
> Adelino Silva
>
> *Sent:* Wednesday, April 27, 2022 2:49 PM
> *To:* Adelino Silva <[email-masked]>
> *Cc:* Pgsql-admin <[email-masked]>
> *Subject:* [EXTERNAL] Re: Postgres Stale Statistics
>
> Hi Adelino, I had gone through that thread before, we cannot move the
> stats to RAM as of now. Thanks, Nikhil On Wed, Apr 27, 2022 at 6:16 PM
> Adelino Silva <[email-masked]> wrote: Hi, Found this thread
> that explains the warning.
> ZjQcmQRYFpfptBannerStart

---

From: Adelino Silva <[email-masked]>
Date: Thu, 28 Apr 2022 09:22:35 +0000

Hi Nikhil,

About the EAGAIN (Resource temporarily unavailable).
UDP is a stateless protocol, unlike TCP which is connection oriented. The recvfrom() code will not know whether or not the sender has closed its socket, it only knows whether or not there is data waiting to be read. According to the man page for recvfrom on Linux:

    If no messages are available at the socket, the receive calls wait for a message to arrive, unless the socket is nonblocking (see fcntl(2)) in which case the value -1 is returned and the external variable errno set to EAGAIN.

may you need to explore other option like disk saturation.
using stale statistics instead of current ones because stats collector is not responding<https://opensourcedbtech.com/2018/04/03/using-stale-statistics-instead-of-current-ones-because-stats-collector-is-not-responding/>

Regards,

Adelino Silva
Sent: Wednesday, April 27, 2022 4:36 PM

Hi Adelino, I went through the article and I see there is no issue with IPv6 in our case, it is using IPv4. I used strace and found 'Resource temporarily unavailable' error though, not sure what this means, does this mean there is an
ZjQcmQRYFpfptBannerStart
This Message Is From an External Sender
This message came from outside your organization.

ZjQcmQRYFpfptBannerEnd
Hi Adelino,

I went through the article and I see there is no issue with IPv6 in our case, it is using IPv4.

I used strace and found 'Resource temporarily unavailable' error though, not sure what this means, does this mean there is an issue with disk I/O?

close(3)                                = 0

recvfrom(10, "\2\0\0\0\230\0\0\0\7@\0\0\1\0\0\0\5\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 1000, 0, NULL, NULL) = 152

recvfrom(10, 0x7ffeeb967fa0, 1000, 0, NULL, NULL) = -1 EAGAIN (Resource temporarily unavailable)

epoll_ctl(3, EPOLL_CTL_ADD, 11, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=31860176, u64=31860176}}) = 0

epoll_ctl(3, EPOLL_CTL_ADD, 7, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=31860200, u64=31860200}}) = 0

epoll_ctl(3, EPOLL_CTL_ADD, 10, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=31860224, u64=31860224}}) = 0

close(3)                                = 0

recvfrom(10, "\2\0\0\0\250\3\0\0\7@\0\0\10\0\0\0\1\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 1000, 0, NULL, NULL) = 936

recvfrom(10, "\2\0\0\0\250\3\0\0\0\0\0\0\10\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 1000, 0, NULL, NULL) = 936

recvfrom(10, "\2\0\0\0x\1\0\0\7@\0\0\3\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 1000, 0, NULL, NULL) = 376

recvfrom(10, 0x7ffeeb967fa0, 1000, 0, NULL, NULL) = -1 EAGAIN (Resource temporarily unavailable)

epoll_ctl(3, EPOLL_CTL_ADD, 11, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=31860176, u64=31860176}}) = 0

epoll_ctl(3, EPOLL_CTL_ADD, 7, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=31860200, u64=31860200}}) = 0

epoll_ctl(3, EPOLL_CTL_ADD, 10, {EPOLLIN|EPOLLERR|EPOLLHUP, {u32=31860224, u64=31860224}}) = 0

close(3)

Regards,

Nikhil

On Wed, Apr 27, 2022 at 8:25 PM Adelino Silva <[email-masked]<mailto:[email-masked]>> wrote:
One possible cause for this problem is that the statistics collector process is bound to an IP:port which is not responding.
See the following thread discussion.

https://stackoverflow.com/questions/46008372/using-stale-statistics-instead-of-current-ones<https://stackoverflow.com/questions/46008372/using-stale-statistics-instead-of-current-ones>
[https://cdn.sstatic.net/Sites/stackoverflow/Img/[email-masked]?v=73d79a89bded]<https://stackoverflow.com/questions/46008372/using-stale-statistics-instead-of-current-ones>
Using stale statistics instead of current ones - Stack Overflow<https://stackoverflow.com/questions/46008372/using-stale-statistics-instead-of-current-ones>
Teams. Q&A for work. Connect and share knowledge within a single location that is structured and easy to search. Learn more
stackoverflow.com<http://stackoverflow.com>

Regards,

Adelino Silva

________________________________
From: Nikhil Shetty <[email-masked]<mailto:[email-masked]>>
Sent: Wednesday, April 27, 2022 2:49 PM
To: Adelino Silva <[email-masked]<mailto:[email-masked]>>
Cc: Pgsql-admin <[email-masked]<mailto:[email-masked]>>
Subject: [EXTERNAL] Re: Postgres Stale Statistics

Hi Adelino, I had gone through that thread before, we cannot move the stats to RAM as of now. Thanks, Nikhil On Wed, Apr 27, 2022 at 6:16 PM Adelino Silva <[email-masked]<mailto:[email-masked]>> wrote: Hi, Found this thread that explains the warning.
ZjQcmQRYFpfptBannerStart
This Message Is From an External Sender
This message came from outside your organization.

ZjQcmQRYFpfptBannerEnd
Hi Adelino,

Thanks,
Nikhil

Regards,

Adelino Silva

ZjQcmQRYFpfptBannerEnd
Hi,

Thanks and Regards,

Nikhil

---

From: Nikhil Shetty <[email-masked]>
Date: Sun, 8 May 2022 12:12:06 +0530

Hi Adelino,

> UDP is a stateless protocol, unlike TCP which is connection oriented. The
> recvfrom() code will not know whether or not the sender has closed its
> socket, it only knows whether or not there is data waiting to be read.
> According to the man page for recvfrom on Linux:
>     If no messages are available at the socket, the receive calls wait for
> a message to arrive, unless the socket is nonblocking (see fcntl(2)) in
> which case the value -1 is returned and the external variable errno set to
> EAGAIN.

Are you saying the message '-1 EAGAIN (Resource temporarily unavailable)'
is normal?

> <https://opensourcedbtech.com/2018/04/03/using-stale-statistics-instead-of-current-ones-because-stats-collector-is-not-responding/>

There is no disk saturation from what we see.

Thanks,
Nikhil

On Thu, Apr 28, 2022 at 2:52 PM Adelino Silva <[email-masked]>
wrote:

> UDP is a stateless protocol, unlike TCP which is connection oriented. The
> recvfrom() code will not know whether or not the sender has closed its
> socket, it only knows whether or not there is data waiting to be read.
> According to the man page for recvfrom on Linux:
>
>     If no messages are available at the socket, the receive calls wait for
> a message to arrive, unless the socket is nonblocking (see fcntl(2)) in
> which case the value -1 is returned and the external variable errno set to
> EAGAIN.
>
>
> <https://opensourcedbtech.com/2018/04/03/using-stale-statistics-instead-of-current-ones-because-stats-collector-is-not-responding/>
>
> Regards,
>
> Adelino Silva
> *Sent:* Wednesday, April 27, 2022 4:36 PM
> Hi Adelino, I went through the article and I see there is no issue with
> IPv6 in our case, it is using IPv4. I used strace and found 'Resource
> temporarily unavailable' error though, not sure what this means, does this
> mean there is an
> ZjQcmQRYFpfptBannerStart

---

From: Nikhil Shetty <[email-masked]>
Date: Tue, 10 May 2022 23:44:02 +0530

Hi,

Any inputs on how we can debug this further?

Thanks,
Nikhil

On Sun, 8 May 2022 at 12:12 PM, Nikhil Shetty <[email-masked]>
wrote:

>>     If no messages are available at the socket, the receive calls wait
>> for a message to arrive, unless the socket is nonblocking (see fcntl(2)) in
>>     If no messages are available at the socket, the receive calls wait
>> for a message to arrive, unless the socket is nonblocking (see fcntl(2)) in
>> Hi, We are getting below WARNING on one of the standby instances. Not
>> sure what caused it but to resolve it we tried restarting the database
>> instances but it is still not working WARNING - using stale statistics
>> instead of current ones because
>> ZjQcmQRYFpfptBannerStart