Errors while vacuuming large tables
Errors while vacuuming large tables
От:
Jeff Boes <jboes@nexcerpt.com>
Дата:
We expire rows by a datestamp from a few fairly large tables in our
schema (running 7.2.1).
Table A: 140 Krows, 600 MB
Table B: 100 Krows, 2.7 GB
Table C: 140 Krows, 2.7 GB
Table D: 3.2 Mrows, 500 MB
so that something like 15-20% of each table is deleted at a crack (done on
a weekend, of course). After the deletions, a VACUUM FULL is performed
on each of these tables. Recently, we get this message quite often on
table A:
ERROR: Parent tuple was not found
which I'm led to believe by things I've read here and elsewhere is caused
by a bug in PostgreSQL having to do with rows marked as read-locked or
something. I hope this gets repaired soon, because it's annoying not to
be able to recover the space automatically on this table.
But this weekend, we got a different set of errors:
ERROR: cannot open segment 1 of relation table_D (target
block 2337538109): No such file or directory
and for table B:
NOTICE: Child itemid in update-chain marked as unused - can't continue
repair_frag
ERROR: cannot open segment 3 of relation pg_toast_51207070 (target
block 2336096317): No such file or directory
What's the remedy to keep this from happening? We have an Apache
mod_perl installation running queries against these tables; could an open
read-only transaction cause problems like this?
--
Jeff Boes vox 616.226.9550 ext 24
Database Engineer fax 616.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
Re: Errors while vacuuming large tables
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Jeff Boes writes: > We expire rows by a datestamp from a few fairly large tables in our > schema (running 7.2.1). > Recently, we get this message quite often on > table A: > ERROR: Parent tuple was not found Please update to 7.2.3 and see if things get better. Also, if you're running on a recent Red Hat system, you might try Red Hat's 7.2.3-based RHDB release (7.2.3-RH), which is not out yet but should be out within days. It has a few additional VACUUM fixes back-patched from 7.3 work, which might solve your problem if the standard 7.2.3 release doesn't. (Note that I cannot recommend going to 7.2.2-RH in place of 7.2.3, due to the severity of the bugs that caused us to release 7.2.3; those are not fixed in 7.2.2-RH, as they were not found until after that release was frozen.) Keep an eye on http://www.redhat.com/software/database/ for the 7.2.3 update. regards, tom lane
Re: Errors while vacuuming large tables
От:
Jeff Boes <jboes@nexcerpt.com>
Дата:
On Mon, 14 Oct 2002 11:13:46 -0400, Jeff Boes wrote:
> ERROR: cannot open segment 1 of relation table_D (target block
> 2337538109): No such file or directory
>
> and for table B:
>
> NOTICE: Child itemid in update-chain marked as unused - can't continue
> repair_frag
> ERROR: cannot open segment 3 of relation pg_toast_51207070 (target
> block 2336096317): No such file or directory
>
Well, we attempted a VACUUM FULL on these tables again, but got the
dreaded:
NOTICE: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am
going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
We also had two load spikes several hours after this occurred, where the
system was unusable for 5 minutes (this has also happened before). We
suspect there was some corruption in the indexes. I did a REINDEX on
each affected table followed by a VACUUM FULL, and this time the VACUUMs
finished without error. All this may be superstition on my part, but I
report it here in case anyone recognizes the situation, or is as
desperate as I was to correct the problem ...
We are definitely moving to 7.2.3 ASAP, but it will be a week or two
before we can schedule this.
--
Jeff Boes vox 616.226.9550 ext 24
Database Engineer fax 616.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise