Re: Lock pileup causes server to stall

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Jesper Krogh wrote:
> 
> > On 10/11/2014, at 22.40, Alvaro Herrera <alvherre@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux