Re: autovacuum: 50% iowait for hours

Joao Ferreira gmail wrote:
> 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.
>

It's impossible to tune something as complicated as vacuuming for this
range of workloads with a single configuration of settings.  The best
you could do is write a script that monitored each individual system and
tweaked itself based on activity.  That is however a very hard problem
to solve.

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

The autovacuum in 8.1 barely works, if you set it carefully to match the
system workload.  That means that in your case, it effectively doesn't
work, because you don't have enough information to do that.

If they're already working for you, I think that scheduled vacuums done
at known off-hours periods are as good as you're going to do here.

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

Just make sure you're producing good backups of these systems then.  You
can expect to run into one of the data eating bugs in 8.1 eventually.
You'll probably have a corrupt database from the sort of fsync issues
cheap storage introduces too.  Not upgrading from 8.1.4 is effectively
saying "I don't care about the integrity of my database", so you really
need to make alternate plans to help there.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us