Totally unremovable dead rows?
От | Derek Chen-Becker |
---|---|
Тема | Totally unremovable dead rows? |
Дата | |
Msg-id | 4829F1E7.6040307@cpicorp.com обсуждение исходный текст |
Ответы |
Re: Totally unremovable dead rows?
|
Список | pgsql-general |
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@cpicorp.com PGP Key available from public key servers Fingerprint: E4C4 26C0 8588 E80A C29F 636D 1FBE 0FE3 2871 4AE8 ----------------------------------------------------------------------
В списке pgsql-general по дате отправления: