On Wed, 13 Jun 2001, Zeugswetter Andreas SB wrote:
>
> > 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.
the one question I'd have is what does the table size look like
before/after this 'once a minute vacuum'? I have one client that has
continuous UPDATEs happening to his table, no INSERTs, no DELETEs ... and
his tables quicklky grow from a 8k table to 65Meg if there is no vacuum
happening every few *hours* ...
The other question is, what are your startup parameters? What sort of
shared memory buffer are you working with? I image that VACUUM does a
flush to disk, so are 1000 items filling your buffer(s), causing an almost
continue fsync to disk for each INSERT after that ... so the VACUUM is
clearing the buffer(s) to disk, giving your space again to do quick
INSERTs for 1000 records again?