Warning about using pg_stat_reset() and pg_stat_reset_shared()

On Wed, Sep 28, 2022 at 11:45 AM Bruce Momjian <bruce@momjian.us> wrote:

We have discussed the problems caused by the use of pg_stat_reset() and
pg_stat_reset_shared(), specifically the removal of information needed
by autovacuum. I don't see these risks documented anywhere. Should we
do that?

+1.

Are there other risks?

I don't know.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Thu, 29 Sept 2022 at 04:45, Bruce Momjian <bruce@momjian.us> wrote:

We have discussed the problems caused by the use of pg_stat_reset() and
pg_stat_reset_shared(), specifically the removal of information needed
by autovacuum. I don't see these risks documented anywhere. Should we
do that? Are there other risks?

There was some discussion in [1]/messages/by-id/CAKJS1f8DTbCHf9gedU0He6ARsd58E6qOhEHM1caomqj_r9MOiQ@mail.gmail.com a few years back. A few people were
for the warning. Nobody seemed to object to it. There's a patch in
[2]: /messages/by-id/CAKJS1f80o98hcfSk8j=fdN09S7Sjz+vuzhEwbyQqvHJb_sZw0g@mail.gmail.com

David

[1]: /messages/by-id/CAKJS1f8DTbCHf9gedU0He6ARsd58E6qOhEHM1caomqj_r9MOiQ@mail.gmail.com
[2]: /messages/by-id/CAKJS1f80o98hcfSk8j=fdN09S7Sjz+vuzhEwbyQqvHJb_sZw0g@mail.gmail.com

On Wed, Oct 5, 2022 at 11:07:49AM +1300, David Rowley wrote:

On Thu, 29 Sept 2022 at 04:45, Bruce Momjian <bruce@momjian.us> wrote:

We have discussed the problems caused by the use of pg_stat_reset() and
pg_stat_reset_shared(), specifically the removal of information needed
by autovacuum. I don't see these risks documented anywhere. Should we
do that? Are there other risks?

There was some discussion in [1] a few years back. A few people were
for the warning. Nobody seemed to object to it. There's a patch in
[2].

David

[1] /messages/by-id/CAKJS1f8DTbCHf9gedU0He6ARsd58E6qOhEHM1caomqj_r9MOiQ@mail.gmail.com
[2] /messages/by-id/CAKJS1f80o98hcfSk8j=fdN09S7Sjz+vuzhEwbyQqvHJb_sZw0g@mail.gmail.com

Ah, good point. I have slightly reworded the doc patch, attached.
However, the last line has me confused:

A database-wide <command>ANALYZE</command> is recommended after
the statistics have been reset.

As far as I can tell, analyze updates pg_statistics values, but not
pg_stat_all_tables.n_dead_tup and n_live_tup, which are used by
autovacuum to trigger vacuum operations. I am afraid we have to
recommand VACUUM ANALYZE after pg_stat_reset(), no?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson

On Wed, 12 Oct 2022 at 04:11, Bruce Momjian <bruce@momjian.us> wrote:

As far as I can tell, analyze updates pg_statistics values, but not
pg_stat_all_tables.n_dead_tup and n_live_tup, which are used by
autovacuum to trigger vacuum operations. I am afraid we have to
recommand VACUUM ANALYZE after pg_stat_reset(), no?

As far as I can see ANALYZE will update these fields. I'm looking at
pgstat_report_analyze() called from do_analyze_rel().

It does:

tabentry->n_live_tuples = livetuples;
tabentry->n_dead_tuples = deadtuples;

I also see it working from testing:

create table t as select x from generate_Series(1,100000)x;
delete from t where x > 90000;
select pg_sleep(1);
select n_live_tup,n_dead_tup from pg_stat_user_tables where relname = 't';
select pg_stat_reset();
select n_live_tup,n_dead_tup from pg_stat_user_tables where relname = 't';
analyze t;
select n_live_tup,n_dead_tup from pg_stat_user_tables where relname = 't';

The result of the final query is:

n_live_tup | n_dead_tup
------------+------------
90000 | 10000

Maybe the random sample taken by ANALYZE for your case didn't happen
to land on any pages with dead tuples?

David

On Wed, Oct 12, 2022 at 08:50:19AM +1300, David Rowley wrote:

On Wed, 12 Oct 2022 at 04:11, Bruce Momjian <bruce@momjian.us> wrote:

As far as I can tell, analyze updates pg_statistics values, but not
pg_stat_all_tables.n_dead_tup and n_live_tup, which are used by
autovacuum to trigger vacuum operations. I am afraid we have to
recommand VACUUM ANALYZE after pg_stat_reset(), no?

As far as I can see ANALYZE will update these fields. I'm looking at
pgstat_report_analyze() called from do_analyze_rel().

It does:

tabentry->n_live_tuples = livetuples;
tabentry->n_dead_tuples = deadtuples;

I also see it working from testing:

create table t as select x from generate_Series(1,100000)x;
delete from t where x > 90000;
select pg_sleep(1);
select n_live_tup,n_dead_tup from pg_stat_user_tables where relname = 't';
select pg_stat_reset();
select n_live_tup,n_dead_tup from pg_stat_user_tables where relname = 't';
analyze t;
select n_live_tup,n_dead_tup from pg_stat_user_tables where relname = 't';

The result of the final query is:

n_live_tup | n_dead_tup
------------+------------
90000 | 10000

Maybe the random sample taken by ANALYZE for your case didn't happen
to land on any pages with dead tuples?

Ah, good point, I missed that in pgstat_report_analyze(). I will apply
the patch then in a few days, thanks.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson

On Wed, Oct 12, 2022 at 12:04:08PM -0400, Bruce Momjian wrote:

Maybe the random sample taken by ANALYZE for your case didn't happen
to land on any pages with dead tuples?

Ah, good point, I missed that in pgstat_report_analyze(). I will apply
the patch then in a few days, thanks.

Patch applied back to PG 10, thanks.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson

On Tue, 18 Oct 2022 at 08:07, Bruce Momjian <bruce@momjian.us> wrote:

Patch applied back to PG 10, thanks.

Thanks.

David