Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Re: [PORTS] vacuum takes too long

From: The Hermit Hacker <scrappy(at)hub(dot)org>
To: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
Cc: David Wetzel <dave(at)turbocat(dot)de>, ports(at)postgreSQL(dot)org, hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] Re: [PORTS] vacuum takes too long
Date: 1999-01-07 02:30:57
Message-ID: Pine.BSF.4.05.9901062204440.417-100000@thelab.hub.org (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-ports
On Wed, 6 Jan 1999, Bruce Momjian wrote:

> > > From: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
> > 
> > > > insert a few row in a table (>50000) and do
> > > > delete from mytable;
> > > > vacuum verbose  analyze;
> > > >
> > > > Why is this that slow?
> > >
> > > Analyze checks every column in every row.
> > 
> > even if you only type "vacuum verbose" it takes _very_ long.
> > 
> > I deleted _all_ records with "delete from mytable;" before.
> > A drop and a new create is faster. But what is when you delete (maybe  
> > 100000) rows but keep 100 in the table?
> > 
> > I use 6.4.2 on NetBSD/i486 (that box makes gets 12MBytes/sec via the  
> > filesystem out of the drives)
> 
> Not sure what to say.  Vacuum does take a while, and it is often faster
> to drop and recreate.

Let's ignore the 'analyze' part first...take a simple 'vacuum'
command...what takes the longest?  My understanding is a vacuum
simplistically, takes and moves all rows "up" in the file to fill in any
blanks resulting from updates and deletes, then truncates the end of the
file...

If so, is there no way of having vacuum running on its own?  

Basically, if my understanding is remotely correct, vaccum is
defragmenting the table...so why can't the defragmenting be performed
during idle time...or, at least some of it.  

Start at the top of the table, go to the first 'blank' section (a deleted
record)...find the next good record that will fit in the space, move it
there...clear out the old space, etc...

if dba issues a 'vacuum', lock the table and do all records at once, but
otherwise try and vacuum the table live...

With the new MVCC serialization, this concept should be less intrusive on
readers, no?

Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy(at)hub(dot)org           secondary: scrappy(at){freebsd|postgresql}.org 


In response to

Responses

pgsql-ports by date

Next:From: Bruce MomjianDate: 1999-01-07 02:36:31
Subject: Re: [HACKERS] Re: [PORTS] vacuum takes too long
Previous:From: Unprivileged userDate: 1999-01-07 01:35:01
Subject: Port Bug Report: The postgres server crashes

pgsql-hackers by date

Next:From: Bruce MomjianDate: 1999-01-07 02:36:31
Subject: Re: [HACKERS] Re: [PORTS] vacuum takes too long
Previous:From: Tom LaneDate: 1999-01-07 01:16:52
Subject: Re: [HACKERS] Libpq functions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group