Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tEpqa-00A2x9-WA for pgsql-general@arkaria.postgresql.org; Sat, 23 Nov 2024 13:06:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tEpqY-000aiy-LW for pgsql-general@arkaria.postgresql.org; Sat, 23 Nov 2024 13:06:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tEpqY-000ain-3l for pgsql-general@lists.postgresql.org; Sat, 23 Nov 2024 13:06:46 +0000 Received: from mail-oa1-x32.google.com ([2001:4860:4864:20::32]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tEpqV-003NP7-BA for pgsql-general@postgresql.org; Sat, 23 Nov 2024 13:06:44 +0000 Received: by mail-oa1-x32.google.com with SMTP id 586e51a60fabf-296994dd3bfso1922047fac.0 for ; Sat, 23 Nov 2024 05:06:42 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1732367202; x=1732972002; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=I8zLr0Ykqqe3Oi0u83YsH4XsBMi6FM+Hi3YyvJyyYq0=; b=NqGUK3xKyBFqjamHfEWI8pYZGrZBBcVqpfFdHuwhGtKJDbVb3VLsJZzytUK/MqlcKs qhyomHvbUyfPqhmZCNTEEfd0rYI3fzIfm2d0r56FnZXqfSBXFwzFqd1JOEGvZ6dNSKZq xThds2fNvZtg+JUR0dlKlGq6fLrR+pXlcjWASlTqyWfUWt4+Hc2JyS/Rg5u0mVw2dhwB wNeQnkshFYHmLQ9SB0xtWNnMRRD/HNvN90rDBWzIrUg4l2K8qSffL6nqK0nUMJwKgF+q l7zhA1RB9WrhXD3vVcz1o3gpSsmtLGU4JqKL1yPKw6uxWYey8urPbdp3OmR5XrfxHLUb Xoig== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1732367202; x=1732972002; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=I8zLr0Ykqqe3Oi0u83YsH4XsBMi6FM+Hi3YyvJyyYq0=; b=cYQx01trPsdhpxd2V2HFes4vHssPrE4q5pK0yYdH1a687njq7LpiKI+8qp82GxE9nu HVvXwCBaekIk32SV2m5ZnoXIiLHAneyRLd/SRb9Mja4R8F7tJ2Loy+yJ4vGs+92MGMdj 6mob8pJCoR0y2NozAf5CwuDA7JF3iiQU2GmOzKmyBT1ypqqJq6DqjR4LBoXbBKmuXFre 3P2fqpPCiWTUIgasDbSze+k9UzRLHBkDNsu+HpO9ZKJuR89iJCanl9NtHpZ8oP2Cmo93 xt+D1CgGB/AxOy/6e36IxH0OrVNdOD3MLZ7MIu/h2hNSjqSuDSd/B0w5SJYstIE3BCUy MkMw== X-Gm-Message-State: AOJu0YydMQJHCO00cEWHQQf6Q2cqI9XCzZoIbmqGWm49+Fv3X+g/ppDM h3UfsCoMm3PnwYdaX3F7UQkabjxO/+5e2DGpH2m85CqNAS3VTRx4whW6Sbkcv/HRCVpyu0tSWV3 HpnAbJuTRDoC61vynH+y7ztuKm1I= X-Gm-Gg: ASbGncvVIO5eYnwdJ+C3qPQiCItwmBqbJyR+6F56OxV6x4xpTS/55b3Yrbsiy9gHCrz L2ldonkVAaKiOkvnbsQmGA53SfZS5sig= X-Google-Smtp-Source: AGHT+IGgrbXX1vK+AroOKIeBA1ri00LAIgymA5vGliKeccymsSCjkKtZdlYGkTU0yboz/2DmoBuk/ciXy3W4A9u4eSY= X-Received: by 2002:a05:6871:3808:b0:296:c2a6:cb27 with SMTP id 586e51a60fabf-29720c35be7mr6266012fac.24.1732367202106; Sat, 23 Nov 2024 05:06:42 -0800 (PST) MIME-Version: 1.0 References: <6a157286-5948-48ac-bc50-7bb4e65ee5e5@aklaver.com> <38725031-5e80-46ee-b403-a92107b24c39@aklaver.com> <1e2db3f5-00f4-4ec2-800f-46136c2fad59@aklaver.com> <1270021b-c5b5-4ca8-b48d-7dfb163bb6f7@aklaver.com> <9a0d0e80-13e1-421a-9d84-da000fe4433a@aklaver.com> In-Reply-To: <9a0d0e80-13e1-421a-9d84-da000fe4433a@aklaver.com> From: Steeve Boulanger Date: Sat, 23 Nov 2024 07:06:30 -0600 Message-ID: Subject: Re: Database stats ( pg_stat_database.stats_reset ) get reset on daily basis - why? To: Adrian Klaver Cc: pgsql-general Content-Type: multipart/alternative; boundary="000000000000b3c49a0627942b75" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b3c49a0627942b75 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > What is the [2] referring to? Number of the log line for each session or process, starting at 1 > 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. Very plausible. I thought the same too. > 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. -Steeve On Fri, Nov 22, 2024 at 11:18=E2=80=AFAM Adrian Klaver 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 > 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=3D[322035][2] db=3D[MyDB] usr=3D[user1= ] > > client=3D[host1] app=3D[[unknown]]LOG: connection authorized: user=3Dus= er1 > > 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[user1= ] > > client=3D[host1] app=3D[app1]LOG: duration: 1.071 ms parse : > > SELECT <..> > > The above is some garden variety select? > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > --000000000000b3c49a0627942b75 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
>=C2=A0What is the [2] referring to?<= /div>

Number of the log line for each sess= ion or process, starting at 1

= >=C2=A0My guess is the difference in time it takes to log the act= ion and set
> the log timestamp. Whereas the stats_reset value is t= he timestamp when
> the stats system actually did the reset.

Very plausible. I thought the same too.

>=C2=A0The above is= some garden variety select?

Not 100% sure what th= e expression "garden variety select" means lol, but I'll take= a guess that it means an "select from an in-house application"= =C2=A0 .. and yes it is.=C2=A0=C2=A0

= -Steeve

On Fri, Nov 22, 2024 at 11:18=E2=80=AFAM Adrian K= laver <adrian.klaver@aklave= r.com> wrote:
On 11/21/24 15:50, Steeve Boulanger wrote:
>=C2=A0 > 1) Do the 77 share some trait the other 80 don't.
>
> No pattern found yet .. but still verifying a few things
>
>=C2=A0 > 2) Do the OS system logs reveal anything?
>
> Nothing found in syslog
>
>=C2=A0 > 3) What was happening in the databases just prior to the ti= me 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 =C2=A0limit 1;
>
>=C2=A0 =C2=A0 =C2=A0 datname =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0stats_reset =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 since_rese= t
> ----------------+-------------------------------+-----------------
> MyDB =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | *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] =C2=A0db=3D[MyDB] usr=3D= [user1]
> 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?? ..><= br>
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] =C2=A0db=3D[MyDB] usr=3D= [user1]
> client=3D[host1] app=3D[app1]LOG: =C2=A0duration: 1.071 ms parse <u= nnamed>:
> SELECT <..>

The above is some garden variety select?



--
Adrian Klaver
adrian.klave= r@aklaver.com

--000000000000b3c49a0627942b75--