Thread: Reseting statistics, cluster wide

Reseting statistics, cluster wide

From
Mario Splivalo
Date:
I have set up munin monitoring that plots nice graphs using
pg_stat_bgwriter and pg_stat_database views. It has been on for quite
some time now, and the numbers those views return are too big for munin
to handle. I would like to reset those statistics. As explained by
manual, pg_stat_reset() resets only db-related statistics, not the ones
used cluster-wide. Is there a way to reset those? I don't mind stopping
the server if I need to, but I'd realy like to have those started all
over again.

    Mario

Re: Reseting statistics, cluster wide

From
raghu ram
Date:


On Thu, Apr 21, 2011 at 1:01 AM, Mario Splivalo <mario.splivalo@megafon.hr> wrote:
I have set up munin monitoring that plots nice graphs using pg_stat_bgwriter and pg_stat_database views. It has been on for quite some time now, and the numbers those views return are too big for munin to handle. I would like to reset those statistics. As explained by manual, pg_stat_reset() resets only db-related statistics, not the ones used cluster-wide. Is there a way to reset those? I don't mind stopping the server if I need to, but I'd realy like to have those started all over again.


Currently we don't have such feature to reset statistics on cluster level. You have to use two PostgreSQL functions like pg_stat_reset_shared(text), pg_stat_reset ().


There are a few statistics that are only kept on a per-cluster basis, such as those reported in pg_stat_database and pg_stat_bgwriter. Since pg_stat_reset only resets per-database statistics.


psql=# select pg_stat_reset();


psql=# select pg_stat_reset_shared('bgwritter');  -- It will reset the statistics shown by pg_stat_bgwritter.


--Raghu Ram


 

       Mario

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

Re: Reseting statistics, cluster wide

From
Mario Splivalo
Date:
On 04/20/2011 10:49 PM, raghu ram wrote:
> On Thu, Apr 21, 2011 at 1:01 AM, Mario Splivalo
> <mario.splivalo@megafon.hr <mailto:mario.splivalo@megafon.hr>> wrote:
>     I have set up munin monitoring that plots nice graphs using
>     pg_stat_bgwriter and pg_stat_database views. It has been on for
>     quite some time now, and the numbers those views return are too big
>     for munin to handle. I would like to reset those statistics. As
>     explained by manual, pg_stat_reset() resets only db-related
>     statistics, not the ones used cluster-wide. Is there a way to reset
>     those? I don't mind stopping the server if I need to, but I'd realy
>     like to have those started all over again.
> Currently we don't have such feature to reset statistics on cluster
> level. You have to use two PostgreSQL functions like
> pg_stat_reset_shared(text), pg_stat_reset ().
> There are a few statistics that are only kept on a per-cluster basis,
> such as those reported in pg_stat_database and pg_stat_bgwriter. Since
> pg_stat_reset only resets per-database statistics.
>
>
> psql=# select pg_stat_reset();
>
>
> psql=# select pg_stat_reset_shared('bgwritter');  -- It will reset the
> statistics shown by pg_stat_bgwritter.
>

Heh, I've neglected to mention that i'm using postgres 8.4.5 on Debian
Stable. Is there a way to reset statistics on pg8.4? I've tested it on
9.0 and it works there.

    Mario

Re: Reseting statistics, cluster wide

From
raghu ram
Date:

Heh, I've neglected to mention that i'm using postgres 8.4.5 on Debian Stable. Is there a way to reset statistics on pg8.4? I've tested it on 9.0 and it works there.



AFAIK, I dont think we can achieve this in PG 8.4 at cluster level. Can anybody has the information ?

-- Raghu

Re: Reseting statistics, cluster wide

From
Cédric Villemain
Date:
2011/4/21 raghu ram <raghuchennuru@gmail.com>:
>>
>> Heh, I've neglected to mention that i'm using postgres 8.4.5 on Debian
>> Stable. Is there a way to reset statistics on pg8.4? I've tested it on 9.0
>> and it works there.
>>
>
> AFAIK, I dont think we can achieve this in PG 8.4 at cluster level. Can
> anybody has the information ?
> -- Raghu

With 8.4 you only have the pg_stat_reset(), it resets all stats. The
finest one in 9.0 is a new feature/improvement.

See http://www.postgresql.org/docs/8.4/static/monitoring-stats.html

--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support

Re: Reseting statistics, cluster wide

From
Mario Splivalo
Date:
On 04/22/2011 03:20 PM, Cédric Villemain wrote:
> 2011/4/21 raghu ram<raghuchennuru@gmail.com>:
>>>
>>> Heh, I've neglected to mention that i'm using postgres 8.4.5 on Debian
>>> Stable. Is there a way to reset statistics on pg8.4? I've tested it on 9.0
>>> and it works there.
>>>
>>
>> AFAIK, I dont think we can achieve this in PG 8.4 at cluster level. Can
>> anybody has the information ?
>> -- Raghu
>
> With 8.4 you only have the pg_stat_reset(), it resets all stats. The
> finest one in 9.0 is a new feature/improvement.

Yeps, but that is only for database-related statistics. Stuff returned
from pg_stat_bgwriter view are not reset.

    Mario

Re: Reseting statistics, cluster wide

From
Cédric Villemain
Date:
2011/4/22 Mario Splivalo <mario.splivalo@megafon.hr>:
> On 04/22/2011 03:20 PM, Cédric Villemain wrote:
>>
>> 2011/4/21 raghu ram<raghuchennuru@gmail.com>:
>>>>
>>>> Heh, I've neglected to mention that i'm using postgres 8.4.5 on Debian
>>>> Stable. Is there a way to reset statistics on pg8.4? I've tested it on
>>>> 9.0
>>>> and it works there.
>>>>
>>>
>>> AFAIK, I dont think we can achieve this in PG 8.4 at cluster level. Can
>>> anybody has the information ?
>>> -- Raghu
>>
>> With 8.4 you only have the pg_stat_reset(), it resets all stats. The
>> finest one in 9.0 is a new feature/improvement.
>
> Yeps, but that is only for database-related statistics. Stuff returned from
> pg_stat_bgwriter view are not reset.

ah yes.
There exist a very brutal way to do it, which will remove all stats.
(you need the server to be down to do that)
move or rm the "global/pgstat.stat" file.

To come back to your original problem : I am surprised you have too
large value for munin, isn't there something to do on the munin side
instead ?

--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support

Re: Reseting statistics, cluster wide

From
Mario Splivalo
Date:
On 04/22/2011 04:26 PM, Cédric Villemain wrote:
> ah yes.
> There exist a very brutal way to do it, which will remove all stats.
> (you need the server to be down to do that)
> move or rm the "global/pgstat.stat" file.
>
> To come back to your original problem : I am surprised you have too
> large value for munin, isn't there something to do on the munin side
> instead ?

I had some problems with munin that I've fixed now, it is plotting
values quite ok :) But still, I wanted to see if there is a way to reset
stats. Thnx :)

    Mario