Table locking problems?

From: Dan Harris
Subject: Table locking problems?
Date: ,
Msg-id: 52CFA236-FC43-4018-B399-1854240110FF@drivefaster.net
(view: Whole thread, Raw)
Responses: Re: Table locking problems?  ("Joshua D. Drake")
Re: Table locking problems?  (Michael Fuhr)
Re: Table locking problems?  (Steve Poe)
List: pgsql-performance

Tree view

Table locking problems?  (Dan Harris, )
 Re: Table locking problems?  ("Joshua D. Drake", )
  Re: Table locking problems?  (Tom Lane, )
   Re: Table locking problems?  (Dan Harris, )
 Re: Table locking problems?  (Michael Fuhr, )
 Re: Table locking problems?  (Steve Poe, )
  Re: Table locking problems?  (Dan Harris, )
   Re: Table locking problems?  (John A Meinel, )
    Re: Table locking problems?  (Dan Harris, )
     Re: Table locking problems?  (John A Meinel, )

I thought I would send this to pg-performance since so many people
helped me with my speed issues recently.  I was definitely IO-
bottlenecked.

Since then, I have installed 2 RAID arrays with 7 15k drives in them
in RAID 0+1 as well as add a new controller card with 512MB of cache
on it.  I also created this new partition on the RAID as XFS instead
of ext3.

These changes have definitely improved performance, but I am now
finding some trouble with UPDATE or DELETE queries "hanging" and
never releasing their locks.  As this happens, other statements queue
up behind it.  It seems to occur at times of very high loads on the
box.  Is my only option to kill the query ( which usually takes down
the whole postmaster with it! ouch ).

Could these locking issues be related to the other changes I made?
I'm really scared that this is related to choosing XFS, but I sure
hope not.   How should I go about troubleshooting the "problem"
queries?  They don't seem to be specific to a single table or single
database.

I'm running 8.0.1 on kernel 2.6.12-3 on 64-bit Opterons if that
matters..


-Dan


pgsql-performance by date:

From: John A Meinel
Date:
Subject: Re: Table locking problems?
From: Tobias Brox
Date:
Subject: partial index regarded more expensive