Search Postgresql Archives

Totally unremovable dead rows?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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
----------------------------------------------------------------------


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux