pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
8.x Vaccum/Autovacuum settings
3+ messages / 3 participants
[nested] [flat]

* 8.x Vaccum/Autovacuum settings
@ 2006-06-24 22:03 Chris Hoover <revoohc@gmail.com>
  2006-06-25 12:01 ` Re: 8.x Vaccum/Autovacuum settings Dawid Kuroczko <qnex42@gmail.com>
  2006-06-28 00:25 ` Re: 8.x Vaccum/Autovacuum settings Jim Nasby <jnasby@pervasive.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Chris Hoover @ 2006-06-24 22:03 UTC (permalink / raw)
  To: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: 8.x Vaccum/Autovacuum settings
  2006-06-24 22:03 8.x Vaccum/Autovacuum settings Chris Hoover <revoohc@gmail.com>
@ 2006-06-25 12:01 ` Dawid Kuroczko <qnex42@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Dawid Kuroczko @ 2006-06-25 12:01 UTC (permalink / raw)
  To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin

On 6/25/06, Chris Hoover <revoohc@gmail.com> wrote:
>
> 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.

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: 8.x Vaccum/Autovacuum settings
  2006-06-24 22:03 8.x Vaccum/Autovacuum settings Chris Hoover <revoohc@gmail.com>
@ 2006-06-28 00:25 ` Jim Nasby <jnasby@pervasive.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Jim Nasby @ 2006-06-28 00:25 UTC (permalink / raw)
  To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2006-06-28 00:25 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-24 22:03 8.x Vaccum/Autovacuum settings Chris Hoover <revoohc@gmail.com>
2006-06-25 12:01 ` Dawid Kuroczko <qnex42@gmail.com>
2006-06-28 00:25 ` Jim Nasby <jnasby@pervasive.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox