Thread: Reseting statistics, cluster wide
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
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
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
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
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
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
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
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