Re: reindex and copy - deadlock?

Thank you, Tom!

We vacuum and reindex every night and
reindex typically took 30 minutes. Today,
it ran since 3AM, and has not finished till 8:30AM.

The email and its indexe sizes are:
tablename indexname             size_kb reltuples
email                           1292696 8.07905e+06
email     email_cre_dom_idx     323112
email     email_cid_cre_dom_idx 357952
email     email_did_cre_idx     205712
email     email_cid_cre_idx     205560

I agree with you that deadlock is unlikely from
database and OS report.

We have bounced the server since it is a production
database and nobody can access email table because
of this.

I will use gdb next time. What's this right way to
get info as postgres owner?
gdb
attach pid

Thanks again!


--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Litao Wu <litaowu@yahoo.com> writes:
> > We often experience with the problem that reindex
> > cannot be finished in our production database.
> > It's typically done with 30 minutes. However,
> > sometimes, when there is another "COPY" process,
> > reindex will not finish. By monitoring the CPU
> > time reindex takes, it does not increase at all.
> > That seems a deadlock.
>
> There is no deadlock visible in your report: the
> reindex process is not
> waiting for a lock, according to either ps or
> pg_locks.  You sure it's
> not just slow?  I'd expect reindex to be largely I/O
> bound, so the lack
> of CPU activity doesn't prove much.
>
> If you think it's actually stuck waiting for
> something, try attaching to
> the REINDEX backend process with gdb to get a stack
> trace.  That would
> at least give some idea what it's waiting for.
>
>             regards, tom lane





__________________________________
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/