Re: Vacuuming problems on TOAST table

Tom Lane wrote:
> Ofer Israeli <oferi(at)checkpoint(dot)com> writes:
>> During our testing we see that the table size increases
>> substantially. When looking at the autovacuum log, set with default
>> configuration, it seems that it ran for around 60 seconds (see below
>> and note that this was a 1-minute test, i.e. only 100 updates)!
>
> autovacuum is intended to run fairly slowly, so as to not consume too
> much resources. If you think it's too slow you can adjust the
> autovacuum_cost tunables.
>
>> When setting a higher cost for the autovacuum, tried values of 2000,
>> it ran for even longer: ~400 seconds!
>
> That's the wrong direction, no?

The settings we used were not in the postgresql.conf file, but rather an update of the pg_autovacuum table where we set the vac_cost_limit to 2000. The reason for this being that we wanted this definition only for the big (TOASTed) table I was referring to.

The logged settings in the ~400 second case were:
autovac_balance_cost(pid=6224 db=16385, rel=17881, cost_limit=10, cost_delay=1)

Which comes as quite a surprise as it seems that the cost_limit is not set or am I missing something?

Thanks,
Ofer