	After the comment by someone about the UPDATE being responsible for
the reason for vacuuming (sorry, I didn't know that), I looked into a stored
procedure that gets triggered during an insert. The stored procedure does an
UPDATE on another table, for every insert. So inserting 100,000 items into
the table causes an update on 100,000 items in another table. I noticed that
the other table's file size gets very large (right now it's over a megabyte
and only 10% complete inserting), even though there are only about 5 items
in that table. Since that table has the UPDATE happening to it, it's getting
large. A vacuum chops it down to 8K.
	I tried increasing the buffer size, and that made the 100,000
inserts (with the corresponding update) go longer before hitting the barrier
and slowing down tremendously (until another vacuum is done).

	Since vacuum isn't tied to a time, but rather the size of the
buffers? or the indices? it would seem plausible to do as another person had
mentioned and have vacuum kick off when the buffers are xx% full.

Mike



-----Original Message-----
From: Zeugswetter Andreas SB [mailto:ZeugswetterA@wien.spardat.at]
Sent: Wednesday, June 13, 2001 1:04 AM
To: 'Mike Cianflone'; Hackers List
Subject: AW: [HACKERS] vacuum



> 	Is there a relative consensus for how often to run vacuum? I have a
> table of about 8 columns that I fill with 100,000 items simply via a "\i
> alarms.sql". After 1,000 items or so it gets extremely slow to fill with
> data, and will take over a day to fill the entire thing unless I run
vacuum
> once a minute.

You will have to tell us, what exactly your alarms.sql does, and what
indexes 
your table has. Above behavior is certainly not to be expected in general,
especially the "vacuum once a minute" is highly suspicious.

For a series of insert only statements, the vacuum is not supposed to help
at 
all, thus there must be an update hidden somewhere. 

Andreas

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Wed, 13 Jun 2001 12:16:43 -0700
Mike Cianflone <mcianflone@littlefeet-inc.com>
RE: vacuum
Mike Cianflone writes:

> 	After the comment by someone about the UPDATE being responsible for
> the reason for vacuuming (sorry, I didn't know that), I looked into a stored
> procedure that gets triggered during an insert. The stored procedure does an
> UPDATE on another table, for every insert. So inserting 100,000 items into
> the table causes an update on 100,000 items in another table. I noticed that
> the other table's file size gets very large (right now it's over a megabyte
> and only 10% complete inserting), even though there are only about 5 items
> in that table. Since that table has the UPDATE happening to it, it's getting
> large. A vacuum chops it down to 8K.

An UPDATE basically acts as "append and mark old record obsolete".  This
is the so-called non-overwriting storage manager which keeps all data,
even deleted data, indefinitely until you run vacuum (hence the name).

> 	I tried increasing the buffer size, and that made the 100,000
> inserts (with the corresponding update) go longer before hitting the barrier
> and slowing down tremendously (until another vacuum is done).

Increasing the buffer size will keep the data in memory longer before
pushing it out to disk.  The net result will be the same though.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Wed, 13 Jun 2001 22:55:45 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
RE: vacuum

Now that you've narrowed it down to a specific table, at least you can
specifically vacuum just that table and ignore the rest of the database
...might help a bit?

On Wed, 13 Jun 2001, Mike Cianflone wrote:

> 	After the comment by someone about the UPDATE being responsible for
> the reason for vacuuming (sorry, I didn't know that), I looked into a stored
> procedure that gets triggered during an insert. The stored procedure does an
> UPDATE on another table, for every insert. So inserting 100,000 items into
> the table causes an update on 100,000 items in another table. I noticed that
> the other table's file size gets very large (right now it's over a megabyte
> and only 10% complete inserting), even though there are only about 5 items
> in that table. Since that table has the UPDATE happening to it, it's getting
> large. A vacuum chops it down to 8K.
> 	I tried increasing the buffer size, and that made the 100,000
> inserts (with the corresponding update) go longer before hitting the barrier
> and slowing down tremendously (until another vacuum is done).
>
> 	Since vacuum isn't tied to a time, but rather the size of the
> buffers? or the indices? it would seem plausible to do as another person had
> mentioned and have vacuum kick off when the buffers are xx% full.
>
> Mike
>
>
>
> -----Original Message-----
> From: Zeugswetter Andreas SB [mailto:ZeugswetterA@wien.spardat.at]
> Sent: Wednesday, June 13, 2001 1:04 AM
> To: 'Mike Cianflone'; Hackers List
> Subject: AW: [HACKERS] vacuum
>
>
>
> > 	Is there a relative consensus for how often to run vacuum? I have a
> > table of about 8 columns that I fill with 100,000 items simply via a "\i
> > alarms.sql". After 1,000 items or so it gets extremely slow to fill with
> > data, and will take over a day to fill the entire thing unless I run
> vacuum
> > once a minute.
>
> You will have to tell us, what exactly your alarms.sql does, and what
> indexes
> your table has. Above behavior is certainly not to be expected in general,
> especially the "vacuum once a minute" is highly suspicious.
>
> For a series of insert only statements, the vacuum is not supposed to help
> at
> all, thus there must be an update hidden somewhere.
>
> Andreas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org


Wed, 13 Jun 2001 18:24:10 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
RE: vacuum
On Wed, Jun 13, 2001 at 06:24:10PM -0300, The Hermit Hacker wrote:
> 
> Now that you've narrowed it down to a specific table, at least you can
> specifically vacuum just that table and ignore the rest of the database
> ...might help a bit?

Even better: since he's loading a script anyway, the script could start
a transaction, disable the trigger, load the data, bulk UPDATE the other
table, and re-enable the trigger. I bet it takes only a few minutes to
do the whole thing that way.

Ross

> 
> On Wed, 13 Jun 2001, Mike Cianflone wrote:
> 
> > 	After the comment by someone about the UPDATE being responsible for
> > the reason for vacuuming (sorry, I didn't know that), I looked into a stored
> > procedure that gets triggered during an insert. The stored procedure does an
> > UPDATE on another table, for every insert. So inserting 100,000 items into
> > the table causes an update on 100,000 items in another table. I noticed that
> > the other table's file size gets very large (right now it's over a megabyte
> > and only 10% complete inserting), even though there are only about 5 items
> > in that table. Since that table has the UPDATE happening to it, it's getting
> > large. A vacuum chops it down to 8K.
> > 	I tried increasing the buffer size, and that made the 100,000
> > inserts (with the corresponding update) go longer before hitting the barrier
> > and slowing down tremendously (until another vacuum is done).
> >
> > 	Since vacuum isn't tied to a time, but rather the size of the
> > buffers? or the indices? it would seem plausible to do as another person had
> > mentioned and have vacuum kick off when the buffers are xx% full.
> >
> > Mike
> >
> >
> >
> > -----Original Message-----
> > From: Zeugswetter Andreas SB [mailto:ZeugswetterA@wien.spardat.at]
> > Sent: Wednesday, June 13, 2001 1:04 AM
> > To: 'Mike Cianflone'; Hackers List
> > Subject: AW: [HACKERS] vacuum
> >
> >
> >
> > > 	Is there a relative consensus for how often to run vacuum? I have a
> > > table of about 8 columns that I fill with 100,000 items simply via a "\i
> > > alarms.sql". After 1,000 items or so it gets extremely slow to fill with
> > > data, and will take over a day to fill the entire thing unless I run
> > vacuum
> > > once a minute.
> >
> > You will have to tell us, what exactly your alarms.sql does, and what
> > indexes
> > your table has. Above behavior is certainly not to be expected in general,
> > especially the "vacuum once a minute" is highly suspicious.
> >
> > For a series of insert only statements, the vacuum is not supposed to help
> > at
> > all, thus there must be an update hidden somewhere.
> >
> > Andreas
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://www.postgresql.org/search.mpl
> >
> 
> Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

Tue, 19 Jun 2001 14:25:18 -0500
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: vacuum