Re: Lock pileup causes server to stall

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.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services