Обсуждение: 8.x Vaccum/Autovacuum settings
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?
Thanks,
Chris
P.S.
I chose these numbers based upon the documentation at http://www.powerpostgresql.com/Downloads/annotated_conf_80.html
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?
Thanks,
Chris
P.S.
I chose these numbers based upon the documentation at http://www.powerpostgresql.com/Downloads/annotated_conf_80.html
On 6/25/06, Chris Hoover <revoohc@gmail.com> wrote:
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.
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.
On Jun 24, 2006, at 5:03 PM, Chris Hoover wrote: > 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? It's highly, highly dependent on both your hardware and your workload. If you have some kind of metric for how well your app is performing, you can fire off a vacuum with different settings and see when it starts to slow down your application. If you're more write-constrained than read constrained, you'll want to bump up the cost of dirty pages, probably bumping up vacuum_cost_limit at the same time. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461