MIME-Version: 1.0
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>
 <9a0d0e80-13e1-421a-9d84-da000fe4433a@aklaver.com> <CAAiSvx9CangeS_yZ6RLXsXHBZSKmuRvrcB39DNSB_Ler3=OhTw@mail.gmail.com>
 <01020193592431ad-0fedca70-38e9-4fd0-bf49-f5bbf628c76e-000000@eu-west-1.amazonses.com>
In-Reply-To: <01020193592431ad-0fedca70-38e9-4fd0-bf49-f5bbf628c76e-000000@eu-west-1.amazonses.com>
From: Steeve Boulanger <sboulanger29@gmail.com>
Date: Sat, 23 Nov 2024 07:16:44 -0600
Message-ID: <CAAiSvx9HPJAXtTxyjs7uyJN+0NSBXGtwJiF15GSAjHdYwh4V7A@mail.gmail.com>
Subject: Re: Database stats ( pg_stat_database.stats_reset ) get reset on
 daily basis - why?
To: "Ray O'Donnell" <ray@rodonnell.ie>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, pgsql-general <pgsql-general@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAAiSvx9HPJAXtTxyjs7uyJN%2B0NSBXGtwJiF15GSAjHdYwh4V7A%40mail.gmail.com>
Precedence: bulk

> Here (Ireland) we sometimes say "common-or-garden variety".... It means a=
 normal, everyday variety. :-)

I'm afraid that my Irish dialect is limited to "sl=C3=A1inte" only ;-)  In
any case, thanks for taking the time to help with this issue. I'm
still investigating, but I think that calling the "ghostbusters" is
moving up the list now lol.


On Sat, Nov 23, 2024 at 7:09=E2=80=AFAM Ray O'Donnell <ray@rodonnell.ie> wr=
ote:
>
> On 23/11/2024 13:06, Steeve Boulanger wrote:
>
>
> > The above is some garden variety select?
>
> Not 100% sure what the expression "garden variety select" means lol, but =
I'll take a guess that it means an "select from an in-house application"  .=
. and yes it is.
>
>
> Here (Ireland) we sometimes say "common-or-garden variety".... It means a=
 normal, everyday variety. :-)
>
> Ray.
>
>
>
>
> -Steeve
>
> On Fri, Nov 22, 2024 at 11:18=E2=80=AFAM Adrian Klaver <adrian.klaver@akl=
aver.com> wrote:
>>
>> 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 s=
tats
>> > 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=3D[322035][2]  db=3D[MyDB] usr=3D[user=
1]
>> > client=3D[host1] app=3D[[unknown]]LOG: connection authorized: user=3Du=
ser1
>> > database=3DMyDB applicatio
>> > n_name=3Dapp1 <..>
>>
>> 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=3D[322035][3]  db=3D[MyDB] usr=3D[user=
1]
>> > client=3D[host1] app=3D[app1]LOG:  duration: 1.071 ms parse <unnamed>:
>> > SELECT <..>
>>
>> The above is some garden variety select?
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>>
>
>
> --
> Raymond O'Donnell // Galway // Ireland
> ray@rodonnell.ie