From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Lock pileup causes server to stall
Date: Mon, 10 Nov 2014 18:40:01 -0300
Message-ID: <20141110214000.GL1791@alvin.alvh.no-ip.org> (raw)
In-Reply-To: <54611702.50308@agliodbs.com>
References: <54611702.50308@agliodbs.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alvherre@2ndquadrant.com, josh@agliodbs.com
Subject: Re: Lock pileup causes server to stall
In-Reply-To: <20141110214000.GL1791@alvin.alvh.no-ip.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox