Postgres Professional
   

Re: Lock pileup causes server to stall

From: Jesper Krogh
Subject: Re: Lock pileup causes server to stall
Date: ,
Msg-id: 9E96340C-89AD-4A7A-9C92-47A0C08DE1D8@krogh.cc
(view: Whole thread, Raw)
In response to: Re: Lock pileup causes server to stall  (Alvaro Herrera)
Responses: Re: Lock pileup causes server to stall  (Alvaro Herrera)
List: pgsql-performance

Tree view

Lock pileup causes server to stall  (Josh Berkus, )
 Re: Lock pileup causes server to stall  (Alvaro Herrera, )
  Re: Lock pileup causes server to stall  (Jesper Krogh, )
   Re: Lock pileup causes server to stall  (Alvaro Herrera, )
    Re: Lock pileup causes server to stall  (Jesper Krogh, )
 Re: Lock pileup causes server to stall  (Josh Berkus, )
  Re: Lock pileup causes server to stall  (Jeff Janes, )
 Re: Lock pileup causes server to stall  (Josh Berkus, ) 
> On 10/11/2014, at 22.40, Alvaro Herrera <> wrote:
>
> Josh Berkus wrote:
>> All,
>>
>> pg version: 9.3.5
>> RHEL 6.5
>> 128GB/32 cores
>> Configured with shared_buffers=16GB
>> Java/Tomcat/JDBC application
>>
>> Server has an issue that whenever we get lock waits (transaction lock
>> waits, usually on an FK dependancy) lasting over a minute or more than
>> 10 at once, *all* queries on the server slow to a crawl, taking 100X to
>> 400X normal execution times.
>
> Current FK checking makes you wait if the referenced tuple is modified
> on any indexed column, not just those that are actually used in
> foreign keys.  Maybe this case would be sped up if we optimized that.

Even if it is an gin index that is being modified?   seems like a harsh limitation to me.

Jesper

pgsql-performance by date:

Previous:
From: Jesper Krogh
Date:
Subject: Re: Lock pileup causes server to stall
Next:
From: Alvaro Herrera
Date:
Subject: Re: Lock pileup causes server to stall