Re: vacuum
От | Ross J. Reedstrom |
---|---|
Тема | Re: vacuum |
Дата | |
Msg-id | 20010619142518.D26463@rice.edu обсуждение исходный текст |
Ответ на | RE: vacuum (The Hermit Hacker <scrappy@hub.org>) |
Список | pgsql-hackers |
On Wed, Jun 13, 2001 at 06:24:10PM -0300, The Hermit Hacker wrote: > > 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? Even better: since he's loading a script anyway, the script could start a transaction, disable the trigger, load the data, bulk UPDATE the other table, and re-enable the trigger. I bet it takes only a few minutes to do the whole thing that way. Ross > > 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@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 > > > > ---------------------------(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@hub.org secondary: scrappy@{freebsd|postgresql}.org > > > ---------------------------(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
В списке pgsql-hackers по дате отправления: