Обсуждение: Locking or Something Else?

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?

Thanks!

Ian
 PostgreSQL 9.0.6 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit

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.

If you are wrapping into transactions, then it may be that your disk
subsystem has slow fsyncs.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   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

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.

If you are wrapping into transactions, then it may be that your disk
subsystem has slow fsyncs.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   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