Re: autovacuum: 50% iowait for hours

On Thu, May 13, 2010 at 4:05 PM, Joao Ferreira
<joao(dot)miguel(dot)c(dot)ferreira(at)gmail(dot)com> wrote:
>
> Hello all,
>
> I have a hard situation in hands. my autovacuum does not seem to be able
> to get his job done;
>
> database is under active INSERTs/UPDATEs;
> CPU is in aprox 50% iowait for the past 5 hours;
>
> I've tried turning off autovacuum and the effect goes away; I turn it back
> on and it goes back to 50% iowait; my IO system is nothing special at all;
>
> besides turning autovacuum off and running vacuum by hand once in a while,
> what else can I do to get out of this situation ?
>
> bellow some logs
>
> I'm seriously considering turning off autovacuum for good; but I'dd like
> to get input concerning other approaches... I mean... if I don't turn it
> of, how can I be sure this will not happen again... we ship products with
> PG inside... I must be absolutelly sure this will not ever happen in any of
> our costumers. I'm a bit confuse... sorry :) !

Have you considered tuning autovacuum to not use less IO so that it
has no serious impact on other running pg processes? it's pretty easy
to do, just don't go crazy (i.e. move autovacuum_vacuum_cost_delay
from 10 to 20 or 30 ms, not 2000ms)