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(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