Re: autovacuum: 50% iowait for hours

On Thu, May 13, 2010 at 6:23 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> 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)
>

+ 1 here, start with a 20ms delay, your vacuums make take a bit longer to
run, but they'll have less impact on I/O.

Just curious, what is your log_min_messages setting? I notice that you had
'DEBUG' in your logs, I'm guessing that you've just cranked up to DEBUG for
your testing.... make sure that you leave that 'warning' or 'notice' for
production, leaving those logs at DEBUG will also chew up I/O and get in the
way of things like autovacuum.

What version of Postgres are you using? The visibility map in 8.4 should
lower the amount of I/O that you're stuck with (in*most* cases) with vacuum.
Although you'll still need a full table scan to avoid xid wrap, you should
get away with only vacuuming changed blocks in the general case.

--
Scott Mead
EnterpriseDB
The Enterprise Postgres Company
www.enterprisedb.com

>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>