Re: tuning autovacuum

On Wed, Jun 8, 2011 at 10:55 PM, Euler Taveira de Oliveira
<euler(at)timbira(dot)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.

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