Re: Autovacuum issues with truncate and create index ...

Baptiste LHOSTE wrote:

> These queries are very simple : delete from table where
> start_date < availableTimestamp. We performed an EXPLAIN to try
> to understand what could be the problem. The query planner said
> that the index on start_date could not be used because it was not
> up-to-date.

Could you show that output you base that on?

> How a server (8 CPUs) which has a 0.56 load over the last 15
> minutes could not handle 3 autovacuum processes, for me it is
> very confusing.

When the bottleneck is disk I/O the CPUs count is not going to
help. Threads which have not been context-switched out, but are
sitting waiting for the electric motors to drag the disk arm to the
right cylinder probably don't count against the load average.

Note that while three autovacuum processes normally don't cause any
grief, you seem to be near the tipping point anyway, so it may be a
case of "the straw that broke the camel's back". Especially since
you made autovacuum many times more resource-hungry than it is by
default.

-Kevin