Just curious,
What are most of you setting your vacuum and autovacuum parameters to for your 8.x databases. I just turned on autovacuuming on one of my db servers and went with a very conservative vacuum_cost_delay of 200 and vacuum_cost_limit of 50. I am wondering if anyone else has tested to find out just how far you can push your vacuum/autovacuum before you start to feel performance hits from running it?
Depends how much you're UPDATing (and DELETing). If much, then you will
hit the problem that you're VACUUMing too slow, and the residues from not
yet removed tuples will accumulate and you'll get the performance hit.
Say, if vacuum of 1mln rows take 2h, and you accumulate 0.5mln rows within
an hour, you could end up with:
t=0h 1.0mln rows, 0 dead
t=1h 1.5mln rows, 0.5mln dead, VACUUM starts (will take 3h)
t=2h 2mln rows, 1mln dead, vacuum 33%
t=3h 2.5mln rows, 1.5mln dead, vacuum 66%
t=4h 3mln rows, 2mln dead, vacuum 99%
2.5mln rows, 1.5mln dead, after vacuum, VACUUM starts (will take 5h)
t=5h 3mln rows, 2mln dead, vacuum 20%
t=6h 3.5mln rows, 2.5mln dead, vacuum 40%
t=7h 4mln rows, 3mln dead, vacuum 60%
t=8h 4.5mln rows, 3.5mln dead, vacuum 80%
t=9h 5mln rows, 4mln dead, vacuum 99%
3.5mln rows, 2.5mln dead, after vacuum, VACUUM starts (will take 7h)
...and so on...
so vacuum should run quick enough to remove dead tuples quicker than
they accumulate. For one busy DB, I use cost of 8000 and delay of 150,
which makes spindles busy, but does not cause the load to soar high...
Regards,
Dawid
...and so on.
