pg_stat_database update stats_reset only by pg_stat_reset

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

pg_stat_database update stats_reset only by pg_stat_reset

张连壮
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.

0001-pg_stat_database-update-stats_reset-only-by-pg_stat_.patch (1K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: pg_stat_database update stats_reset only by pg_stat_reset

张连壮
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.


张连壮 <[hidden email]> 于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.
Reply | Threaded
Open this post in threaded view
|

Re: pg_stat_database update stats_reset only by pg_stat_reset

Daniel Verite
        张连壮 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 <[hidden email]>
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


Reply | Threaded
Open this post in threaded view
|

Re: pg_stat_database update stats_reset only by pg_stat_reset

Michael Paquier-2
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

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: pg_stat_database update stats_reset only by pg_stat_reset

Tomas Vondra-4
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



Reply | Threaded
Open this post in threaded view
|

Re: pg_stat_database update stats_reset only by pg_stat_reset

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

Tomas Vondra <[hidden email]> 于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