Thread: pg_stat_database update stats_reset only by pg_stat_reset

pg_stat_database update stats_reset only by pg_stat_reset

From

张连壮

Date:

13 May 2019, 10:30:54

pg_stat_reset_single_table_counters/pg_stat_reset_single_function_counters only update pg_stat_database column stats_reset.

stat_reset shuld update when all the column is reset.

sample:

drop database if exists lzzhang_db;

create database lzzhang_db;

\c lzzhang_db

create table lzzhang_tab(id int);

insert into lzzhang_tab values(1);

select tup_fetched, stats_reset from pg_stat_database where datname='lzzhang_db';

select pg_sleep(1);

select pg_stat_reset_single_table_counters('lzzhang_tab'::regclass::oid);

select tup_fetched, stats_reset from pg_stat_database where datname='lzzhang_db';

result:

tup_fetched | stats_reset

-------------+-------------------------------

514 | 2019-05-12 03:22:55.702753+08

(1 row)

tup_fetched | stats_reset

-------------+-------------------------------

710 | 2019-05-12 03:22:56.729336+08

(1 row)

tup_fetched is not reset but stats_reset is reset.

Attachment

0001-pg_stat_database-update-stats_reset-only-by-pg_stat_.patch

Re: pg_stat_database update stats_reset only by pg_stat_reset

From

张连壮

Date:

14 May 2019, 12:00:24

it reset statistics for a single table and update the column stats_reset of pg_stat_database.

but i think that stats_reset shoud be database-level statistics, a single table should not update the column stats_reset.

i am monitor the xact_commit every 5 minutes, when stats_reset is reset but ohter columns is not reset, i can't decide

if i will recount the xact_commit, because pg_stat_reset make all column to zero. pg_stat_reset_single_table_counters

only reset the column stats_reset.

张连壮 <lianzhuangzhang@gmail.com> 于2019年5月13日周一下午3:30写道：

pg_stat_reset_single_table_counters/pg_stat_reset_single_function_counters only update pg_stat_database column stats_reset.
stat_reset shuld update when all the column is reset.

sample:
drop database if exists lzzhang_db;
create database lzzhang_db;
\c lzzhang_db

create table lzzhang_tab(id int);
insert into lzzhang_tab values(1);
insert into lzzhang_tab values(1);

select tup_fetched, stats_reset from pg_stat_database where datname='lzzhang_db';
select pg_sleep(1);

select pg_stat_reset_single_table_counters('lzzhang_tab'::regclass::oid);
select tup_fetched, stats_reset from pg_stat_database where datname='lzzhang_db';

result:
tup_fetched | stats_reset
-------------+-------------------------------
514 | 2019-05-12 03:22:55.702753+08
(1 row)
tup_fetched | stats_reset
-------------+-------------------------------
710 | 2019-05-12 03:22:56.729336+08
(1 row)
tup_fetched is not reset but stats_reset is reset.

Re: pg_stat_database update stats_reset only by pg_stat_reset

From

"Daniel Verite"

Date:

11 July 2019, 17:34:20

    张连壮 wrote:

> it reset statistics for a single table and update the column stats_reset of
> pg_stat_database.
> but i think that stats_reset shoud be database-level statistics, a single
> table should not update the column stats_reset.

This patch is a current CF entry at
https://commitfest.postgresql.org/23/2116/

The issue it addresses was submitted as bug #15801:
https://www.postgresql.org/message-id/flat/15801-21c7fbff08b6c10c%40postgresql.org

As mentioned in the discussion on -bugs, it's not necessarily a bug
because:

* the comment in the code specifically states that it's intentional,
in pgstat_recv_resetsinglecounter():

    /* Set the reset timestamp for the whole database */
    dbentry->stat_reset_timestamp = GetCurrentTimestamp();

* the commit message also states the same:

commit 4c468b37a281941afd3bf61c782b20def8c17047
Author: Magnus Hagander <magnus@hagander.net>
Date:    Thu Feb 10 15:09:35 2011 +0100

    Track last time for statistics reset on databases and bgwriter

    Tracks one counter for each database, which is reset whenever
    the statistics for any individual object inside the database is
    reset, and one counter for the background writer.

    Tomas Vondra, reviewed by Greg Smith


I can understand why you'd want that resetting the stats for a single object
would not reset the per-database timestamp, but this would revert a 8+ years
old decision that seems intentional and has apparently not been criticized
since then (based on searching for pg_stat_reset_single_table_counters in
the archives) . More opinions are probably needed in favor of this
change (or against, in which case the fate of the patch might be a
rejection).


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Re: pg_stat_database update stats_reset only by pg_stat_reset

From

Michael Paquier

Date:

12 July 2019, 07:51:50

On Thu, Jul 11, 2019 at 04:34:20PM +0200, Daniel Verite wrote:
> I can understand why you'd want that resetting the stats for a single object
> would not reset the per-database timestamp, but this would revert a 8+ years
> old decision that seems intentional and has apparently not been criticized
> since then (based on searching for pg_stat_reset_single_table_counters in
> the archives) . More opinions are probably needed in favor of this
> change (or against, in which case the fate of the patch might be a
> rejection).

I agree with Daniel that breaking an 8-year-old behavior may not be of
the taste of folks relying on the current behavior, particularly
because we have not had complains about the current behavior being
bad.  So -1 from me.
--
Michael

Attachment

signature.asc

Re: pg_stat_database update stats_reset only by pg_stat_reset

From

Tomas Vondra

Date:

12 July 2019, 16:07:19

On Fri, Jul 12, 2019 at 01:51:50PM +0900, Michael Paquier wrote:
>On Thu, Jul 11, 2019 at 04:34:20PM +0200, Daniel Verite wrote:
>> I can understand why you'd want that resetting the stats for a single object
>> would not reset the per-database timestamp, but this would revert a 8+ years
>> old decision that seems intentional and has apparently not been criticized
>> since then (based on searching for pg_stat_reset_single_table_counters in
>> the archives) . More opinions are probably needed in favor of this
>> change (or against, in which case the fate of the patch might be a
>> rejection).
>
>I agree with Daniel that breaking an 8-year-old behavior may not be of
>the taste of folks relying on the current behavior, particularly
>because we have not had complains about the current behavior being
>bad.  So -1 from me.

Yeah, I agree. There are several reasons why it's done this way:

1) overhead

Now we only store a two timestamps - for a database and for bgwriter. We
could track a timestamp for each object, of course ...

2) complexity

Updating the timestamps would be fairly simple, but what about querying
the data? Currently you fetch the data, see if the stats_reset changed
since the last snapshot, and if not you're good. If it changed, you know
some object (or the whole db) has reset counters, so you can't rely on
the data being consistent.

If we had stats_reset for each object, figuring out which data is still
valid and what has been reset would be far more complicated.

But reseting stats is not expected to be a common operation, so this
seemed like an acceptable tradeoff (and I'd argue it still is).

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pg_stat_database update stats_reset only by pg_stat_reset

From

张连壮

Date:

17 July 2019, 08:52:46

Yeah, I agree. this is not necessary, i will remove the commitfest at '2019-07-19'.

Tomas Vondra <tomas.vondra@2ndquadrant.com> 于2019年7月12日周五下午9:07写道：

On Fri, Jul 12, 2019 at 01:51:50PM +0900, Michael Paquier wrote:
>On Thu, Jul 11, 2019 at 04:34:20PM +0200, Daniel Verite wrote:
>> I can understand why you'd want that resetting the stats for a single object
>> would not reset the per-database timestamp, but this would revert a 8+ years
>> old decision that seems intentional and has apparently not been criticized
>> since then (based on searching for pg_stat_reset_single_table_counters in
>> the archives) . More opinions are probably needed in favor of this
>> change (or against, in which case the fate of the patch might be a
>> rejection).
>
>I agree with Daniel that breaking an 8-year-old behavior may not be of
>the taste of folks relying on the current behavior, particularly
>because we have not had complains about the current behavior being
>bad. So -1 from me.

Yeah, I agree. There are several reasons why it's done this way:

1) overhead

Now we only store a two timestamps - for a database and for bgwriter. We
could track a timestamp for each object, of course ...

2) complexity

Updating the timestamps would be fairly simple, but what about querying
the data? Currently you fetch the data, see if the stats_reset changed
since the last snapshot, and if not you're good. If it changed, you know
some object (or the whole db) has reset counters, so you can't rely on
the data being consistent.

If we had stats_reset for each object, figuring out which data is still
valid and what has been reset would be far more complicated.

But reseting stats is not expected to be a common operation, so this
seemed like an acceptable tradeoff (and I'd argue it still is).

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services