Обсуждение: Should the docs have a warning about pg_stat_reset()?
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@mail.gmail.com -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
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
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
On Thu, 28 Mar 2019 at 10:33, Alvaro Herrera <alvherre@2ndquadrant.com> 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
On Wed, Mar 27, 2019 at 7:49 PM David Rowley <david.rowley@2ndquadrant.com> 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
On Sat, 30 Mar 2019 at 00:59, Robert Haas <robertmhaas@gmail.com> wrote: > > On Wed, Mar 27, 2019 at 7:49 PM David Rowley > <david.rowley@2ndquadrant.com> 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@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. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Thu, Apr 11, 2019 at 04:14:11AM +1200, David Rowley wrote: > On Sat, 30 Mar 2019 at 00:59, Robert Haas <robertmhaas@gmail.com> wrote: > > > > On Wed, Mar 27, 2019 at 7:49 PM David Rowley > > <david.rowley@2ndquadrant.com> 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@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? -- Bruce Momjian <bruce@momjian.us> 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 +
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. IMO the answer for $SUBJECT is yes. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Em qua, 10 de abr de 2019 às 16:33, Alvaro Herrera <alvherre@2ndquadrant.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. > 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
On Thu, 11 Apr 2019 at 07:33, Alvaro Herrera <alvherre@2ndquadrant.com> 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
Вложения
On Thu, 11 Apr 2019 at 06:52, Bruce Momjian <bruce@momjian.us> 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
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
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@2ndquadrant.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
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
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
On Wed, Apr 10, 2019 at 2:52 PM Bruce Momjian <bruce@momjian.us> 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