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 1tEptF-00A3A1-1q for pgsql-general@arkaria.postgresql.org; Sat, 23 Nov 2024 13:09:33 +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 1tEptD-000ei9-Pl for pgsql-general@arkaria.postgresql.org; Sat, 23 Nov 2024 13:09:31 +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 <01020193592431ad-0fedca70-38e9-4fd0-bf49-f5bbf628c76e-000000@mail.rodonnell.ie>) id 1tEptD-000ei1-55 for pgsql-general@lists.postgresql.org; Sat, 23 Nov 2024 13:09:31 +0000 Received: from a7-23.smtp-out.eu-west-1.amazonses.com ([54.240.7.23]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from <01020193592431ad-0fedca70-38e9-4fd0-bf49-f5bbf628c76e-000000@mail.rodonnell.ie>) id 1tEpt9-003NPe-CR for pgsql-general@postgresql.org; Sat, 23 Nov 2024 13:09:29 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/simple; s=dv7fx26oobputowqxkzhnujragfvk7eg; d=rodonnell.ie; t=1732367364; h=Content-Type:Message-ID:Date:MIME-Version:Subject:To:Cc:References:From:In-Reply-To; bh=UC20VNd7q2+uUXD54kVJhiW67TSYaAc4h+TNEbH82Rk=; b=dEIPk69CyrzuUaZk2hHFLsCAWtnLz4jiQOnjrtOJ1PIZoZCagSFRgXsajHIbQnt3 DYdAZVvFrYN+ov2bnhuIxqTmdhRelAAyFsnmm0ytpOajT1iOX7DSWmxmCY10atOC5uC MvL0TJETMKYf/VO1LNtyWyvCAJfONxjJ/6ljX+nlsyCNLDuviKOpfjY0j8+3z4TMpZb Jaw3/1foYz8pYxX5xw/Zz/gvEMym3MKtVv7KHWuI/ME0/71gRwi5PnsLU1dnFHTNu81 hsL8uGIsrfbQPSKqtGW1mJYhSiCyLJfoUXovxWI6P+kYIyKJyLvUWZ0O/foTsQCGOGe 0CKtjYFCjw== DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/simple; s=uku4taia5b5tsbglxyj6zym32efj7xqv; d=amazonses.com; t=1732367364; h=Content-Type:Message-ID:Date:MIME-Version:Subject:To:Cc:References:From:In-Reply-To:Feedback-ID; bh=UC20VNd7q2+uUXD54kVJhiW67TSYaAc4h+TNEbH82Rk=; b=PFEaXTlC2hbif1qefjJbTAW4OM+c3L0pKZlPgcAJpVFgrGZPg+CxM846S43QBi77 kVu5h6MymBdFoiDjWUdX5Xmf/2DL2te8vQC/f0VY0PIUugHuBlzQFANoDQ7Ttsw2pUs 0aflqxAyw/OAyseTcMTQQjCbHTwZppIegPl3u12A= Content-Type: multipart/alternative; boundary="------------NHqLKd5unZEG4J8XOhu5K5CM" Message-ID: <01020193592431ad-0fedca70-38e9-4fd0-bf49-f5bbf628c76e-000000@eu-west-1.amazonses.com> Date: Sat, 23 Nov 2024 13:09:24 +0000 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 , Adrian Klaver Cc: pgsql-general 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> Content-Language: en-GB From: Ray O'Donnell In-Reply-To: X-Spam-Bar: / Feedback-ID: ::1.eu-west-1.Bw1eu/7Uepg3xcsiFBL71JJRCh2A2yTFrgBhhiulXmU=:AmazonSES X-SES-Outgoing: 2024.11.23-54.240.7.23 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------NHqLKd5unZEG4J8XOhu5K5CM Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 AM 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=[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 : > > SELECT <..> > > The above is some garden variety select? > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Raymond O'Donnell // Galway // Ireland ray@rodonnell.ie --------------NHqLKd5unZEG4J8XOhu5K5CM Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
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 AM Adrian Klaver <adrian.klaver@aklaver.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 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



-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie
--------------NHqLKd5unZEG4J8XOhu5K5CM--