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.ku...@ashnik.com> wrote: > > > On Mon, 25 Jul 2016, 10:35 p.m. Adrian Klaver, <adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@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.kla...@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 >
