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(dot)klaver(at)aklaver(dot)com