X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7BEE0D1B276 for ; Tue, 8 Jun 2004 14:25:48 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 81432-08 for ; Tue, 8 Jun 2004 17:25:45 +0000 (GMT) Received: from web13123.mail.yahoo.com (web13123.mail.yahoo.com [216.136.174.141]) by svr1.postgresql.org (Postfix) with SMTP id C477ED1B233 for ; Tue, 8 Jun 2004 14:25:42 -0300 (ADT) Message-ID: <20040608172542.90818.qmail@web13123.mail.yahoo.com> Received: from [63.78.248.48] by web13123.mail.yahoo.com via HTTP; Tue, 08 Jun 2004 10:25:42 PDT Date: Tue, 8 Jun 2004 10:25:42 -0700 (PDT) From: Litao Wu Subject: Re: reindex and copy - deadlock? To: Tom Lane Cc: pgsql-performance@postgresql.org In-Reply-To: <2960.1086713228@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/94 X-Sequence-Number: 7152 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 wrote: > Litao Wu 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/