Feedback-ID: i76984098:Fastmail
Message-ID: <60f946c9-8c68-4c40-a634-8483a1a1458c@aklaver.com>
Date: Sat, 23 Nov 2024 09:35:28 -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>,
 Ray O'Donnell <ray@rodonnell.ie>
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>
 <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>
 <CAAiSvx9HPJAXtTxyjs7uyJN+0NSBXGtwJiF15GSAjHdYwh4V7A@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAAiSvx9HPJAXtTxyjs7uyJN+0NSBXGtwJiF15GSAjHdYwh4V7A@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/60f946c9-8c68-4c40-a634-8483a1a1458c%40aklaver.com>
Precedence: bulk

On 11/23/24 05:16, Steeve Boulanger wrote:
>> 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áinte" 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.

One possible scenario:


log_min_messages = info
log_min_error_statement = info
log_statement = 'all'


psql -d test -U postgres -p 5432

CREATE OR REPLACE FUNCTION public.admin_func()
  RETURNS void
  LANGUAGE plpgsql
AS $function$
BEGIN
     PERFORM pg_stat_reset();
     RAISE NOTICE 'Reset statistics';
END;
$function$


  select datname, stats_reset from pg_stat_database;
    datname    |          stats_reset
--------------+-------------------------------
  NULL         | NULL
  postgres     | NULL
  template1    | NULL
  template0    | NULL
  test         | 2024-11-23 09:21:49.421552-08
  task_manager | NULL
  test_psql    | NULL
  production   | NULL
  pp_archive   | NULL
  farm_db      | NULL

select admin_func();
NOTICE:  Reset statistics
  admin_func
------------

(1 row)

select datname, stats_reset from pg_stat_database;
    datname    |          stats_reset
--------------+-------------------------------
  NULL         | NULL
  postgres     | NULL
  template1    | NULL
  template0    | NULL
  test         | 2024-11-23 09:26:30.749257-08
  task_manager | NULL
  test_psql    | NULL
  production   | NULL
  pp_archive   | NULL
  farm_db      | NULL


2024-11-23 09:26:30.749 PST [14501] postgres@test LOG:  statement: 
select admin_func();
2024-11-23 09:26:30.749 PST [14501] postgres@test NOTICE:  Reset statistics
2024-11-23 09:26:30.749 PST [14501] postgres@test CONTEXT:  PL/pgSQL 
function admin_func() line 4 at RAISE
2024-11-23 09:26:30.749 PST [14501] postgres@test STATEMENT:  select 
admin_func();

The issue being that the pg_stat_reset() is buried in a function and 
does not show up on its own. The RAISE NOTICE alerts in my logs just so 
I could find the function easily. It could be there is a function or 
functions in your setup doing something similar.

> 
> 
> On Sat, Nov 23, 2024 at 7:09 AM Ray O'Donnell <ray@rodonnell.ie> wrote:
>>
>> 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

-- 
Adrian Klaver
adrian.klaver@aklaver.com