Re: Unexplained statistics reset? Help tracking it down.

On 12/09/2016 06:19 AM, Bill Moran wrote:
>
> I've been seeing some unexplained behavior whereas the statistics in a Postgres
> database reset with no explanation as to why. This is concerning because it
> results in terrible query plans until someone manually runs analyze, and that
> it negatively impacts autovacuum.
>
> This is Postgres 9.5.4 on Ubuntu 14 (yes, I know there's a patch I need to
> apply -- it's on my TODO list, but I don't see any mention of fixing unexplained
> stats resets in that version, so I'm not assuming that will fix it)
>
> My first thought is that some random user was calling pg_reset_stats() without
> realizing what they were doing. However, I have full query logging enabled on
> this system, and the logs don't show this happening. (Yes, I've also checked
> for someone disabling query logging for their connection before doing myseterious
> things).
>
> Before I start theorizing that this might be a bug in Postgres, does anyone have
> any suggestions on what other ways the stats could be reset that I need to check
> on? Has anyone else experienced this to lend credence to the possibility that it's

https://www.postgresql.org/docs/9.5/static/monitoring-stats.html

" When the server shuts down cleanly, a permanent copy of the statistics
data is stored in the pg_stat subdirectory, so that statistics can be
retained across server restarts. When recovery is performed at server
start (e.g. after immediate shutdown, server crash, and point-in-time
recovery), all statistics counters are reset."

> a bug? I have no clue how to reproduce it, as the occurrance is rare and still
> seems random.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com