Thread: Vacuum
Hi all,
I'm running PostgreSQL 7.1 with Slackware 8 on a dual processor Dell server.
I have a database with 17 tables. I started VacuumDB with the analyze
option on Friday around 3pm...when I came in this morning at 8 it was still
not done. From the verbose info, it seems to be "stuck" on one table,
whowrote which is the second largest table (2546 records).
I'm not sure what is causing it to seem to pause here, since the database
hasn't been put into production or anything, just the design. The table is
nearly identical to another one, called whichcats, that ran through no fine.
I put their table create statements below in case it would help, but my
question is what is taking vacuumdb so long and how can I fix it?
-- Table: whowrote (table its stalling on)
CREATE TABLE "whowrote" (
"connectionid" int4 DEFAULT
nextval('"whowrote_tmp_connectionid_seq"'::text) NOT NULL,
"pubid" int4,
"authorid" int4,
CONSTRAINT "whowrote_tmp_pkey" PRIMARY KEY ("connectionid"),
CONSTRAINT "whowrotefk" FOREIGN KEY (authorid) REFERENCES "authors"
(authorid),
CONSTRAINT "whowrotepubfk" FOREIGN KEY (pubid) REFERENCES "publications"
(pubid)
);
-- Table: whichcats (similar table)
CREATE TABLE "whichcats" (
"connectionid" int4 DEFAULT nextval('"whichcats_connectionid_seq"'::text)
NOT NULL,
"catid" int4,
"pubid" int4,
CONSTRAINT "whichcats_pkey" PRIMARY KEY ("connectionid"),
CONSTRAINT "whichcatfk" FOREIGN KEY (catid) REFERENCES "cats" (catid),
CONSTRAINT "whichcatpubfk" FOREIGN KEY (pubid) REFERENCES "publications"
(pubid)
);
Summer S. Wilson ICQ 26835530
Programmer/Analyst I, EIT TCE
Webmaster, An Eclectic World http://eclectic-world.com
"Summer S. Wilson" <collectonian@eclectic-world.com> writes:
> I'm running PostgreSQL 7.1 with Slackware 8 on a dual processor Dell server.
> I have a database with 17 tables. I started VacuumDB with the analyze
> option on Friday around 3pm...when I came in this morning at 8 it was still
> not done. From the verbose info, it seems to be "stuck" on one table,
> whowrote which is the second largest table (2546 records).
Is it consuming CPU time, or just sitting? (ps or top would tell you)
If it's just sitting, I'd bet that there is some other connected backend
that's holding an open transaction with a lock on that table.
regards, tom lane
Tom, Is there any way to check whether there is open transaction with a lock on stucking table while vacuuming and terminate vacuum? Tom Lane wrote: > "Summer S. Wilson" <collectonian@eclectic-world.com> writes: > >>I'm running PostgreSQL 7.1 with Slackware 8 on a dual processor Dell server. >>I have a database with 17 tables. I started VacuumDB with the analyze >>option on Friday around 3pm...when I came in this morning at 8 it was still >>not done. From the verbose info, it seems to be "stuck" on one table, >>whowrote which is the second largest table (2546 records). >> > > Is it consuming CPU time, or just sitting? (ps or top would tell you) > If it's just sitting, I'd bet that there is some other connected backend > that's holding an open transaction with a lock on that table. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > . > >
Hi Tom, It does not appear to be consuming any CPU time, its just sitting there. I'm not sure what would be connected to it because we have nothing live right now, and none of the pages related to that table have been designed yet. Is there a safe way to end the vacuum? > "Summer S. Wilson" <collectonian@eclectic-world.com> writes: > >>I'm running PostgreSQL 7.1 with Slackware 8 on a dual processor Dell server. >>I have a database with 17 tables. I started VacuumDB with the analyze >>option on Friday around 3pm...when I came in this morning at 8 it was still >>not done. From the verbose info, it seems to be "stuck" on one table, >>whowrote which is the second largest table (2546 records). >> > > Is it consuming CPU time, or just sitting? (ps or top would tell you) > If it's just sitting, I'd bet that there is some other connected backend > that's holding an open transaction with a lock on that table. Summer S. Wilson ICQ 26835530 Programmer/Analyst I, EIT TCE Webmaster, An Eclectic World http://eclectic-world.com
Hi all,
I haven't been following this thread that carefully but when yesterday we
also had vacuuming problems I decided I should. Reading this message shows
that Summer's problem seems to be different than ours although they could be
connected.
Yesterday our application stopped responding. After a little investigation
we found that 1 process on the db box was using 100% CPU. Via the ps command
we could see that there were 2 processes running continuously, a 'vacuum'
and a 'query'. Unfortunately I failed to note which was using 100% CPU.
At the time of discovery the run-a-way process had used almost 1 hour CPU
time. Since the app wasn't responding I'm assuming one or more other
processes were blocked. I can only guess that the vacuum process was the
long one because under typical usage no queries in the application run for
more than a few seconds.
I have since restarted the db with debug output turned on in order to track
down the queries. Any other ideas or suggestions? BTW, we're running with PG
7.1.3.
Thanks,
--Rainer
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Summer S. Wilson
> Sent: Tuesday, October 09, 2001 10:20 PM
> To: pgsql-admin@postgresql.org
> Cc: tgl@sss.pgh.pa.us
> Subject: Re: [ADMIN] Vacuum
>
>
> Hi Tom,
>
> It does not appear to be consuming any CPU time, its just sitting there.
> I'm not sure what would be connected to it because we have nothing live
> right now, and none of the pages related to that table have been designed
> yet. Is there a safe way to end the vacuum?
>
>
> Summer S. Wilson ICQ 26835530
> Programmer/Analyst I, EIT TCE
> Webmaster, An Eclectic World http://eclectic-world.com