Robert Haas wrote: > On Wed, Jun 8, 2011 at 10:55 PM, Euler Taveira de Oliveira > <eu...@timbira.com> wrote: > > Em 08-06-2011 20:35, Robert Haas escreveu: > >> Is the hint correct? ?I mean, what if there were 100 small tables that > >> needed vacuuming all at the same time. ?We'd hit this limit no matter > >> how high you set autovacuum_max_workers, but it wouldn't be right to > >> set it to 101 just because every once in a blue moon you might trip > >> over the limit. > >> > > I think so. You are picturing a scene with only one message. It is the same > > case of the too-frequent-checkpoint messages; i.e., you should look if those > > messages have some periodicity. > > Yeah, maybe. I'm just not sure there would be an easy way for users > to judge when they should or should not make a change. > > >> I think it'd be really useful to expose some more data in this area > >> though. ?One random idea is - remember the time at which a table was > >> first observed to need vacuuming. Clear the timestamp when it gets > >> vacuumed. ?Then you can do: > >> > > Hmmm. But this fine grained information alone doesn't help tuning the number > > of autovacuum workers. I consider counters easier to implement and simpler > > to analyze. But the timestamp idea has its merit because we already have a > > similar statistic (last timestamp table was vacuumed or analyzed). > > Well, it won't directly tell you how many you need. But certainly if > you see things getting further and further behind, you know you need > more. > > Or, alternatively, you need to reduce vacuum_cost_delay. IME, that's > actually the most common cause of this problem.
This thread from June died because there was concern about the overhead of additional autovacuum statistics, and I have to say I am not super-excited about it either because most users will not use them. Ideally we would have something like checkpoint_warning that warns users in the log when there are too few autovacuum workers and cleanup is being delayed. The big trick is how to accurately measure this. The amount of time that a table waits to be vacuumed probably isn't relevant enough --- it might have been days since it was last vacuumed, and waiting 10 minutes isn't a big deal, so it is hard to say what _scale_ we would give users for that warning that would make sense. We could compare it to the time since the last autovacuum, but if the table is suddently heavily modified, that doesn't help either. I think it has to drive off of the 'n_dead_tuples' statistic value for the table. I was toying with the idea of comparing the n_dead_tuples value at the time the table is first scanned for autovacuum consideration, and the value at the time an autovacuum worker actually starts scanning the table. The problem there is that if someone does a massive DELETE in that time interval, or does an UPDATE on all the rows, it would think that autovacuum should have been there to mark some dead rows, but it was not. In the case of DELETE, having autovacuum work earlier would not have helped, but it would have helped in the UPDATE case. We could look at table size growth during that period. If the autovacuum had run earlier, we would have used that dead space, but is wasn't recorded by autovacuum yet, but again, it seems vague. Ideas? -- Bruce Momjian <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
