Re: Database and Table stats gets reset automatically

What is your pg_stat_tmp directory ? Just a random guess, any chances that
someone played with pgstat.stat file ?

Thanks,
Adarsh Sharma

On Mon, Jul 25, 2016 at 8:51 PM, Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)com>
wrote:

>
>
> On Mon, 25 Jul 2016, 10:35 p.m. Adrian Klaver, <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
>
>> On 07/25/2016 07:28 AM, Sameer Kumar wrote:
>> >
>> >
>> > On Mon, 25 Jul 2016, 10:10 p.m. Adrian Klaver,
>> > <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)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
>