Обсуждение: Re: fix pg_autovacuum
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>) "Las mujeres son como hondas: mientras más resistencia tienen, más lejos puedes llegar con ellas" (Jonas Nightingale, Leap of Faith)
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
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@alvh.no-ip.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 >)
"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
On Tue, Sep 06, 2005 at 06:34:44AM -0500, Ben Grimm wrote: > 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. Nope, about zero. Your best bet seems to be to help on the beta testing so 8.1 comes out quickly. -- Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com "En las profundidades de nuestro inconsciente hay una obsesiva necesidad de un universo lógico y coherente. Pero el universo real se halla siempre un paso más allá de la lógica" (Irulan)
On 9/6/05, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
That's alright -- I'll definitely be testing 8.1 when time permits, but since we only just now upgraded to 8.0... I doubt that any further upgrades will be in my near future.
But I was thinking about it this morning I ended up writing a replacement in perl. It follows essentially the same rules that pg_autovacuum does, but only one db at a time (since that meets my needs) and requires a table to snapshot stats for the incremental comparisons, and I used a view just to make the logic a bit simpler. It's been running for 12 hours and my database is just as vacuumed/analyzed as it ever was, and essentially zero load - so no complaints here. It's attached if you're interested -- I'm guessing that it works much the same way as what you've done for 8.1.
-- Ben
On Tue, Sep 06, 2005 at 06:34:44AM -0500, Ben Grimm wrote:
> 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.
Nope, about zero. Your best bet seems to be to help on the beta testing
so 8.1 comes out quickly.
That's alright -- I'll definitely be testing 8.1 when time permits, but since we only just now upgraded to 8.0... I doubt that any further upgrades will be in my near future.
But I was thinking about it this morning I ended up writing a replacement in perl. It follows essentially the same rules that pg_autovacuum does, but only one db at a time (since that meets my needs) and requires a table to snapshot stats for the incremental comparisons, and I used a view just to make the logic a bit simpler. It's been running for 12 hours and my database is just as vacuumed/analyzed as it ever was, and essentially zero load - so no complaints here. It's attached if you're interested -- I'm guessing that it works much the same way as what you've done for 8.1.
-- Ben