Postgres Pro
Downloads
Home   >   mailing lists

Re: autovacuum: 50% iowait for hours - Mailing list pgsql-general

From Scott Mead
Subject Re: autovacuum: 50% iowait for hours
Date
Msg-id AANLkTin3kzB2HafpsTfTRnQJwbo0e3xuyX-23ZQvZ2qf@mail.gmail.com
Whole thread Raw
In response to Re: autovacuum: 50% iowait for hours  (Scott Marlowe <scott.marlowe@gmail.com>)
List pgsql-general
Tree view


On Thu, May 13, 2010 at 6:23 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Thu, May 13, 2010 at 4:05 PM, Joao Ferreira
<joao.miguel.c.ferreira@gmail.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.  

pgsql-general by date:

Previous
From: Scott Mead
Date:
Subject: Re: autovacuum: 50% iowait for hours
Next
From: Leonardo F
Date:
Subject: Re: Authentication method for web app