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

On 11/21/24 15:50, Steeve Boulanger wrote:
>  > 1) Do the 77 share some trait the other 80 don't.
> 
> No pattern found yet .. but still verifying a few things
> 
>  > 2) Do the OS system logs reveal anything?
> 
> Nothing found in syslog
> 
>  > 3) What was happening in the databases just prior to the time the stats
> reset?
> 
> Here's an example (log extracts) for a stats reset occurrence:
> 
> select datname, stats_reset, now()-stats_reset as since_reset
> from pg_stat_database
> where ( now()-stats_reset ) < interval '1 day'
> order by 3  limit 1;
> 
>      datname     |          stats_reset          |   since_reset
> ----------------+-------------------------------+-----------------
> MyDB           | *2024-11-21 13:48:34.332*785+00 | 00:00:22.266304
> 
> <--LOGS-->
> 2024-11-21 13:48:34.324 UTC pid=[322035][2]  db=[MyDB] usr=[user1] 
> client=[host1] app=[[unknown]]LOG: connection authorized: user=user1 
> database=MyDB applicatio
> n_name=app1 <..>

What is the [2] referring to?

> 
> <.. no calls at "2024-11-21 13:48:34.332" - WHY?? ..>

My guess is the difference in time it takes to log the action and set 
the log timestamp. Whereas the stats_reset value is the timestamp when 
the stats system actually did the reset.

> 
> 2024-11-21 13:48:34.336 UTC pid=[322035][3]  db=[MyDB] usr=[user1] 
> client=[host1] app=[app1]LOG:  duration: 1.071 ms parse <unnamed>: 
> SELECT <..>

The above is some garden variety select?



-- 
Adrian Klaver
adrian.klaver@aklaver.com