Re: Reset DB stats suggestion pg_stat_reset()

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: postggen2020 s <postggen2020(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, pgsql-admin(at)postgresql(dot)org
Subject: Re: Reset DB stats suggestion pg_stat_reset()
Date: 2020-02-23 19:25:27
Message-ID: 78fe4c74-0dfe-aea8-9d27-9d5c06c3f5c1@aklaver.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

On 2/23/20 10:19 AM, Adrian Klaver wrote:
> On 2/22/20 6:40 PM, postggen2020 s wrote:
>> Thanks Adrian.
>> I am aware about the functions. Here need is, can we use this?.or is
>> there any known effects after firing the functions?.
>
> Yes the stats counters will be reset to 0, which means the planner will
> not have accurate statistics to work with. That can be corrected with an
> ANALYZE.

I should have listened to the little voice that told me to confirm the
above. The reset will reset the counters in the tables listed here:

https://www.postgresql.org/docs/12/monitoring-stats.html#MONITORING-STATS-VIEWS

It does not change the statistics here:

https://www.postgresql.org/docs/12/catalog-pg-statistic.html

Therefore an ANALYZE is not strictly required.

The questions below still hold though.

The questions I have are:
>
> What is the issue you are trying to deal with?
>
> Also why do you think resetting the stats will fix it?
>
>
>>
>> Thanks,
>> Postgann.
>>
>> On Sat, Feb 22, 2020 at 10:47 PM Adrian Klaver
>> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>>
>>     On 2/22/20 12:26 AM, postggen2020 s wrote:
>>      > Hi Team,
>>      >
>>      > Good Afternoon,
>>      >
>>      > We have seen some deadlocks and tempfile count in
>>     pg_stat_database view.
>>      > We are trying to reset the stats.
>>      > Can we use pg_stat_reset() function to reset these stats
>> without any
>>      > impact stats of databases.
>>      > Please advise the process to reset the stats.
>>
>>     See here:
>>
>>     https://www.postgresql.org/docs/12/monitoring-stats.html
>>
>>     Table 27.20. Additional Statistics Functions
>>
>>     It details what your options are.
>>
>>      >
>>      > Thanks.
>>      > Postggen.
>>
>>
>>     --     Adrian Klaver
>>     adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Aditya Toshniwal 2020-02-24 06:46:08 PG v12.2 - Setting jit_above_cost is causing the server to crash
Previous Message Adrian Klaver 2020-02-23 18:19:34 Re: Reset DB stats suggestion pg_stat_reset()

Browse pgsql-general by date

  From Date Subject
Next Message Edson Richter 2020-02-23 21:00:25 RE: Replication: slave server has 3x size of production server?
Previous Message Andrus 2020-02-23 19:15:55 Re: How to get error message details from libpq based psqlODBC driver (regression)