7.3.3 drop table takes very long time
I'm trying to drop a table and it's taking a very long time. There has been
a lot of modification to the table and it has a lot of old data still being
used as a result of not using the vacuum function enough. I ran an insert
into and it was taking a long time (ran for about 48 hours) so I aborted it.
I tried vacuuming it and that ran for about the same amount of time before I
aborted. I figured the fastest ting would be to drop the table and re-create
it.
I tried running a pg_dump and it ran for about 4 days without putting any of
the data to the output file.
I started the drop table command yesterday and it's been running for almost
24 hours.
I know it's processing because it's using up the CPU and I can run
transactions on all of the other tables except this one. It has about
132,000 records in the table.
Any ideas on how I can speed this up?
Can I go into the /data directory and find the file that contains that table
and delete that? If so, how would I go about doing this?
Eric
_________________________________________________________________
Expand your wine savvy � and get some great new recipes � at MSN Wine.
http://wine.msn.com
"Eric Freeman" <ejf7@hotmail.com> writes:
I started the drop table command yesterday and it's been running for almost
24 hours.
You're stuck in some kind of infinite loop --- there's no way that DROP
should take any noticeable amount of time. I'm guessing that the system
catalog entries for this particular table are corrupted somehow, but no
idea just how. It would be worth trying to track it down in case there
is a PG bug lurking.
Can you attach to the looping backend with a debugger and get a stack
trace for us?
regards, tom lane
Tom Lane wrote:
"Eric Freeman" <ejf7@hotmail.com> writes:
I started the drop table command yesterday and it's been running for almost
24 hours.You're stuck in some kind of infinite loop --- there's no way that DROP
should take any noticeable amount of time. I'm guessing that the system
catalog entries for this particular table are corrupted somehow, but no
idea just how. It would be worth trying to track it down in case there
is a PG bug lurking.Can you attach to the looping backend with a debugger and get a stack
trace for us?
Is there any possibility that he's got an open transacation sitting out
there for days holding a lock on that table?
Mike Mascari
Mike Mascari <mascarm@mascari.com> writes:
Is there any possibility that he's got an open transacation sitting out
there for days holding a lock on that table?
Good thought ... but if that was the issue then the DROP would just be
sleeping waiting for the lock, and Eric did say it was consuming CPU ...
regards, tom lane
On Thursday 08 January 2004 9:14 am, Mike Mascari wrote:
Tom Lane wrote:
"Eric Freeman" <ejf7@hotmail.com> writes:
I started the drop table command yesterday and it's been running
for almost 24 hours.You're stuck in some kind of infinite loop --- there's no way that
DROP should take any noticeable amount of time. I'm guessing
that the system catalog entries for this particular table are
corrupted somehow, but no idea just how. It would be worth
trying to track it down in case there is a PG bug lurking.Can you attach to the looping backend with a debugger and get a
stack trace for us?Is there any possibility that he's got an open transacation sitting
out there for days holding a lock on that table?Mike Mascari
Yesterday I had someone drop a table while a pg_dumpall was running.
The drop didn't complete till the dump was done.
Cheers,
Steve
Steve Crawford <scrawford@pinpointresearch.com> writes:
On Thursday 08 January 2004 9:14 am, Mike Mascari wrote:
Is there any possibility that he's got an open transacation sitting
out there for days holding a lock on that table?
Yesterday I had someone drop a table while a pg_dumpall was running.
The drop didn't complete till the dump was done.
Yup, because pg_dump takes an AccessShareLock (reader's lock) on every
table it intends to dump. But the process wanting to drop the table
would have been blocked on the lock, and would not have been chewing any
CPU time while it waited. Eric seems to be seeing something different.
regards, tom lane
I restarted Postgres and it dropped in a second or 2. I can't believe I
didn't think of trying that before.
Thanks for all the help.
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steve Crawford <scrawford@pinpointresearch.com>
CC: Mike Mascari <mascarm@mascari.com>, Eric Freeman <ejf7@hotmail.com>,
pgsql-general@postgresql.org
Subject: Re: [GENERAL] 7.3.3 drop table takes very long time Date: Thu, 08
Jan 2004 16:26:32 -0500
Received: from mc12-f8.hotmail.com ([65.54.167.144]) by mc12-s4.hotmail.com
with Microsoft SMTPSVC(5.0.2195.6824); Thu, 8 Jan 2004 13:31:21 -0800
Received: from hosting.commandprompt.com ([207.173.200.216]) by
mc12-f8.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Thu, 8 Jan 2004
13:30:36 -0800
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])by
hosting.commandprompt.com (8.11.6/8.11.6) with ESMTP id i08LRZk29714;Thu, 8
Jan 2004 13:28:14 -0800
Received: from localhost (neptune.hub.org [200.46.204.2])by
svr1.postgresql.org (Postfix) with ESMTP id 20237D1B4AFfor
<pgsql-general-postgresql.org@localhost.postgresql.org>; Thu, 8 Jan 2004
21:27:25 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71]) by localhost
(neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id
70015-01 for <pgsql-general-postgresql.org@localhost.postgresql.org>; Thu,
8 Jan 2004 17:26:36 -0400 (AST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])by
svr1.postgresql.org (Postfix) with ESMTP id A0509D1B48Afor
<pgsql-general@postgresql.org>; Thu, 8 Jan 2004 17:26:34 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])by
sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id i08LQW19016036;Thu, 8 Jan
2004 16:26:33 -0500 (EST)
X-Message-Info: 820stLNiepTzJGdgouOWLmzTpS/lU6jjUO41cNhiU8g=
X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org
In-reply-to: <200401081306.23269.scrawford@pinpointresearch.com>
References: <BAY99-F32kpDasYutu4000304b7@hotmail.com>
<11871.1073581548@sss.pgh.pa.us> <3FFD8FED.3000900@mascari.com>
<200401081306.23269.scrawford@pinpointresearch.com>
Comments: In-reply-to Steve Crawford
<scrawford@pinpointresearch.com>message dated "Thu, 08 Jan 2004 13:06:23
-0800"
Message-ID: <16035.1073597192@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Mailing-List: pgsql-general
Precedence: bulk
Return-Path: pgsql-general-owner+M55362@postgresql.org
X-OriginalArrivalTime: 08 Jan 2004 21:30:36.0245 (UTC)
FILETIME=[A76F8850:01C3D62E]Steve Crawford <scrawford@pinpointresearch.com> writes:
On Thursday 08 January 2004 9:14 am, Mike Mascari wrote:
Is there any possibility that he's got an open transacation sitting
out there for days holding a lock on that table?Yesterday I had someone drop a table while a pg_dumpall was running.
The drop didn't complete till the dump was done.Yup, because pg_dump takes an AccessShareLock (reader's lock) on every
table it intends to dump. But the process wanting to drop the table
would have been blocked on the lock, and would not have been chewing any
CPU time while it waited. Eric seems to be seeing something different.regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
_________________________________________________________________
Check your PC for viruses with the FREE McAfee online computer scan.
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
Import Notes
Resolved by subject fallback