Re: fix pg_autovacuum

Hi Alvaro,

Is there any chance of backporting the integrated version to 8.0? We have
about 50,000 tables and the autovacuum daemon churns at 100% cpu for long
stretches... any improvement over that would be welcome.

Thanks,
Ben

On 7/29/05, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>
> On Fri, Jul 29, 2005 at 03:54:07PM -0500, Jeff Lund wrote:
>
> Hi,
>
> In the future please post to a list instead of asking me privately. I
> have picked a list arbitrarily to post the response.
>
> > I am a DBA using Postgres 8.0.3, and it is great. Could someone
> > tell me what is wrong or what the problems are with fix pg_autovacuum
> > O(n^2) behavior.
>
> The problem is that pg_autovacuum uses a query against the pg_class
> system catalog, and has to compare the results with its internal table
> list. This was done using a O(n^2) algorithm. I'm not sure if a fix
> was developed for this problem, but I suspect not.
>
> The integrated autovacuum process that has been developed for 8.1 does
> not have this problem because it uses a radically different approach to
> obtaining/keeping information. In fact, each iteration is a new
> process, so there's no table list kept in memory.
>
> I might add that I completely rewrote the autovacuum daemon, and I don't
> think a single line of the original code is present in the new version.
> The ideas, of course, are mostly the same. (For the time being at least
> -- there's no saying where the new code will evolve to.)
>
> --
> Alvaro Herrera (<alvherre[a]alvh.no-ip.org <http://ip.org>>)
> "Las mujeres son como hondas: mientras más resistencia tienen,
> más lejos puedes llegar con ellas" (Jonas Nightingale, Leap of Faith)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>