From: | The Hermit Hacker <scrappy(at)hub(dot)org> |
---|---|
To: | Mike Cianflone <mcianflone(at)littlefeet-inc(dot)com> |
Cc: | "'Zeugswetter Andreas SB'" <ZeugswetterA(at)wien(dot)spardat(dot)at>, Hackers List <pgsql-hackers(at)postgresql(dot)org> |
Subject: | RE: vacuum |
Date: | 2001-06-13 21:24:10 |
Message-ID: | Pine.BSF.4.33.0106131823420.80800-100000@mobile.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-06-13 21:55:10 | Re: [PATCH] addition of text_inet, text_cidr and inet_set_masklen |
Previous Message | Peter Eisentraut | 2001-06-13 21:14:38 | Re: Re: [PATCHES] Fw: Isn't pg_statistic a security hole - Solution Proposal |