Message-ID: <4CE46856.7070006@agliodbs.com>
Date: Wed, 17 Nov 2010 15:42:14 -0800
From: Josh Berkus <josh@agliodbs.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US;
	rv:1.9.1b3pre) Gecko/20090223 Thunderbird/3.0b2
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Anyone seen this kind of lock pileup?
References: <4CE412F4.4090101@agliodbs.com> <24934.1290031132@sss.pgh.pa.us>
In-Reply-To: <24934.1290031132@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit


>> What's odd about this is that the resulting "lock pileup" takes a 
>> mysterious 2-3.5 seconds to clear, despite the fact that none of the 
>> connections are *doing* anything during that time, nor are there 
>> deadlock errors.  In theory at least, the locks should clear out in 
>> reverse order in less than a second; none of the individual statements 
>> takes more than 10ms to execute.

Ok, I've collected more data.   Looks like the case I was examining was
idiosyncratic; most of these lock pile-ups involve 400 or more locks
waiting held by around 20 different backends.  Given this, taking 3
seconds to sort that all out doesn't seem that unreasonable.
Presumably there's a poll cycle of some sort for waiting statements?

Anyway, the obvious answer is for the user to fix their application.

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