Should the docs have a warning about pg_stat_reset()?

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

Should the docs have a warning about pg_stat_reset()?

David Rowley-3
As I mentioned in [1], I've had a few cases recently about auto-vacuum
not working. On the other thread, it was all about auto-vacuum being
configured to run too slowly.  The other culprit for auto-vacuum not
working is when people periodically use pg_stat_reset().

The problem with pg_stat_reset() is that it zeros n_dead_tup and
n_mod_since_analyze.  If say a user resets the stats on a monthly
basis then this can mean that tables that normally receive an
auto-vacuum any less frequently than once per month could never
receive an auto-vacuum... at least not until an anti-wraparound vacuum
gets hold of it.

The best I can think to do to try and avoid this is to put a giant
WARNING in the docs about either not using it or to at least run
ANALYZE after using it.

Does anyone else think this is a problem worth trying to solve?

[1] https://www.postgresql.org/message-id/CAKJS1f_YbXC2qTMPyCbmsPiKvZYwpuQNQMohiRXLj1r=8_rYvw@...

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: Should the docs have a warning about pg_stat_reset()?

Euler Taveira
Em ter, 26 de mar de 2019 às 09:54, David Rowley
<[hidden email]> escreveu:

>
> As I mentioned in [1], I've had a few cases recently about auto-vacuum
> not working. On the other thread, it was all about auto-vacuum being
> configured to run too slowly.  The other culprit for auto-vacuum not
> working is when people periodically use pg_stat_reset().
>
> The problem with pg_stat_reset() is that it zeros n_dead_tup and
> n_mod_since_analyze.  If say a user resets the stats on a monthly
> basis then this can mean that tables that normally receive an
> auto-vacuum any less frequently than once per month could never
> receive an auto-vacuum... at least not until an anti-wraparound vacuum
> gets hold of it.
>
It seems a bug^H^H^H new feature. The problem is if you keep resetting
statistic before reaching an ANALYZE threshold. In this case,
autoVACUUM was never triggered because we don't have stats. The
consequence is a huge bloat.

> The best I can think to do to try and avoid this is to put a giant
> WARNING in the docs about either not using it or to at least run
> ANALYZE after using it.
>
+1. I am afraid it is not sufficient.

> Does anyone else think this is a problem worth trying to solve?
>
I don't remember why we didn't consider table without stats to be
ANALYZEd. Isn't it the case to fix autovacuum? Analyze
autovacuum_count + vacuum_count = 0?

If at least autovacuum was also time-based, it should mitigate the
lack of statistic.


--
   Euler Taveira                                   Timbira -
http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Reply | Threaded
Open this post in threaded view
|

Re: Should the docs have a warning about pg_stat_reset()?

Peter Eisentraut-6
On 2019-03-26 16:28, Euler Taveira wrote:
> I don't remember why we didn't consider table without stats to be
> ANALYZEd. Isn't it the case to fix autovacuum? Analyze
> autovacuum_count + vacuum_count = 0?

When the autovacuum system was introduced, we didn't have those columns.
 But now it seems to make sense that a table with autoanalyze_count +
analyze_count = 0 should be a candidate for autovacuum even if the write
statistics are zero.  Obviously, this would have the effect that a
pg_stat_reset() causes an immediate autovacuum for all tables, so maybe
it's not quite that simple.

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


Reply | Threaded
Open this post in threaded view
|

Re: Should the docs have a warning about pg_stat_reset()?

Alvaro Herrera-9
On 2019-Mar-27, Peter Eisentraut wrote:

> On 2019-03-26 16:28, Euler Taveira wrote:
> > I don't remember why we didn't consider table without stats to be
> > ANALYZEd. Isn't it the case to fix autovacuum? Analyze
> > autovacuum_count + vacuum_count = 0?
>
> When the autovacuum system was introduced, we didn't have those columns.
>  But now it seems to make sense that a table with autoanalyze_count +
> analyze_count = 0 should be a candidate for autovacuum even if the write
> statistics are zero.  Obviously, this would have the effect that a
> pg_stat_reset() causes an immediate autovacuum for all tables, so maybe
> it's not quite that simple.

I'd say it would make them a candidate for auto-analyze; upon completion
of that, there's sufficient data to determine whether auto-vacuum is
needed or not.  This sounds like a sensible idea to me.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: Should the docs have a warning about pg_stat_reset()?

David Rowley-3
On Thu, 28 Mar 2019 at 10:33, Alvaro Herrera <[hidden email]> wrote:

>
> On 2019-Mar-27, Peter Eisentraut wrote:
>
> > On 2019-03-26 16:28, Euler Taveira wrote:
> > > I don't remember why we didn't consider table without stats to be
> > > ANALYZEd. Isn't it the case to fix autovacuum? Analyze
> > > autovacuum_count + vacuum_count = 0?
> >
> > When the autovacuum system was introduced, we didn't have those columns.
> >  But now it seems to make sense that a table with autoanalyze_count +
> > analyze_count = 0 should be a candidate for autovacuum even if the write
> > statistics are zero.  Obviously, this would have the effect that a
> > pg_stat_reset() causes an immediate autovacuum for all tables, so maybe
> > it's not quite that simple.
>
> I'd say it would make them a candidate for auto-analyze; upon completion
> of that, there's sufficient data to determine whether auto-vacuum is
> needed or not.  This sounds like a sensible idea to me.

Yeah, analyze, not vacuum.  It is a bit scary to add new ways for
auto-vacuum to suddenly have a lot of work to do.  When all workers
are busy it can lead to neglect of other duties.  It's true that there
won't be much in the way of routine vacuuming work for the database
the stats were just reset on, as of course, all the n_dead_tup
counters were just reset. However, it could starve other databases of
vacuum attention.  Anti-wraparound vacuums on the current database may
get neglected too.

I'm not saying let's not do it, I'm just saying we need to think of
what bad things could happen as a result of such a change.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: Should the docs have a warning about pg_stat_reset()?

Robert Haas
On Wed, Mar 27, 2019 at 7:49 PM David Rowley
<[hidden email]> wrote:

> Yeah, analyze, not vacuum.  It is a bit scary to add new ways for
> auto-vacuum to suddenly have a lot of work to do.  When all workers
> are busy it can lead to neglect of other duties.  It's true that there
> won't be much in the way of routine vacuuming work for the database
> the stats were just reset on, as of course, all the n_dead_tup
> counters were just reset. However, it could starve other databases of
> vacuum attention.  Anti-wraparound vacuums on the current database may
> get neglected too.
>
> I'm not saying let's not do it, I'm just saying we need to think of
> what bad things could happen as a result of such a change.

+1.  I think that if we documented that pg_stat_reset() is going to
trigger an auto-analyze of every table in your system, we'd have some
people who didn't read the documentation and unleashed a storm of
auto-analyze activity, and other people who did read the documentation
and then intentionally used it to unleash a storm of auto-analyze
activity.  Neither sounds that great.

I really wish somebody had the time and energy to put some serious
work on the problem of autovacuum scheduling in general.  Our current
algorithm is a huge improvement over what what we had before 8.3, but
that was a decade ago.  This particular issue strikes me as something
that is likely to be hard to solve with an isolated tweak.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Reply | Threaded
Open this post in threaded view
|

Re: Should the docs have a warning about pg_stat_reset()?

David Rowley-3
On Sat, 30 Mar 2019 at 00:59, Robert Haas <[hidden email]> wrote:

>
> On Wed, Mar 27, 2019 at 7:49 PM David Rowley
> <[hidden email]> wrote:
> > Yeah, analyze, not vacuum.  It is a bit scary to add new ways for
> > auto-vacuum to suddenly have a lot of work to do.  When all workers
> > are busy it can lead to neglect of other duties.  It's true that there
> > won't be much in the way of routine vacuuming work for the database
> > the stats were just reset on, as of course, all the n_dead_tup
> > counters were just reset. However, it could starve other databases of
> > vacuum attention.  Anti-wraparound vacuums on the current database may
> > get neglected too.
> >
> > I'm not saying let's not do it, I'm just saying we need to think of
> > what bad things could happen as a result of such a change.
>
> +1.  I think that if we documented that pg_stat_reset() is going to
> trigger an auto-analyze of every table in your system, we'd have some
> people who didn't read the documentation and unleashed a storm of
> auto-analyze activity, and other people who did read the documentation
> and then intentionally used it to unleash a storm of auto-analyze
> activity.  Neither sounds that great.

I still think we should start with a warning about pg_stat_reset().
People are surprised by this, and these are just the ones who notice:

https://www.postgresql.org/message-id/CAB_myF4sZpxNXdb-x=weLpqBDou6uE8FHtM0FVerPM-1J7phkw@...

I imagine there are many others just suffering from bloat due to
auto-vacuum not knowing how many dead tuples there are in the tables.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: Should the docs have a warning about pg_stat_reset()?

Bruce Momjian
On Thu, Apr 11, 2019 at 04:14:11AM +1200, David Rowley wrote:

> On Sat, 30 Mar 2019 at 00:59, Robert Haas <[hidden email]> wrote:
> >
> > On Wed, Mar 27, 2019 at 7:49 PM David Rowley
> > <[hidden email]> wrote:
> > > Yeah, analyze, not vacuum.  It is a bit scary to add new ways for
> > > auto-vacuum to suddenly have a lot of work to do.  When all workers
> > > are busy it can lead to neglect of other duties.  It's true that there
> > > won't be much in the way of routine vacuuming work for the database
> > > the stats were just reset on, as of course, all the n_dead_tup
> > > counters were just reset. However, it could starve other databases of
> > > vacuum attention.  Anti-wraparound vacuums on the current database may
> > > get neglected too.
> > >
> > > I'm not saying let's not do it, I'm just saying we need to think of
> > > what bad things could happen as a result of such a change.
> >
> > +1.  I think that if we documented that pg_stat_reset() is going to
> > trigger an auto-analyze of every table in your system, we'd have some
> > people who didn't read the documentation and unleashed a storm of
> > auto-analyze activity, and other people who did read the documentation
> > and then intentionally used it to unleash a storm of auto-analyze
> > activity.  Neither sounds that great.
>
> I still think we should start with a warning about pg_stat_reset().
> People are surprised by this, and these are just the ones who notice:
>
> https://www.postgresql.org/message-id/CAB_myF4sZpxNXdb-x=weLpqBDou6uE8FHtM0FVerPM-1J7phkw@...
>
> I imagine there are many others just suffering from bloat due to
> auto-vacuum not knowing how many dead tuples there are in the tables.

OK, let me step back.  Why are people resetting the statistics
regularly?  Based on that purpose, does it make sense to clear the
stats that effect autovacuum?

--
  Bruce Momjian  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +


Reply | Threaded
Open this post in threaded view
|

Re: Should the docs have a warning about pg_stat_reset()?

Alvaro Herrera-9
On 2019-Apr-10, Bruce Momjian wrote:

> On Thu, Apr 11, 2019 at 04:14:11AM +1200, David Rowley wrote:

> > I still think we should start with a warning about pg_stat_reset().
> > People are surprised by this, and these are just the ones who notice:
> >
> > https://www.postgresql.org/message-id/CAB_myF4sZpxNXdb-x=weLpqBDou6uE8FHtM0FVerPM-1J7phkw@...
> >
> > I imagine there are many others just suffering from bloat due to
> > auto-vacuum not knowing how many dead tuples there are in the tables.
>
> OK, let me step back.  Why are people resetting the statistics
> regularly?  Based on that purpose, does it make sense to clear the
> stats that effect autovacuum?

I agree that we should research that angle.  IMO resetting stats should
be seriously frowned upon.  And if they do need to reset some counters
for some valid reason, offer a mechanism that leaves the autovac-
guiding counters alone.

IMO the answer for $SUBJECT is yes.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: Should the docs have a warning about pg_stat_reset()?

Euler Taveira
Em qua, 10 de abr de 2019 às 16:33, Alvaro Herrera
<[hidden email]> escreveu:

>
> On 2019-Apr-10, Bruce Momjian wrote:
>
> > On Thu, Apr 11, 2019 at 04:14:11AM +1200, David Rowley wrote:
>
> > > I still think we should start with a warning about pg_stat_reset().
> > > People are surprised by this, and these are just the ones who notice:
> > >
> > > https://www.postgresql.org/message-id/CAB_myF4sZpxNXdb-x=weLpqBDou6uE8FHtM0FVerPM-1J7phkw@...
> > >
> > > I imagine there are many others just suffering from bloat due to
> > > auto-vacuum not knowing how many dead tuples there are in the tables.
> >
> > OK, let me step back.  Why are people resetting the statistics
> > regularly?  Based on that purpose, does it make sense to clear the
> > stats that effect autovacuum?
>
> I agree that we should research that angle.  IMO resetting stats should
> be seriously frowned upon.  And if they do need to reset some counters
> for some valid reason, offer a mechanism that leaves the autovac-
> guiding counters alone.
>
Then you have to change the way pg_stat_reset() works (it currently
removes the hash tables). Even pg_stat_reset_single_table_counters()
could cause trouble although it is in a smaller proportion. Reset
statistics leaves autovacuum state machine in an invalid state. Since
reset statistic is a rare situation (at least I don't know monitoring
tools or practices that regularly execute those functions), would it
be worth adding complexity to pg_stat_reset* functions? autovacuum
could handle those rare cases just fine.

> IMO the answer for $SUBJECT is yes.
>
+1. However, I also suggest a WARNING saying "autovacuum won't work
because you reset statistics that it depends on" plus detail "Consider
executing ANALYZE on all your tables" / "Consider executing ANALYZE on
table foo.bar".


--
   Euler Taveira                                   Timbira -
http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento


Reply | Threaded
Open this post in threaded view
|

Re: Should the docs have a warning about pg_stat_reset()?

David Rowley-3
In reply to this post by Alvaro Herrera-9
On Thu, 11 Apr 2019 at 07:33, Alvaro Herrera <[hidden email]> wrote:
> IMO the answer for $SUBJECT is yes.

Here's what I had in mind.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

pg_stat_reset_warning.patch (1K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Should the docs have a warning about pg_stat_reset()?

David Rowley-3
In reply to this post by Bruce Momjian
On Thu, 11 Apr 2019 at 06:52, Bruce Momjian <[hidden email]> wrote:
>
> OK, let me step back.  Why are people resetting the statistics
> regularly?  Based on that purpose, does it make sense to clear the
> stats that effect autovacuum?

I can't speak for everyone, but once upon a time when I first started
using PostgreSQL, to monitor the application's use of the database I
recorded the output of pg_stat_user_tables once per day and then reset
the statistics.  It was useful to know the number of inserted tuples,
seq scans, index scans etc so I could understand the load on the
database better.   Of course, nowadays with LEAD()/LAG() it's pretty
easy to find the difference from the previous day. I'd have done it
differently if those had existed back then.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: Should the docs have a warning about pg_stat_reset()?

Vik Fearing-4
In reply to this post by Peter Eisentraut-6
On 27/03/2019 22:28, Peter Eisentraut wrote:

> On 2019-03-26 16:28, Euler Taveira wrote:
>> I don't remember why we didn't consider table without stats to be
>> ANALYZEd. Isn't it the case to fix autovacuum? Analyze
>> autovacuum_count + vacuum_count = 0?
>
> When the autovacuum system was introduced, we didn't have those columns.
>  But now it seems to make sense that a table with autoanalyze_count +
> analyze_count = 0 should be a candidate for autovacuum even if the write
> statistics are zero.  Obviously, this would have the effect that a
> pg_stat_reset() causes an immediate autovacuum for all tables, so maybe
> it's not quite that simple.

Not just pg_stat_reset() but also on promotion.
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Reply | Threaded
Open this post in threaded view
|

Re: Should the docs have a warning about pg_stat_reset()?

Tomas Vondra-4
In reply to this post by Euler Taveira
On Wed, Apr 10, 2019 at 08:09:34PM -0300, Euler Taveira wrote:

>Em qua, 10 de abr de 2019 às 16:33, Alvaro Herrera
><[hidden email]> escreveu:
>>
>> On 2019-Apr-10, Bruce Momjian wrote:
>>
>> > On Thu, Apr 11, 2019 at 04:14:11AM +1200, David Rowley wrote:
>>
>> > > I still think we should start with a warning about pg_stat_reset().
>> > > People are surprised by this, and these are just the ones who notice:
>> > >
>> > > https://www.postgresql.org/message-id/CAB_myF4sZpxNXdb-x=weLpqBDou6uE8FHtM0FVerPM-1J7phkw@...
>> > >
>> > > I imagine there are many others just suffering from bloat due to
>> > > auto-vacuum not knowing how many dead tuples there are in the tables.
>> >
>> > OK, let me step back.  Why are people resetting the statistics
>> > regularly?  Based on that purpose, does it make sense to clear the
>> > stats that effect autovacuum?
>>
>> I agree that we should research that angle.  IMO resetting stats should
>> be seriously frowned upon.  And if they do need to reset some counters
>> for some valid reason, offer a mechanism that leaves the autovac-
>> guiding counters alone.
>>
>Then you have to change the way pg_stat_reset() works (it currently
>removes the hash tables). Even pg_stat_reset_single_table_counters()
>could cause trouble although it is in a smaller proportion. Reset
>statistics leaves autovacuum state machine in an invalid state. Since
>reset statistic is a rare situation (at least I don't know monitoring
>tools or practices that regularly execute those functions), would it
>be worth adding complexity to pg_stat_reset* functions? autovacuum
>could handle those rare cases just fine.
>

Yeah, resetting most of the stats but keeping a couple of old values
around is going to do more harm than good. Even resetting stats for a
single object is annoying when you have to analyze the data, making it
even more granular by keeping some fields is just complicating it
further ...

The main reason why people do this is that we only provide cumulative
counters, so if you need to monitor how it changed in a given time
period (last hour, day, ...) you need to compute the delta somehow. And
just resetting the stats is the easiest way to achieve that.

+1 to have a warning about this, and maybe we should point people to
tools regularly snapshotting the statistics and computing the deltas for
them. There's a couple of specialized ones, but even widely available
monitoring tools will do that.

If only we had a way to regularly snapshot the data from within the
database, and then compute the deltas on that. If only we could insert
data from one table into another one a then do some analysics on it,
with like small windows moving over the data or something ...

regards

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


Reply | Threaded
Open this post in threaded view
|

Re: Should the docs have a warning about pg_stat_reset()?

Joe Conway
On 4/13/19 3:42 PM, Tomas Vondra wrote:
> If only we had a way to regularly snapshot the data from within the
> database, and then compute the deltas on that. If only we could insert
> data from one table into another one a then do some analysics on it,
> with like small windows moving over the data or something ...

Why not store deltas separately with their own delta reset command?

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


Reply | Threaded
Open this post in threaded view
|

Re: Should the docs have a warning about pg_stat_reset()?

Tomas Vondra-4
On Sun, Apr 14, 2019 at 09:11:52AM -0500, Joe Conway wrote:
>On 4/13/19 3:42 PM, Tomas Vondra wrote:
>> If only we had a way to regularly snapshot the data from within the
>> database, and then compute the deltas on that. If only we could insert
>> data from one table into another one a then do some analysics on it,
>> with like small windows moving over the data or something ...
>
>Why not store deltas separately with their own delta reset command?
>

Well, we could do that, but we don't. Essentially, we'd implement some
sort of RRD, but we'd have to handle cleanup, configuration (how much
history, how frequently to snapshot the deltas). I think the assumption
is people will do that in a third-party tool.

regards

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


Reply | Threaded
Open this post in threaded view
|

Re: Should the docs have a warning about pg_stat_reset()?

Jeff Janes
In reply to this post by Bruce Momjian
On Wed, Apr 10, 2019 at 2:52 PM Bruce Momjian <[hidden email]> wrote:

OK, let me step back.  Why are people resetting the statistics
regularly?  Based on that purpose, does it make sense to clear the
stats that effect autovacuum?

When I've done it (not regularly, thankfully), it was usually because I failed to type "pg_stat_reset_shared('bgwriter')" or  "pg_stat_statements_reset()" correctly.

I've also been tempted to do it because storing snapshots with a cron job or something requires effort and planning ahead to set up the tables and cron and some way to limit the retention, and than running LAG windows functions over the snapshots requires a re-study of the documentation, because they are a bit esoteric and I don't use them enough to commit the syntax to memory.  I don't want to see pg_statio_user_indexes often enough to make elaborate arrangements ahead of time (especially since track_io_timing columns is missing from it); but when I do want them, I want them.  And when I do, I don't want them to be "since the beginning of time".

When I'm thinking about pg_statio_user_indexes, I am probably not thinking about autovac, since they have about nothing in common with each other.  (Other than pg_stat_reset operating on both.)

Cheers,

Jeff