On Thu, May 13, 2010 at 6:23 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:
On Thu, May 13, 2010 at 4:05 PM, Joao FerreiraHave you considered tuning autovacuum to not use less IO so that it
<joao.miguel.c.ferreira@xxxxxxxxx> 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 :) !
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.
