Thread: AW: 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
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?
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
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