Re: CPU-intensive autovacuuming - Mailing list pgsql-general

From Matthew T. O'Connor
Subject Re: CPU-intensive autovacuuming
Date
Msg-id 42A5C4B6.8070908@zeut.net
Whole thread Raw
In response to Re: CPU-intensive autovacuuming  (Phil Endecott <spam_from_postgresql_general@chezphil.org>)
List pgsql-general
Phil Endecott wrote:

> Matthew T. O'Connor wrote:
>
>> The integrated version of autovacuum that didn't make the cut before
>> 8.0 avoids this problem since the autovacuum data is stored in the
>> database.
>
>
> What is the status of this?  Is it something that will be included in
> 8.1 or 8.0.n?  I might be able to patch the current code but that
> doesn't seem like a useful thing to do if a better solution will
> arrive eventually.  I am currently running vacuums from a cron job and
> I think I will be happy with that for the time being.


This is a good question :-)  I have been so busy with work lately that I
have not been able to work on it.  I am currently trying to resurrect
the patch I sent in for 8.0 and update it so that it applies against
HEAD.  Once that is done, I will need help from someone with the
portions of the work that I'm not comfortable / capable of.   The main
issue with the version I created during the 8.0 devel cycle it used
libpq to connect, query and issue commands against the databases.  This
was deemed bad, and I need help setting up the infrastructure to make
this happen without libpq.  I hope to have my patch applying against
HEAD sometime this week but it probably won't happen till next week.

So the summary of the autovacuum integration status is that we are fast
running out of time (feature freeze July 1), and I have very little time
to devote to this task.  So you might want to submit your O(n) patch
cause unfortunately it looks like integrated autovacuum might slip
another release unless someone else steps up to work on it.


> (Incidentally, I have also found that the indexes on my pg_attributes
> table were taking up over half a gigabyte, which came down to less
> than 40 megs after reindexing them.  Is there a case for having
> autovacuum also call reindex?)


Yes there is certainly some merit to having autovacuum or something
similar perform other system maintenance tasks such as reindexing.  I
just haven't taken it there yet.  It does seem strange that your
pg_attributes table go that big, anyone have any insight here?  You did
say you are using 7.4.2, I forget it that has the index reclaiming code
in vacuum, also there are some autovacuum bugs in the early 7.4.x
releases.  You might try to upgrade to either 8.0.x or a later 7.4.x
release.


Matthew O'Connor



pgsql-general by date:

Previous
From: brew@theMode.com
Date:
Subject: Debian Stable goes from Woody to Sarge!!
Next
From: Bruce Momjian
Date:
Subject: Re: CPU-intensive autovacuuming