Thread: vacuum never finishes
Correction vaccuum not vaccuum analyze. We are having trouble with vacuum analyze. It never finishes after 4.5 hours. It hangs up on an index of a table with almost 3 million rows. heres what the ouput was before we terminated it. NOTICE: Pages 143181: Changed 808, reaped 86454, Empty 0, New 0; Tup 2925417: Vac 2286428, Keep/VTL 0/0, Crash 0, UnUsed 172389, MinLen 148, MaxLen 412; Re-using: Free/Avail. Space 508493104/50844288 0; EndEmpty/Avail. Pages 0/85613. CPU 11.16s/2.45u sec. NOTICE: Index tblcoldpool_2_pkey: Pages 15109; Tuples 2925417: Deleted 899775. CPU 2.57s/19.82u sec . NOTICE: Index bulk_tblcoldpool_index: Pages 20219; Tuples 2925417: Deleted 899775. CPU 2.95s/19.05u sec. NOTICE: Index since_tblcoldpool_index: Pages 20146; Tuples 2925417: Deleted 899775. CPU 2.72s/19.38 u sec. NOTICE: Index spons_tblcoldpool_index: Pages 19963; Tuples 2925417: Deleted 899775. CPU 2.39s/19.90 u sec. NOTICE: Index email_tblcoldpool_2_index: Pages 32494; Tuples 2925417: Deleted 899775. CPU 5.85s/29. 49u sec. NOTICE: Index random: Pages 16731; Tuples 2925417: Deleted 899775. CPU 2.17s/20.31u sec. NOTICE: Index emailsponser_tblcoldpool_index: Pages 31678; Tuples 2925417: Deleted 899774. CPU 5.42 s/29.62u sec. Any hints would be greatly appreciated. __________________________________________________ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com
Hi, I'm using v7.1.3 and somehow when some tables get very bloated (many inserts over weeks) I seem to need to vacuum repeatedly before the table really gets cleaned up and postmaster starts using less CPU. Why is this so? Regards, Link.
On Mon, Feb 25, 2002 at 03:28:20PM +0800, Lincoln Yeoh wrote: > Hi, > > I'm using v7.1.3 and somehow when some tables get very bloated (many > inserts over weeks) I seem to need to vacuum repeatedly before the table > really gets cleaned up and postmaster starts using less CPU. It is generally recommended to run VACUUM ANALYZE every day. The reason is that new rows are put on the end of the table and yet need VACUUM to clean out the cruft. This is better explained in the docs. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > If the company that invents a cure for AIDS is expected to make their > money back in 17 years, why can't we ask the same of the company that > markets big-titted lip-syncing chicks and goddamn cartoon mice?
I know that. What I don't understand is why each time I decide to vacuum, I need more than one vacuum to clean out lots of accumulated cruft. Why do I need to issue two or three or even more vacuums before the tables reach their fully cleaned sizes and states? Regards, Link. At 06:34 PM 25-02-2002 +1100, Martijn van Oosterhout wrote: >On Mon, Feb 25, 2002 at 03:28:20PM +0800, Lincoln Yeoh wrote: > > Hi, > > > > I'm using v7.1.3 and somehow when some tables get very bloated (many > > inserts over weeks) I seem to need to vacuum repeatedly before the table > > really gets cleaned up and postmaster starts using less CPU. > >It is generally recommended to run VACUUM ANALYZE every day. The reason is >that new rows are put on the end of the table and yet need VACUUM to clean >out the cruft.
Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> Why do I need to issue two or three or even more vacuums before the tables
> reach their fully cleaned sizes and states?
I seem to recall having noticed that there are corner cases where
VACUUM FULL doesn't achieve all the packing you might expect, and another
VACUUM FULL will accomplish more. Don't recall the details, but you
might find something if you grovel through the code or the pghackers
archives.
regards, tom lane