MIME-Version: 1.0
From: Steeve Boulanger <sboulanger29@gmail.com>
Date: Thu, 21 Nov 2024 11:29:26 -0600
Message-ID: <CAAiSvx8_Z6QGey92dC+6Yona18KDMLWinfR5LOJsMgj-42XJtg@mail.gmail.com>
Subject: Database stats ( pg_stat_database.stats_reset ) get reset on daily
 basis - why?
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000064036906276f9c97"
Archived-At: <https://www.postgresql.org/message-id/CAAiSvx8_Z6QGey92dC%2B6Yona18KDMLWinfR5LOJsMgj-42XJtg%40mail.gmail.com>
Precedence: bulk

--00000000000064036906276f9c97
Content-Type: text/plain; charset="UTF-8"

Hello,

I have noticed recently that our databases (ie most but not all) have their
stats reset on daily basis:

select count(case when stats_reset > (now() - interval '1 day') then 1 else
null end) db_reset_cnt, count(*) db_total_cnt from pg_stat_database;
 db_reset_cnt | db_total_cnt
--------------+--------------
           77 |          157

Furthermore, the archiver stats also get reset:

select stats_reset from pg_stat_archiver;
          stats_reset
-------------------------------
 2024-11-21 13:52:35.864855+00

Some facts:
- PostgreSQL v13.16 on Linux Ubuntu 20.04
- There's no evidence in the logs that any pg_stat_reset* functions have
been executed
            name            | setting
----------------------------+---------
 log_min_duration_statement | 0
 log_statement              | all
- we don't have any user procs with a pg_stat_reset call
- the timestamps of the reset across databases are sometimes close to each
other, but not always

Anyone has any insight on what could be doing this daily stats reset, or
perhaps how to find the root cause?

Regards,
Steeve

--00000000000064036906276f9c97
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello,<div><br></div><div>I have noticed recently that our=
 databases (ie most but not all) have their stats reset on daily basis:</di=
v><div><br></div><div>select count(case when stats_reset &gt; (now() - inte=
rval &#39;1 day&#39;) then 1 else null end) db_reset_cnt, count(*) db_total=
_cnt from pg_stat_database;<br>=C2=A0db_reset_cnt | db_total_cnt<br>-------=
-------+--------------<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A077 | =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0157</div><div><br></div><div>Furthermore, th=
e archiver stats also get reset:=C2=A0</div><div><br></div><div>select stat=
s_reset from pg_stat_archiver;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 stats_=
reset<br>-------------------------------<br>=C2=A02024-11-21 13:52:35.86485=
5+00<br></div><div><br></div><div>Some facts:</div><div>- PostgreSQL v13.16=
 on Linux Ubuntu 20.04</div><div>- There&#39;s no evidence in the logs that=
 any pg_stat_reset* functions have been executed</div><div>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 name =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=
 setting<br>----------------------------+---------<br>=C2=A0log_min_duratio=
n_statement | 0<br>=C2=A0log_statement =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0| all<br></div><div>- we don&#39;t have any user procs with a =
pg_stat_reset call=C2=A0</div><div>- the timestamps of the=C2=A0reset acros=
s databases are sometimes close to each other, but not always</div><div>=C2=
=A0</div><div>Anyone has any insight on what could be doing this daily stat=
s reset, or perhaps how to find the root cause?<br><br>Regards,</div><div>S=
teeve=C2=A0</div><div><br></div></div>

--00000000000064036906276f9c97--