[jira] [Issue Comment Edited] (DERBY-4437) Concurrent inserts into table with identity column perform poorly

["Mike Matrigali (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-4437?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13054576#comment-13054576 ] 

Mike Matrigali edited comment on DERBY-4437 at 6/24/11 7:10 PM:
----------------------------------------------------------------

Here are my thoughts.  The work so far looks great and I would be fine seeing it checked in as is and backported.  I think it is 
very reasonable to log a JIRA for the tunable aspect and hope someone is interested in that work.  I actually think that is better as I would
rather see the tunable aspect go into only trunk and a new release rather than backported as a bug fix - since as you point out it is experimental.
It would be great if we could have a discussion there on possible ideas for an algorithm there.  I actually think the project could be done by
a newcomer as the coding would be very localized once you check in, the hard part is just coding the tuning stuff in one place.  Might have
to do some new work to have interesting inputs to the algorithm.

It may also be worth logging a separate improvement JIRA if one does not exist to solve the lost range on JIRA crash.  This is not a simple problem, and may
not be worth effort but might as well create a place holder.  It would somehow probably need new log records and specialized recovery of those during crash
recovery.  It is complicated in that it is logical work that needs to be done above store but currently only store work is done during crash recovery.  Another 
option would be some brute force work to actually scan the tables on recovery and find the "highest" and reset the range before an application came in.  If
there are indexes on these things this becomes much faster.  Some of this work is similar to the problem we have with post commit space reclamation 
work that is also lost when a crash happens.

The following would be my votes on the changes you mentioned you are willing to do, but would not argue strongly with opposing views:

o if a knob is added so a user could backtrack if they did not like losing ranges, then I would bump the default to something like 20, given
   knut's experiments and rick's notes on other databases defaults.  I would backport this change to 10.8, again only if knob was included.  
   Given how fast processors are nowadays adding
   a commit I/O for every 5 inserts seems a high price, when the system is doing 900 inserts a second.

o If no knob I think I would leave default of 5 for backport to 10.8 and bump default to 20 in trunk.  We could give notice in 10.8 release that upcoming
   10.9 would bump this default.

o Using the properties seems like complicated syntax to me.  I assume using "alter table" is not possible as there is no standard in this area.  What would people
   think about using a system procedure instead of a property.  That way the call could simply take the standard application and table name arguments, and
   would require the usual alter table database permissions to set.  Underneath the procedure call it should just call an internal alter table call.  

      was (Author: mikem):
    Here are my thoughts.  The work so far looks great and I would be fine seeing it checked in as is and backported.  I think it is 
very reasonable to log a JIRA for the tunable aspect and hope someone is interested in that work.  I actually think that is better as I would
rather see the tunable aspect go into only trunk and a new release rather than backported as a bug fix - since as you point out it is experimental.
It would be great if we could have a discussion there on possible ideas for an algorithm there.  I actually think the project could be done by
a newcomer as the coding would be very localized once you check in, the hard part is just coding the tuning stuff in one place.  Might have
to do some new work to have interesting inputs to the algorithm.

It may also be worth logging a separate improvement JIRA if one does not exist to solve the lost range on JIRA crash.  This is not a simple problem, and may
not be worth effort but might as well create a place holder.  It would somehow probably need new log records and specialized recovery of those during crash
recovery.  It is complicated in that it is logical work that needs to be done above store but currently only store work is done during crash recovery.  Another 
option would be some brute force work to actually scan the tables on recovery and find the "highest" and reset the range before an application came in.  If
there are indexes on these things this becomes much faster.  Some of this work is similar to the problem we have with post commit space reclamation 
work that is also lost when a crash happens.

The following would be my votes on the changes you mentioned you are willing to do, but would not argue strongly with opposing views:

o if a knob is added so a user could backtrack if they did not like losing ranges, then I would bump the default to something like 200, given
   knut's experiments.  I would backport this change to 10.8, again only if knob was included.  Given how fast processors are nowadays adding
   a commit I/O for every 5 inserts seems a high price, when the system is doing 900 inserts a second.

o If no knob I think I would leave default of 5 for backport to 10.8 and bump default to 200 in trunk.  We could give notice in 10.8 release that upcoming
   10.9 would bump this default.

o Using the properties seems like complicated syntax to me.  I assume using "alter table" is not possible as there is no standard in this area.  What would people
   think about using a system procedure instead of a property.  That way the call could simply take the standard application and table name arguments, and
   would require the usual alter table database permissions to set.  Underneath the procedure call it should just call an internal alter table call.  
  
> Concurrent inserts into table with identity column perform poorly
> -----------------------------------------------------------------
>
>                 Key: DERBY-4437
>                 URL: https://issues.apache.org/jira/browse/DERBY-4437
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.5.3.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Rick Hillegas
>         Attachments: D4437PerfTest.java, D4437PerfTest2.java, Experiments_4437.html, derby-4437-01-aj-allTestsPass.diff, derby-4437-02-ac-alterTable-bulkImport-deferredInsert.diff, derby-4437-03-aa-upgradeTest.diff, derby-4437-04-aa-reclaimUnusedValuesOnShutdown.diff, derby-4437-05-aa-pluggablePreallocation.diff, derby-4437-06-aa-selfTuning.diff, insertperf.png, insertperf2.png, prealloc.png
>
>
> I have a multi-threaded application which is very insert-intensive. I've noticed that it sometimes can come into a state where it slows down considerably and basically becomes single-threaded. This is especially harmful on modern multi-core machines since most of the available resources are left idle.
> The problematic tables contain identity columns, and here's my understanding of what happens:
> 1) Identity columns are generated from a counter that's stored in a row in SYS.SYSCOLUMNS. During normal operation, the counter is maintained in a nested transaction within the transaction that performs the insert. This allows the nested transaction to commit the changes to SYS.SYSCOLUMN separately from the main transaction, and the exclusive lock that it needs to obtain on the row holding the counter, can be releases after a relatively short time. Concurrent transactions can therefore insert into the same table at the same time, without needing to wait for the others to commit or abort.
> 2) However, if the nested transaction cannot lock the row in SYS.SYSCOLUMNS immediately, it will give up and retry the operation in the main transaction. This prevents self-deadlocks in the case where the main transaction already owns a lock on SYS.SYSCOLUMNS. Unfortunately, this also increases the time the row is locked, since the exclusive lock cannot be released until the main transaction commits. So as soon as there is one lock collision, the waiting transaction changes to a locking mode that increases the chances of others having to wait, which seems to result in all insert threads having to obtain the SYSCOLUMNS locks in the main transaction. The end result is that only one of the insert threads can execute at any given time as long as the application is in this state.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira