Re: autovacuum: 50% iowait for hours

Hello guys,

thx for your inputs. I consider you suggestions valid.

We have hundreds or thousands of unreachable and unmaintained PG
instalations. I'm totally unable to experiment in each of them. Usage
profile can range from 100 rows per hour to 1000, 10.000, 50.000...
sustained... for several days... or even forever... CPU's and IO
subsystem also varies.. from cheap IO to fast enterprise grade hardware.

But I did try vacuum_cost_delay and the effect is as expected: IOWAIT
reduces :) but vacuum time increases even more :( but it still does not
leave the processor alone; autovaccum is still not able to finish it's
job... and I can't wait forever.

We are using pg 8.1.4, and yes we a) reindex b) vacuum full and c)
reindex again once a week up to once a month; this weekly/monthly
maintenance script has been quite effective as a workaround for the
"can't find parent index" bug... we can eliminate heavy bloats just by
running the script... but it also plays an important role in keeping
database correctly indexed (reindex) and responsive.

My experience with this scenario tells me (I may be wrong) that I don't
really need autovacuum as long as I keep those periodic maintenance
operations running (tipically Saturdays or Sundays during the night)

For several reasons I can _not_ upgrade pg. I must use 8.1.4 and just
live with it. And learn to work around the issues it might bring me.
Additionally I can not change the cheap storage we ship in some lower
end versions of our product.

So, I'm still considering turning off autovacuum.

any thoughts ?

I really appreciate the discussion. thx a lot.

Cheers
Joao

On Fri, 2010-05-14 at 06:47 -0400, Scott Mead wrote:
>
>
> 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 (inmost 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
>
>