pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAnyone seen this kind of lock pileup?
6+ messages / 3 participants
[nested] [flat]
* Anyone seen this kind of lock pileup?
@ 2010-11-17 17:37 Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 6+ messages in thread
From: Josh Berkus @ 2010-11-17 17:37 UTC (permalink / raw)
To: pgsql-performance
All,
Having an interesting issue on one 8.4 database. Due to poor
application design, the application is requesting 8-15 exclusive
(update) locks on the same row on parallel connections pretty much
simultaneously (i.e. < 50ms apart).
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.
Has anyone else seen something like this? Any idea what causes it?
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Anyone seen this kind of lock pileup?
@ 2010-11-17 21:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 2 replies; 6+ messages in thread
From: Tom Lane @ 2010-11-17 21:58 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
Josh Berkus <josh@agliodbs.com> writes:
> Having an interesting issue on one 8.4 database. Due to poor
> application design, the application is requesting 8-15 exclusive
> (update) locks on the same row on parallel connections pretty much
> simultaneously (i.e. < 50ms apart).
> 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.
Hmm ... can you extract a test case? Or at least strace the backends
involved?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Anyone seen this kind of lock pileup?
@ 2010-11-17 22:02 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2010-11-17 22:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
> Hmm ... can you extract a test case? Or at least strace the backends
> involved?
No, and no. Strace was the first thing I thought of, but I'd have to
somehow catch one of these backends in the 3 seconds it's locked. Not
really feasible.
It might be possible to construct a test case, depending on how much the
user wants to spend on the problem. I'd estimate that a test case would
take 8-12 hours of my time to get working, given the level of activity
and concurrency required.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Anyone seen this kind of lock pileup?
@ 2010-11-17 22:53 Ivan Voras <ivoras@freebsd.org>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 6+ messages in thread
From: Ivan Voras @ 2010-11-17 22:53 UTC (permalink / raw)
To: pgsql-performance
On 11/17/10 18:37, Josh Berkus wrote:
> All,
>
> Having an interesting issue on one 8.4 database. Due to poor application
> design, the application is requesting 8-15 exclusive (update) locks on
> the same row on parallel connections pretty much simultaneously (i.e. <
> 50ms apart).
>
> 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.
Just a random guess: a timeout-supported livelock? (of course if there
is any timeout-and-retry protocol going on and the timeout intervals are
non-randomized).
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Anyone seen this kind of lock pileup?
@ 2010-11-17 23:42 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2010-11-17 23:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
>> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Anyone seen this kind of lock pileup?
@ 2010-11-17 23:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2010-11-17 23:56 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
Josh Berkus <josh@agliodbs.com> writes:
> 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?
No ... but if the lock requests were mutually exclusive, I could believe
it taking 3 seconds for all of the waiting backends to get their turn
with the lock, do whatever they were gonna do, commit, and release the
lock to the next guy.
> Anyway, the obvious answer is for the user to fix their application.
Probably.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-11-17 23:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-17 17:37 Anyone seen this kind of lock pileup? Josh Berkus <josh@agliodbs.com>
2010-11-17 21:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-11-17 22:02 ` Josh Berkus <josh@agliodbs.com>
2010-11-17 23:42 ` Josh Berkus <josh@agliodbs.com>
2010-11-17 23:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-11-17 22:53 ` Ivan Voras <ivoras@freebsd.org>
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