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