Thread: Warning about using pg_stat_reset() and pg_stat_reset_shared()
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? -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson
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] 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] https://www.postgresql.org/message-id/flat/CAKJS1f8DTbCHf9gedU0He6ARsd58E6qOhEHM1caomqj_r9MOiQ%40mail.gmail.com [2] https://www.postgresql.org/message-id/CAKJS1f80o98hcfSk8j%3DfdN09S7Sjz%2BvuzhEwbyQqvHJb_sZw0g%40mail.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] https://www.postgresql.org/message-id/flat/CAKJS1f8DTbCHf9gedU0He6ARsd58E6qOhEHM1caomqj_r9MOiQ%40mail.gmail.com > [2] https://www.postgresql.org/message-id/CAKJS1f80o98hcfSk8j%3DfdN09S7Sjz%2BvuzhEwbyQqvHJb_sZw0g%40mail.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
Attachment
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