Обсуждение: Reset DB stats suggestion pg_stat_reset()
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.
Thanks.
Postggen.
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.klaver@aklaver.com
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.klaver@aklaver.com
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?.
Thanks,
Postgann.
On Sat, Feb 22, 2020 at 10:47 PM Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com
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?.
Thanks,
Postgann.
On Sat, Feb 22, 2020 at 10:47 PM Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com
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. 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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
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. 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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> >> > > -- Adrian Klaver adrian.klaver@aklaver.com
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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> >> > > -- Adrian Klaver adrian.klaver@aklaver.com
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.
Yes, you could reset stats every month or week or whatever you decide makes sense so you have an understanding of the timeline. Else, knowing that an index has been used X times since who-knows-when is not a very useful piece of information. I have occasionally reset the stats on all indexes on a table when adding or removing an index from that table so that I have an accurate idea of whether index_xyz or index_abc gets used. You can take a snapshot of the stats on the table occasionally and compare prior count to new count if you prefer. I find reset to be cleaner.