Hi,
I'm running OpenNMS on a machine that was misconfigured and our
event database climbed to 2.8 million rows. I've gone in and archived
that vast majority out to files on disk and now the table has about
20-30k rows. When I do a "vacuum full", though, it says that the vast
majority of the rows are dead rows that are unremovable. I've read
through the archives on the list and everything I can find about dead
rows says that they can't be removed when they're locked by a
transaction. Just to be absolutely sure, I shut down all of the apps as
well as PostgreSQL on the server and restarted PostgreSQL in single-user
mode. When I run a "vacuum full verbose" on the events table, I still
show roughly 2.7 million unremovable dead rows:
INFO: "events": found 629702 removable, 2764070 nonremovable row
versions in 440726 pages
DETAIL: 2734213 dead row versions cannot be removed yet.
Nonremovable row versions range from 237 to 1160 bytes long.
There were 1797800 unused item pointers.
Total free space (including removable row versions) is
1808978584 bytes.
175919 pages are or will become empty, including 0 at the end
of the tab
le.
316400 pages containing 1792334416 free bytes are potential
move destina
tions.
CPU 97.17s/19.74u sec elapsed 269.83 sec.
Do I have a corrupted DB? Is there anything else that would prevent
those dead rows from being removed if I'm in single-user mode? Should I
just use pg_dump and wipe the DB then restore? This is PostgreSQL 8.1.5
on Solaris 9/Sparc. OpenNMS is non-functional at the moment because A
lot of the queries it uses end up triggering sequential scans on the
table or on the indices (which also have ~ 2.6 million entries) and they
timeout. Any help would be appreciated.
Thanks,
Derek
--
----------------------------------------------------------------------
Derek Chen-Becker
Senior Network Engineer, Security Architect
CPI Corp, Inc.
1706 Washington Ave
St. Louis, MO 63103
Phone: 314-231-7711 x6455
Fax: 314-613-6724
dbecker@xxxxxxxxxxx
PGP Key available from public key servers
Fingerprint: E4C4 26C0 8588 E80A C29F 636D 1FBE 0FE3 2871 4AE8
----------------------------------------------------------------------
