Search Postgresql Archives

Re: Database stats ( pg_stat_database.stats_reset ) get reset on daily basis - why?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 11/21/24 09:29, Steeve Boulanger wrote:
Hello,

I have noticed recently that our databases (ie most but not all) have their stats reset on daily basis:

select count(case when stats_reset > (now() - interval '1 day') then 1 else null end) db_reset_cnt, count(*) db_total_cnt from pg_stat_database;
  db_reset_cnt | db_total_cnt
--------------+--------------
            77 |          157

Furthermore, the archiver stats also get reset:

select stats_reset from pg_stat_archiver;
           stats_reset
-------------------------------
  2024-11-21 13:52:35.864855+00

Some facts:
- PostgreSQL v13.16 on Linux Ubuntu 20.04
- There's no evidence in the logs that any pg_stat_reset* functions have been executed
             name            | setting
----------------------------+---------
  log_min_duration_statement | 0
  log_statement              | all
- we don't have any user procs with a pg_stat_reset call
- the timestamps of the reset across databases are sometimes close to each other, but not always Anyone has any insight on what could be doing this daily stats reset, or perhaps how to find the root cause?

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-STATS-SETUP

"Cumulative statistics are collected in shared memory. Every PostgreSQL process collects statistics locally, then updates the shared data at appropriate intervals. When a server, including a physical replica, shuts down cleanly, a permanent copy of the statistics data is stored in the pg_stat subdirectory, so that statistics can be retained across server restarts. In contrast, when starting from an unclean shutdown (e.g., after an immediate shutdown, a server crash, starting from a base backup, and point-in-time recovery), all statistics counters are reset."



Regards,
Steeve


--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux