pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLock pileup causes server to stall
8+ messages / 4 participants
[nested] [flat]
* Lock pileup causes server to stall
@ 2014-11-10 19:50 Josh Berkus <josh@agliodbs.com>
2014-11-10 21:40 ` Re: Lock pileup causes server to stall Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 8+ messages in thread
From: Josh Berkus @ 2014-11-10 19:50 UTC (permalink / raw)
To: pgsql-performance
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.
Other info:
* This applies even to queries which are against other databases, so
it's not purely a lock blocking issue.
* this database routinely has a LOT of lock conlicts, churning through 1
million multixacts per day
* pgBouncer is also involved in this stack, and may be contributing to
the problem in some way
* at no time is the DB server out of CPU (max usage = 38%), RAM, or
doing major IO (max %util = 22%).
* BIND statements can be slow as well as EXECUTEs.
I don't have full query logs from a stall period yet, so I'll have more
information when I do: for example, is it ALL queries which are slow or
just some of them?
However, I thought this list would have some other ideas where to look.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Lock pileup causes server to stall
2014-11-10 19:50 Lock pileup causes server to stall Josh Berkus <josh@agliodbs.com>
@ 2014-11-10 21:40 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2014-11-11 17:11 ` Re: Lock pileup causes server to stall Josh Berkus <josh@agliodbs.com>
2014-11-12 06:49 ` Re: Lock pileup causes server to stall Jesper Krogh <jesper@krogh.cc>
0 siblings, 2 replies; 8+ messages in thread
From: Alvaro Herrera @ 2014-11-10 21:40 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Lock pileup causes server to stall
2014-11-10 19:50 Lock pileup causes server to stall Josh Berkus <josh@agliodbs.com>
2014-11-10 21:40 ` Re: Lock pileup causes server to stall Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2014-11-11 17:11 ` Josh Berkus <josh@agliodbs.com>
2014-11-11 17:33 ` Re: Lock pileup causes server to stall Jeff Janes <jeff.janes@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Josh Berkus @ 2014-11-11 17:11 UTC (permalink / raw)
To: pgsql-performance
On 11/10/2014 01:40 PM, Alvaro Herrera wrote:
> 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.
Yeah, I think this is more likely a problem with the general lock table
and shared_buffers than anything to do with actual lock-blocks.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Lock pileup causes server to stall
2014-11-10 19:50 Lock pileup causes server to stall Josh Berkus <josh@agliodbs.com>
2014-11-10 21:40 ` Re: Lock pileup causes server to stall Alvaro Herrera <alvherre@2ndquadrant.com>
2014-11-11 17:11 ` Re: Lock pileup causes server to stall Josh Berkus <josh@agliodbs.com>
@ 2014-11-11 17:33 ` Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jeff Janes @ 2014-11-11 17:33 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
On Tue, Nov 11, 2014 at 9:11 AM, Josh Berkus <josh@agliodbs.com> wrote:
> On 11/10/2014 01:40 PM, Alvaro Herrera wrote:
> > 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.
>
> Yeah, I think this is more likely a problem with the general lock table
> and shared_buffers than anything to do with actual lock-blocks.
>
>
Any chance you can run 'perf record -a' on it?
Cheers,
Jeff
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Lock pileup causes server to stall
2014-11-10 19:50 Lock pileup causes server to stall Josh Berkus <josh@agliodbs.com>
2014-11-10 21:40 ` Re: Lock pileup causes server to stall Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2014-11-12 06:49 ` Jesper Krogh <jesper@krogh.cc>
2014-11-12 13:51 ` Re: Lock pileup causes server to stall Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 1 reply; 8+ messages in thread
From: Jesper Krogh @ 2014-11-12 06:49 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
> On 10/11/2014, at 22.40, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>
> 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.
Even if it is an gin index that is being modified? seems like a harsh limitation to me.
Jesper
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Lock pileup causes server to stall
2014-11-10 19:50 Lock pileup causes server to stall Josh Berkus <josh@agliodbs.com>
2014-11-10 21:40 ` Re: Lock pileup causes server to stall Alvaro Herrera <alvherre@2ndquadrant.com>
2014-11-12 06:49 ` Re: Lock pileup causes server to stall Jesper Krogh <jesper@krogh.cc>
@ 2014-11-12 13:51 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2014-11-12 17:04 ` Re: Lock pileup causes server to stall Josh Berkus <josh@agliodbs.com>
2014-11-13 06:53 ` Re: Lock pileup causes server to stall Jesper Krogh <jesper@krogh.cc>
0 siblings, 2 replies; 8+ messages in thread
From: Alvaro Herrera @ 2014-11-12 13:51 UTC (permalink / raw)
To: Jesper Krogh <jesper@krogh.cc>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
Jesper Krogh wrote:
>
> > On 10/11/2014, at 22.40, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> >
> > 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.
>
> Even if it is an gin index that is being modified? seems like a harsh limitation to me.
Well, as I recall it's only unique indexes, so it's not *that* harsh.
Anyway, the fklocks patch was stupidly complex (and still got much stuff
wrong). I didn't want to add more ground to objections by additionally
breaking the abstraction between heapam and the concept of "columns
referenced by a foreign key constraint". So it was discussed and
decided we'd leave that for future improvement. Patches are welcome,
particularly if they come from the future.
--
Á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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Lock pileup causes server to stall
2014-11-10 19:50 Lock pileup causes server to stall Josh Berkus <josh@agliodbs.com>
2014-11-10 21:40 ` Re: Lock pileup causes server to stall Alvaro Herrera <alvherre@2ndquadrant.com>
2014-11-12 06:49 ` Re: Lock pileup causes server to stall Jesper Krogh <jesper@krogh.cc>
2014-11-12 13:51 ` Re: Lock pileup causes server to stall Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2014-11-12 17:04 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 8+ messages in thread
From: Josh Berkus @ 2014-11-12 17:04 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; Jesper Krogh <jesper@krogh.cc>; +Cc: pgsql-performance
On 11/12/2014 05:51 AM, Alvaro Herrera wrote:
> Anyway, the fklocks patch was stupidly complex (and still got much stuff
> wrong). I didn't want to add more ground to objections by additionally
> breaking the abstraction between heapam and the concept of "columns
> referenced by a foreign key constraint".
Oh, come on. We had hardly any problems with that patch!
;-)
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Lock pileup causes server to stall
2014-11-10 19:50 Lock pileup causes server to stall Josh Berkus <josh@agliodbs.com>
2014-11-10 21:40 ` Re: Lock pileup causes server to stall Alvaro Herrera <alvherre@2ndquadrant.com>
2014-11-12 06:49 ` Re: Lock pileup causes server to stall Jesper Krogh <jesper@krogh.cc>
2014-11-12 13:51 ` Re: Lock pileup causes server to stall Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2014-11-13 06:53 ` Jesper Krogh <jesper@krogh.cc>
1 sibling, 0 replies; 8+ messages in thread
From: Jesper Krogh @ 2014-11-13 06:53 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
>>>>
>>>
>>> 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.
>>
>> Even if it is an gin index that is being modified? seems like a harsh limitation to me.
>
> Well, as I recall it's only unique indexes, so it's not *that* harsh.
>
Sounds good. Indices are there for all kinds of reasons, unique ones are more related to referential integrity, so even not 100% accurate, at least 90% of the way in my world.
We do have an "star"-schema in the db with some amount of information needed in the center that needs updates, apart from that a massive update activity on the sorrounding columns, locks on the center entity has quite high impact on the sorrounding updates. (9.2 moving to 9.3 reallly soon and looking forward for this enhancement.
Jesper
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2014-11-13 06:53 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-11-10 19:50 Lock pileup causes server to stall Josh Berkus <josh@agliodbs.com>
2014-11-10 21:40 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2014-11-11 17:11 ` Josh Berkus <josh@agliodbs.com>
2014-11-11 17:33 ` Jeff Janes <jeff.janes@gmail.com>
2014-11-12 06:49 ` Jesper Krogh <jesper@krogh.cc>
2014-11-12 13:51 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2014-11-12 17:04 ` Josh Berkus <josh@agliodbs.com>
2014-11-13 06:53 ` Jesper Krogh <jesper@krogh.cc>
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