public inbox for [email protected]
help / color / mirror / Atom feedDatabase stats ( pg_stat_database.stats_reset ) get reset on daily basis - why?
3+ messages / 2 participants
[nested] [flat]
* Database stats ( pg_stat_database.stats_reset ) get reset on daily basis - why?
@ 2024-11-21 17:29 Steeve Boulanger <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Steeve Boulanger @ 2024-11-21 17:29 UTC (permalink / raw)
To: pgsql-general
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?
Regards,
Steeve
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Database stats ( pg_stat_database.stats_reset ) get reset on daily basis - why?
@ 2024-11-21 17:54 Adrian Klaver <[email protected]>
parent: Steeve Boulanger <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Adrian Klaver @ 2024-11-21 17:54 UTC (permalink / raw)
To: Steeve Boulanger <[email protected]>; pgsql-general
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
[email protected]
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Database stats ( pg_stat_database.stats_reset ) get reset on daily basis - why?
@ 2024-11-21 20:48 Adrian Klaver <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Adrian Klaver @ 2024-11-21 20:48 UTC (permalink / raw)
To: Steeve Boulanger <[email protected]>; +Cc: pgsql-general
On 11/21/24 12:34, Steeve Boulanger wrote:
Please reply to list also.
Ccing list.
> Thanks Adrian for taking the time to respond. I will review the
> documentation once more, just in case I missed anything.
>
> My apologies - I forgot to mention in my original post, that our last
> cluster shutdown was over 15 days ago, thus an "unclean"
> shutdown would not explain these daily stats reset that we are seeing.
> It might also be relevant to mentioned
Then something is resetting the statistics.
1) What is log_min_error_statement set to?
2) Did you reload the server when changing?:
log_min_duration_statement | 0
log_statement | all
>
> -Steeve
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-11-21 20:48 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-21 17:29 Database stats ( pg_stat_database.stats_reset ) get reset on daily basis - why? Steeve Boulanger <[email protected]>
2024-11-21 17:54 ` Adrian Klaver <[email protected]>
2024-11-21 20:48 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox