Database and Table stats gets reset automatically

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."

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?

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.

What does the stats_reset field show in pg_stat_database?

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

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, 25 Jul 2016, 10:10 p.m. Adrian Klaver, <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.

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

--
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

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?

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&gt;

--
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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&gt;

--
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

On 07/25/2016 08:21 AM, Sameer Kumar wrote:

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?

Not that I know of.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.kumar@ashnik.com>
wrote: