
> 	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

Wed, 13 Jun 2001 10:03:35 +0200
Zeugswetter Andreas SB  <ZeugswetterA@wien.spardat.at>
AW: vacuum
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?


Wed, 13 Jun 2001 06:54:16 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: AW: vacuum
The same question ... how's the size after you vacuum the tables/db ?

> 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* ...

TIA
Andy



Wed, 13 Jun 2001 17:49:33 +0700
"Andy Samuel" <andysamuel@geocities.com>
Re: AW: vacuum

8k ...

On Wed, 13 Jun 2001, Andy Samuel wrote:

> The same question ... how's the size after you vacuum the tables/db ?
>
> > 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* ...
>
> TIA
> Andy
>
>
>
> ---------------------------(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
>

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 08:55:32 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: AW: vacuum