Date: Mon, 10 Nov 2014 18:40:01 -0300
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
Message-ID: <20141110214000.GL1791@alvin.alvh.no-ip.org>
References: <54611702.50308@agliodbs.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <54611702.50308@agliodbs.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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