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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Litao Wu <litaowu(at)yahoo(dot)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/