pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Litao Wu <litaowu@yahoo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: reindex and copy - deadlock?
Date: Wed, 30 Jun 2004 11:45:18 -0700 (PDT)
Message-ID: <20040630184518.89025.qmail@web13122.mail.yahoo.com> (raw)
In-Reply-To: <10244.1088615217@sss.pgh.pa.us>

Thanks!

OK, we will do this exceise next time.

TSince there are multiple databases and
there are 170 postgres processes this morning,
60 of them are access the problem database,
and 57 of 60 are non-idle. 

We only need to gdb those 57 processes, or
we need gdb 60 or 170?

Thanks again!

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Litao Wu <litaowu@yahoo.com> writes:
> > Our PG version is 7.3.2.
> 
> Hmm.  On general principles you should be using
> 7.3.6, but I do not see
> anything in the 7.3.* change logs that looks very
> likely to cure this.
> 
> > The copy process is always there. Besides copy
> > process, there are many select processes wait also
> > (it is understandable only when reindex,
> > but how come selects wait when drop/create index?
> 
> DROP INDEX would lock out selects (it has no other
> way to be sure no
> select is trying to *use* the index).  Once you're
> past that, selects
> would work, but if you try something like
> 	begin; drop index; create index; commit;
> then the drop's lock will be held till commit.
> 
> I'm not sure about whether COPY is related.  In your
> original post, the
> COPY was waiting to acquire RowExclusiveLock on the
> table, so it hadn't
> actually done anything yet and really couldn't be
> holding a buffer lock
> AFAICS.
> 
> > But one thing is sure:
> > reindex or create index is granted lock while
> > others wait. If reindex/create index is not 
> > the perpetrator, how can PG grants it lock
> > but not others, like COPY?
> 
> The point is that it's waiting for a lower-level
> lock (namely a buffer
> LWLock).  There's no deadlock detection for LWLocks,
> because they're not
> supposed to be used in ways that could cause a
> deadlock.
> 
> Assuming for the moment that indeed this is a
> deadlock, you could learn
> something the next time it happens with some manual
> investigation.
> You'll need to keep using the debug-enabled build. 
> When you next get a
> lockup, proceed as follows:
> 
> 1. Attach to the REINDEX or CREATE INDEX process and
> find out which
> LWLock number it is blocked on.  (This is the lockid
> argument of
> LWLockAcquire, 21335 in your trace of today.)
> 
> 2. For *each* live backend process (including the
> REINDEX itself),
> attach with gdb and look at the held-locks status of
> lwlock.c.
> This would go something like
> 
> 	gdb> p num_held_lwlocks
> if greater than zero:
> 	gdb> x/10d held_lwlocks
> (replace "10" by the value of num_held_lwlocks)
> 
> If you find a backend that is holding the lock
> number that REINDEX
> wants, print out its call stack with "bt", and look
> in pg_locks to see
> what lockmanager locks it is holding or waiting for.
>  If you do not find
> one, then the deadlock theory is disproved, and
> we're back to square
> one.
> 
> 			regards, tom lane
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 



		
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail 



view thread (16+ messages)  latest in thread

Message-ID: <20040630184518.89025.qmail@web13122.mail.yahoo.com>
Permalink:  ../20040630184518.89025.qmail@web13122.mail.yahoo.com/
Also on:    postgresql.org/message-id/20040630184518.89025.qmail@web13122.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, tgl@sss.pgh.pa.us
  Subject: Re: reindex and copy - deadlock?
  In-Reply-To: <20040630184518.89025.qmail@web13122.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