From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Jim C. Nasby <jnasby@pervasive.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Jeff Kirby <Jeff.Kirby@wicourts.gov>
To: pgsql-admin@postgresql.org
To: pgsql-hackers@postgresql.org
Subject: Re: Vacuum Full Analyze Stalled
Date: Mon, 03 Oct 2005 21:54:51 -0400
Message-ID: <983.1128390891@sss.pgh.pa.us> (raw)
In-Reply-To: <14993.1128372520@sss.pgh.pa.us>
References: <s3413ba7.062@gwmta.wicourts.gov>
<20051003194114.GD40138@pervasive.com>
<20051003201225.GA11527@surnet.cl>
<14993.1128372520@sss.pgh.pa.us>
[ I just noticed that this thread is happening on pgsql-admin, which is
completely inappropriate for discussing bugs in a beta version.
Please redirect followups to pgsql-hackers. ]
I wrote:
> ... The hypothesis I'm thinking about is that VACUUM is trying to do
> LockBufferForCleanup() and for some reason it never finishes.
I set up a simple-minded reproduction of Kevin's situation: I did
create domain dint as int check (value > 0);
create table manyd (f1 dint, f2 dint, f3 dint,
f4 dint, f5 dint, f6 dint, f7 dint, f8 dint, f9 dint, f10 dint);
and then ran ten concurrent clients doing this continuously:
insert into manyd values(1,2,3,4,5,6,7,8,9,10);
which should be enough to force a lot of indexscans on
pg_constraint_contypid_index. I added an additional client doing
create domain d1 as int check (value > 0);
drop domain d1;
to ensure that there were dead rows needing vacuuming in pg_constraint.
(BTW, Tatsuo's new version of pg_bench lets me do all this without
writing a line of code...)
Finally, I added some debug printouts to LockBufferForCleanup so I
could see if it was being executed or not.
Then I tried both manual and autovacuum-driven vacuums of pg_constraint.
I was able to see from the debug printouts that LockBufferForCleanup was
sometimes forced to wait in both cases. But it never got "stuck".
This eliminates one thing I was worrying about, which was the
possibility that the LockBufferForCleanup waiting path was completely
broken inside autovacuum for some reason. But it doesn't get us a whole
lot closer to a solution.
At this point I think we need more info from Kevin and Jeff before we
can go further. There must be some additional special feature of their
application that makes the problem appear, but what?
A stack trace of the stuck process would definitely help...
regards, tom lane
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, alvherre@alvh.no-ip.org, jnasby@pervasive.com, Kevin.Grittner@wicourts.gov, Jeff.Kirby@wicourts.gov, pgsql-hackers@postgresql.org
Subject: Re: Vacuum Full Analyze Stalled
In-Reply-To: <983.1128390891@sss.pgh.pa.us>
References: <s3413ba7.062@gwmta.wicourts.gov> <20051003194114.GD40138@pervasive.com> <20051003201225.GA11527@surnet.cl> <14993.1128372520@sss.pgh.pa.us> <983.1128390891@sss.pgh.pa.us>
* 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