Re: Lock pileup causes server to stall

On Tue, Nov 11, 2014 at 9:11 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> 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.
>
>
Any chance you can run 'perf record -a' on it?

Cheers,

Jeff