pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Litao Wu <litaowu@yahoo.com>
To: pgsql-performance@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: reindex and copy - deadlock?
Date: Tue, 8 Jun 2004 08:31:06 -0700 (PDT)
Message-ID: <20040608153106.61670.qmail@web13123.mail.yahoo.com> (raw)
In-Reply-To: <40C5CED2.3020002@lamundial.hn>

Hi,

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. But the following query shows
only reindex process (23127)is granted lock while 
COPY process (3149) is not.

Last time when we have this problem and kill 
reindex process and COPY process does not work.
We had to bounce the database server.

As you know, when reindex is running, nobody can
access the table.
Can someone kindly help?

Thanks,



Here is lock info from database:

        replace        | database | transaction |  pid
 |        mode         | granted
-----------------------+----------+-------------+-------+---------------------+---------
 email                 |    17613 |             | 
3149 | RowExclusiveLock    | f
 email_cre_dom_idx     |    17613 |             |
23127 | ExclusiveLock       | t
 email_cid_cre_idx     |    17613 |             |
23127 | ShareLock           | t
 email_cid_cre_idx     |    17613 |             |
23127 | AccessExclusiveLock | t
 email                 |    17613 |             |
23127 | ShareLock           | t
 email                 |    17613 |             |
23127 | AccessExclusiveLock | t
 email_cid_cre_dom_idx |    17613 |             |
23127 | ShareLock           | t
 email_cid_cre_dom_idx |    17613 |             |
23127 | AccessExclusiveLock | t
 email_did_cre_idx     |    17613 |             |
23127 | ShareLock           | t
 email_did_cre_idx     |    17613 |             |
23127 | AccessExclusiveLock | t
 email_cre_dom_idx     |    17613 |             |
23127 | AccessExclusiveLock | t
(11 rows)


Here are the processes of 3149 and 23127 from OS:

postgres  3149  1.3  6.4 154104 134444 ?     S   
Jun03  92:04 postgres: postgres db1 xx.xx.xx.xx COPY
waiting

postgres 23127  3.2  9.3 228224 194512 ?     S   
03:35  15:03 postgres: postgres db1 [local] REINDEX

Here are queries from database:
23127 | REINDEX table email

 3149 | COPY email (...) FROM stdin





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



view thread (16+ messages)  latest in thread

Message-ID: <20040608153106.61670.qmail@web13123.mail.yahoo.com>
Permalink:  ../20040608153106.61670.qmail@web13123.mail.yahoo.com/
Also on:    postgresql.org/message-id/20040608153106.61670.qmail@web13123.mail.yahoo.com

 · 

reply

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: litaowu@yahoo.com
  Subject: Re: reindex and copy - deadlock?
  In-Reply-To: <20040608153106.61670.qmail@web13123.mail.yahoo.com>

* 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