Re: Lock pileup causes server to stall

От: Alvaro Herrera
Тема: Re: Lock pileup causes server to stall
Дата: ,
Msg-id: 20141112135110.GX1791@alvin.alvh.no-ip.org
(см: обсуждение, исходный текст)
Ответ на: Re: Lock pileup causes server to stall  (Jesper Krogh)
Ответы: Re: Lock pileup causes server to stall  (Jesper Krogh)
Список: pgsql-performance

Скрыть дерево обсуждения

Lock pileup causes server to stall  (Josh Berkus, )
 Re: Lock pileup causes server to stall  (Alvaro Herrera, )
  Re: Lock pileup causes server to stall  (Jesper Krogh, )
   Re: Lock pileup causes server to stall  (Alvaro Herrera, )
    Re: Lock pileup causes server to stall  (Jesper Krogh, )
 Re: Lock pileup causes server to stall  (Josh Berkus, )
  Re: Lock pileup causes server to stall  (Jeff Janes, )
 Re: Lock pileup causes server to stall  (Josh Berkus, )

Jesper Krogh wrote:
>
> > On 10/11/2014, at 22.40, 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.
>
> 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



В списке pgsql-performance по дате сообщения:

От: Josh Berkus
Дата:
Сообщение: Re: Lock pileup causes server to stall
От: Jesper Krogh
Дата:
Сообщение: Re: Lock pileup causes server to stall