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

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
><alvherre(at)2ndquadrant(dot)com> 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@mail.gmail.com
>> > >
>> > > 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