From: John Lister <john.lister-ps@kickstone.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
Subject: Re: database corruption help
Date: Thu, 12 Feb 2009 21:11:07 +0000
Message-ID: <4994906B.6090408@kickstone.com> (raw)
In-Reply-To: <4991338B.704@kickstone.com>
References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
<17205.1234200693@sss.pgh.pa.us>
<AB15E89CD7FB4EBD9D76CC5F8D8B3D4E@squarepi.com>
<4990840C.4090900@kickstone.com>
<20151.1234208044@sss.pgh.pa.us>
<49908D72.7040904@kickstone.com>
<25753.1234224090@sss.pgh.pa.us>
<4991338B.704@kickstone.com>
I seem to have more dead rows now..
doing a vacuum full on pg_class gives me
INFO: vacuuming "pg_catalog.pg_class"INFO: "pg_class": found 37
removable, 1845 nonremovable row versions in 18905 pages
DETAIL: 27 dead row versions cannot be removed yet.
Nonremovable row versions range from 160 to 229 bytes long.
There were 933834 unused item pointers.
Total free space (including removable row versions) is 150368692 bytes.
18839 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.01s/0.05u sec elapsed 0.17 sec.INFO: index "pg_class_oid_index"
now contains 1813 row versions in 7 pages
DETAIL: 56 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO: index
"pg_class_relname_nsp_index" now contains 1818 row versions in 24 pages
DETAIL: 63 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.WARNING: index
"pg_class_relname_nsp_index" contains 1818 row versions, but table
contains 1813 row versions
HINT: Rebuild the index with REINDEX.INFO: analyzing
"pg_catalog.pg_class"INFO: "pg_class": scanned 3000 of 18905 pages,
containing 227 live rows and 6 dead rows; 227 rows in sample, 1430
estimated total rows
Total query runtime: 4469 ms.
I've restarted the server which should have got rid of any
outstanding/long running transactions - which it though was the major
cause of lingering dead rows.
Any ideas how to fix this as i'd like to reduce this from 150+Mb which
can't be good for performance....
Thanks
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: john.lister-ps@kickstone.com, tgl@sss.pgh.pa.us
Subject: Re: database corruption help
In-Reply-To: <4994906B.6090408@kickstone.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox