db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-4437) Concurrent inserts into table with identity column perform poorly
Date Tue, 21 Jun 2011 20:07:48 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4437?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

Rick Hillegas updated DERBY-4437:

    Attachment: derby-4437-05-aa-pluggablePreallocation.diff

Attaching derby-4437-05-aa-pluggablePreallocation.diff. This patch implements pluggable allocators
for sequence/identity ranges so that customers can override Derby's default logic for determining
how long the pre-allocated ranges should be. I am running tests now.

This patch does the following:

1) Introduces a new public api interface: SequencePreallocator. Customers can customize how
they want to pre-allocate sequence/identity ranges by implementing their own SequencePreallocator
and then pointing the following new Derby property  at it. The property can be set at the
system, database, and derby.properties levels.


2) Supplies a default implementation of SequencePreallocator. For this first increment, the
default implementation just specifies the range size used in 10.8 (5 values).

In a follow-on patch, I will recode the default SequencePreallocator to implement what Mike
suggested: The size of the range will keep growing until it reaches the limit that the application
can handle. Over time the range may shrink again if the application needs fewer values. Hopefully,
this will be good enough for a scalable out-of-the-box experience.

Customers can write their own SequencePreallocators to do the following:

1) Set the preallocation value to 1. This eliminates the leaking of preallocated values when
the VM exits gracelessly--at the cost of losing the extra concurrency addressed by this JIRA.

2) Set the preallocation value to some other, larger, hardcoded value.

3) Optimize preallocation to handle spikes: don't ever shrink the size of the range, just
grow it as necessary.

Touches the following files:


M      java/engine/org/apache/derby/iapi/reference/Property.java
A      java/engine/org/apache/derby/catalog/SequencePreallocator.java
M      tools/javadoc/publishedapi.ant

New property and the interface which customers can implement in order to control how Derby
pre-allocates ranges.


M      java/engine/org/apache/derby/loc/messages.xml
M      java/shared/org/apache/derby/shared/common/reference/SQLState.java

New and changed messages.


M      java/engine/org/apache/derby/impl/sql/catalog/SequenceGenerator.java
A      java/engine/org/apache/derby/impl/sql/catalog/SequenceRange.java
M      java/engine/org/apache/derby/impl/sql/catalog/SequenceUpdater.java

Replaces the old hard-coded range allocation with the new pluggable scheme.


M      java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java

Corrects a typo here.


M      java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceGeneratorTest.java

New tests to verify the behavior of custom SequencePreallocators.

> 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:
>            Reporter: Knut Anders Hatlen
>            Assignee: Rick Hillegas
>         Attachments: D4437PerfTest.java, D4437PerfTest2.java, 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,
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
> 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


View raw message