Re: Database and Table stats gets reset automatically

On Mon, 25 Jul 2016, 10:35 p.m. Adrian Klaver, <adrian.klaver@aklaver.com> wrote:

On 07/25/2016 07:28 AM, Sameer Kumar wrote:
>
>
> On Mon, 25 Jul 2016, 10:10 p.m. Adrian Klaver,
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 07/24/2016 09:58 PM, Sameer Kumar wrote:
>     > Hi,
>     >
>     > I have PostgreSQL v9.4.4 running in my environment. It has been up for
>     > over 2 years now. I noticed that suddenly the statistics have been
>     reset
>     > and all the stat tables/columns got restarted from.
>
>     Any of these happen?:
>
>     https://www.postgresql.org/docs/9.4/static/monitoring-stats.html
>     ". When recovery is performed at server start (e.g. after immediate
>     shutdown, server crash, and point-in-time recovery), all statistics
>     counters are reset."
>
>
>
> Nope. This has happened on the standby database. The counter on the
> master database are still running fine.
>
> There was a period of few days about a month ago when I had to reverse
> the roles but then I switched them back to the way they were. But
> recently, I have not done anything which brings the server out of recovery.
>
>
>     >
>     > This happened 2 weeks ago. I noticed only recently after I looked
>     at the
>     > plot over last week (which dipped suddenly for "number of tuples
>     > returned", "number of conflicts" etc).
>
>     Any change in procedures the last two weeks?
>
>
> Nope. The scripts to capture this data to csv file is very much the same.
>
>
>     >
>     > The columns which got reset are from pg_stat_database,
>     > pg_stat_user_tables, pg_stat_bgwriter
>     >
>     > As far I know no one has fired pg_stat_reset or pg_stat_reset_shared.
>     >
>     > I noticed that the last largest value is from pg_stat_user_tables.
>     > tup_returned (470440261405). Does the statistics get reset
>     automatically
>
>     tup_returned is in pg_stat_database.
>
>
> Sorry my bad (a copy paste mistake). Yes I was referring to tup_returned
> from pg_stat_database. But the various stats in pg_stat_user_tables have
> also been reset.
>
>
>     What does the stats_reset field show in pg_stat_database?
>
>
> It says a timestamp from 18th July 2016. Infact all the other statistics
> views have the same timestamp.

Don't suppose you still have the logs from that date to see if there is
a clue?

I don't have them on the server. I can fetch them from the archives. Ot might tke a day or two. Let me get back. Thanks for helping.

Any idea about probable suspects apart from the ones you listed?

>
>
>     > when the value for one of the statistics reaches the high number
>     > supported by int4?
>     >
>     > I am running on Red Hat 6.7.
>     >
>     > Regards
>     > Sameer
>     > --
>     > --
>     > Best Regards
>     > Sameer Kumar | DB Solution Architect
>     > *ASHNIK PTE. LTD.*
>     >
>     > 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
>     >
>     > T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
>     <http://www.ashnik.com>
>     >
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
> --
> --
> Best Regards
> Sameer Kumar | DB Solution Architect
> *ASHNIK PTE. LTD.*
>
> 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
>
> T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
>


--
Adrian Klaver
adrian.klaver@aklaver.com

--

--

Best Regards

Sameer Kumar | DB Solution Architect 

ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com