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)
Import Notes
Reply to msg id not found: ce545eac050729135430743a3a@mail.gmail.comReference msg id not found: ce545eac050729135430743a3a@mail.gmail.com
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
Show quoted text
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 <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?
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:
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