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/
Show quoted text
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:
Show quoted text
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