From: Bruce Momjian <bruce@momjian.us>
To: Andres Freund <andres@anarazel.de>
Cc: Hamid Akhtar <hamid.akhtar@percona.com>
Cc: jobinau@gmail.com, pgsql-bugs@lists.postgresql.org, Jobin Augustine <jobin.augustine@percona.com>
Subject: Re: BUG #18046: stats collection behaviour change is affecting the usability of information.
Date: Wed, 6 Sep 2023 13:06:35 -0400
Message-ID: <ZPixmw6EyNKy2SRY@momjian.us> (raw)
In-Reply-To: <ZPivXYK++YnRcMZl@momjian.us>
References: <18046-9263842a9ec688e0@postgresql.org>
<CANugjhvg-EFPc_1w1o0We8O5mUsd9DMYC6MhPOQeyPwaKFBhNQ@mail.gmail.com>
<CAEpZXfek+O+CxbcLc+a+Ng2GxGmV4C90H___-LGXkP6ZUau60w@mail.gmail.com>
<ZPivXYK++YnRcMZl@momjian.us>
(This email has Andres properly in the TO field.)
Andres, can you comment on this thread? I see you had a commit to PG
15 in this area:
commit 5cd1c40b3c
Author: Andres Freund <andres@anarazel.de>
Date: Thu Apr 14 17:40:25 2022 -0700
pgstat: set timestamps of fixed-numbered stats after a crash.
When not loading stats at startup (i.e. pgstat_discard_stats() getting
called), reset timestamps of fixed numbered stats would be left at
0. Oversight in 5891c7a8ed8.
Instead use pgstat_reset_after_failure() and add tests verifying that
fixed-numbered reset timestamps are set appropriately.
Reported-By: "David G. Johnston" <david.g.johnston@gmail.com>
Discussion: https://postgr.es/m/CAKFQuwamFuaQHKdhcMt4Gbw5+Hca2UE741B8gOOXoA=TtAd2Yw@mail.gmail.com
Thanks.
---------------------------------------------------------------------------
On Fri, Aug 4, 2023 at 10:40:46PM +0530, Jobin Augustine wrote:
> Thank you Hamid for working on this and coming with a fix.
>
> On Fri, Aug 4, 2023 at 4:53 PM Hamid Akhtar <hamid.akhtar@percona.com> wrote:
>
>
> Thank you for the bug report Jobin.
>
> IMHO, this is a valid concern. As per the documentation, the "stats_reset"
> column tracks the last time the stats were reset. There is no mention of
> this being timestamp for manual reset only.
>
>
> Without this base info, users don't have the option to understand the
> cumulative statistics in the stats view
>
>
>
> Attach is a fix for PG16 and PG15 that resolves this issue. It ensures that
> when the database stats are being written to disk and the stats_reset is
> not set, it adds the current timestamp to it. Since a new file is written
> at initdb and when the server is recovering from a crash, this works as
> expected.
>
>
>
> I can confirm that this patch fixes the problem.
> I could find simple steps to reproduce the original problem independently.
>
>
> Step 1 : Create a new database
> CREATE DATABASE db1;
>
> Step 2. Create a table in the database
> \c db1
> CREATE TABLE t1 (id INT);
>
> Step 3. Check the timestamp of the start of database-level statistics
> db1=# SELECT datname,stats_reset FROM pg_stat_database;
>
> Expected behaviour(works in all versions upto and including PostgreSQL 14)
>
> datname | stats_reset
> -----------+-------------------------------
> | 2023-08-02 06:41:15.777135+00
> postgres | 2023-08-02 06:41:15.777108+00
> template1 |
> template0 |
> db1 | 2023-08-02 11:02:54.954363+00
> (5 rows)
>
>
> The problem in PostgreSQL 15 and above
>
> datname | stats_reset
> -----------+------------------------------
> |
> postgres |
> db1 |
> template1 |
> template0 |
> (5 rows)
>
>
> Once again, Thank you for the fix.
>
> Jobin Augustine.
>
>
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: bruce@momjian.us, andres@anarazel.de, hamid.akhtar@percona.com, jobin.augustine@percona.com
Subject: Re: BUG #18046: stats collection behaviour change is affecting the usability of information.
In-Reply-To: <ZPixmw6EyNKy2SRY@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox