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(at)wien(dot)spardat(dot)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(at)postgresql(dot)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(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly