Re: vacuuming problems continued

Andrew Sullivan wrote:
> On Thu, Jun 01, 2006 at 01:54:08PM +0200, Antoine wrote:
>> Hi,
>> We just don't seem to be getting much benefit from autovacuum. Running
>> a manual vacuum seems to still be doing a LOT, which suggests to me
>> that I should either run a cron job and disable autovacuum, or just
>> run a cron job on top of autovacuum.

Don't know if this was covered in an earlier thread. Bear with me if so.

I'm working with 7.4.8 and 8.0.3 systems, and pg_autovacuum does have some
glitches ... in part solved by the integrated autovac in 8.1:

- in our env, clients occasionally hit max_connections. This is a known and
(sort of) desired pushback on load. However, that sometimes knocks pg_autovacuum
out.

- db server goes down for any reason: same problem.

Just restarting pg_autovacuum is not good enough; when pg_autovacuum terminates,
it loses its state, so big tables that change less than 50% between such
terminations may never get vacuumed (!)

For that reason, it's taken a switch to a Perl script run from cron every 5
minutes, that persists state in a table. The script is not a plug-compatible
match for pg_autovacuum (hardcoded rates; hardcoded distinction between user and
system tables), but you may find it useful.

--
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.