Feedback-ID: i76984098:Fastmail
Message-ID: <9a0d0e80-13e1-421a-9d84-da000fe4433a@aklaver.com>
Date: Fri, 22 Nov 2024 09:18:31 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Database stats ( pg_stat_database.stats_reset ) get reset on
 daily basis - why?
To: Steeve Boulanger <sboulanger29@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
References: <CAAiSvx8_Z6QGey92dC+6Yona18KDMLWinfR5LOJsMgj-42XJtg@mail.gmail.com>
 <6a157286-5948-48ac-bc50-7bb4e65ee5e5@aklaver.com>
 <CAAiSvx_vsiCmL3gBj+1hRpMxo=nOc3HUa+VaGO9_GfOXDWCsrQ@mail.gmail.com>
 <38725031-5e80-46ee-b403-a92107b24c39@aklaver.com>
 <CAAiSvx-pwz562KrTPdayK+qqRm9DxbrU1r9_Qkpm5BLh-1G+pA@mail.gmail.com>
 <1e2db3f5-00f4-4ec2-800f-46136c2fad59@aklaver.com>
 <CAAiSvx_ZtM72ZO=uq56X=iJ=a17KcgB=07c5abyFnQ8vAses0Q@mail.gmail.com>
 <1270021b-c5b5-4ca8-b48d-7dfb163bb6f7@aklaver.com>
 <CAAiSvx-JPJCGc_VR+YrUNo75pb0yrmB8Ng3zBOCf+Mksdo5JYg@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAAiSvx-JPJCGc_VR+YrUNo75pb0yrmB8Ng3zBOCf+Mksdo5JYg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/9a0d0e80-13e1-421a-9d84-da000fe4433a%40aklaver.com>
Precedence: bulk

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