Re: Locking or Something Else?

On Sunday, May 20, 2012, Martijn van Oosterhout wrote:

> On Sun, May 20, 2012 at 12:26:26AM -0700, Ian Harding wrote:
> > I have a situation where an increase in volume of inserts into the
> > main transaction table causes a huge slowdown. The table has lots of
> > indexes and foreign keys and a trigger.
> >
> > Clearly, something is causing a resource contention issue, but here's
> > my main question:
> >
> > I have log_lock_waits = on and deadlock_timeout = 250ms. Is there
> > any way I could have delays of tens of seconds caused by a write
> > conflict in a table accessed by a trigger that would not result in the
> > wait being logged?
>
> The most common cause for slowdowns during inserts is if you're not
> wrapping them into large transactions. The deadlock timeout only
> tracks deadlocks, it won't trigger on normal lock waits. There can be
> issues with respect to foreign keys, but I didn't think they are
> triggered on inserts.

I must have misunderstood log_lock_waits. I thought it logged all lock
waits longer than deadlock_timeout.

>
> If you are wrapping into transactions, then it may be that your disk
> subsystem has slow fsyncs.
>
> Hope this helps,
> --
> Martijn van Oosterhout <kleptog(at)svana(dot)org <javascript:;>>
> http://svana.org/kleptog/
> > He who writes carelessly confesses thereby at the very outset that he
> does
> > not attach much importance to his own thoughts.
> -- Arthur Schopenhauer
>