From: Jim C. Nasby <jnasby@pervasive.com>
To: pgsql-admin@postgresql.org
To: pgsql-performance@postgresql.org
Subject: Default autovacuum settings too conservative
Date: Wed, 1 Feb 2006 15:16:33 -0600
Message-ID: <20060201211633.GM95850@pervasive.com> (raw)
As I recall, the idea behind vacuum_threshold was to prevent
too-frequent vacuuming of small tables. I'm beginning to question this
reasoning:
Small tables vacuum very, very quickly, so 'extra' vacuuming is very
unlikely to hurt system performance.
Small tables are most likely to have either very few updates (ie: a
'lookup table') or very frequent updates (ie: a table implementing a
queue). In the former, even with vacuum_threshold = 0 vacuum will be a
very rare occurance. In the later case, a high threshold is likely to
cause a large amount of un-nececcasry bloat.
Also, vacuum_scale_factor of 0.4 seems unreasonably large. It means
tables will be 40% dead space, which seems excessively wasteful.
Something between 0.1 and 0.2 seems much better.
Has anyone looked at how effective these two settings are?
--
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jnasby@pervasive.com, pgsql-admin@postgresql.org
Subject: Re: Default autovacuum settings too conservative
In-Reply-To: <20060201211633.GM95850@pervasive.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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