Re: Lock pileup causes server to stall

On 11/10/2014 01:40 PM, 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.
>
>> * This applies even to queries which are against other databases, so
>> it's not purely a lock blocking issue.
>
> Oh.

Yeah, I think this is more likely a problem with the general lock table
and shared_buffers than anything to do with actual lock-blocks.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com