Derby
  1. Derby
  2. DERBY-5494

Same value returned by successive calls to a sequence generator flanking an unorderly shutdown.

    Details

    • Urgency:
      Normal
    • Issue & fix info:
      High Value Fix, Known fix, Patch Available, Repro attached
    • Bug behavior facts:
      Deviation from standard

      Description

      The following sequence of steps causes a sequence generator to return the same value on successive NEXT VALUE FOR calls.

      1) Bring up ij and issue the following commands:

      connect 'jdbc:derby:db;create=true';

      create sequence s;
      values next value for s;

      2) Control-c out of ij so that the engine comes down hard without an orderly shutdown.

      3) Now bring up ij again and issue the following commands:

      connect 'jdbc:derby:db';

      values next value for s;

      Thanks to Knut for finding this one.

      1. derby-5494_3.diff
        26 kB
        Mike Matrigali
      2. derby-5494_diff_1a.txt
        0.8 kB
        Mike Matrigali
      3. derby-5494_diff_2.txt
        7 kB
        Mike Matrigali

        Issue Links

          Activity

          Hide
          Kathey Marsden added a comment -

          Does this issue affect identity columns in 10.8 or trunk?

          Show
          Kathey Marsden added a comment - Does this issue affect identity columns in 10.8 or trunk?
          Hide
          Rick Hillegas added a comment -

          Hi Kathey,

          There may be a variant of this issue which affects identity columns in 10.9. There may also be a variant of this issue which affects identity columns in 10.8, 10.7, and all the way back. I say that because the transaction trickiness in the sequence generators was modelled on similar trickiness in the pre-exisiting identity implementation. But I haven't investigated the implications for identity columns yet. Hopefully the solution to this problem will address identity columns, at least in 10.9.

          Show
          Rick Hillegas added a comment - Hi Kathey, There may be a variant of this issue which affects identity columns in 10.9. There may also be a variant of this issue which affects identity columns in 10.8, 10.7, and all the way back. I say that because the transaction trickiness in the sequence generators was modelled on similar trickiness in the pre-exisiting identity implementation. But I haven't investigated the implications for identity columns yet. Hopefully the solution to this problem will address identity columns, at least in 10.9.
          Hide
          Rick Hillegas added a comment -

          To answer Kathey's question more exactly: Yes, a version of this problem also affects identity columns. This is true regardless of whether you use the new identity generators (currently implemented in 10.9) or the old-style identity generators (in 10.8).

          According to the SQL Standard, part 2, section 4.14.7, identity columns are supposed to behave as though they are backed by internal sequence generators. That means that commits and rollbacks are not supposed to affect identity values.

          The following scripts show that you can make an identity column produce the same value in two successive invocations.

          First run this script to generate a value and then rollback:

          connect 'jdbc:derby:db;create=true';

          create procedure systemExit( in exitCode int )
          language java parameter style java no sql
          external name 'java.lang.System.exit';

          create table t( a int generated always as identity, b int );

          autocommit off;

          select count from sys.syscolumns with rs;

          insert into t( b ) values ( 0 );

          – here you see that the identity column has value 1
          select * from t;

          rollback;

          select * from t;

          call systemExit( 0 );

          Now run this script to generate a new value (which turns out to be the original value):

          connect 'jdbc:derby:db';

          insert into t( b ) values ( 1 );

          – here you see that the identity column again has value 1
          select * from t;

          Show
          Rick Hillegas added a comment - To answer Kathey's question more exactly: Yes, a version of this problem also affects identity columns. This is true regardless of whether you use the new identity generators (currently implemented in 10.9) or the old-style identity generators (in 10.8). According to the SQL Standard, part 2, section 4.14.7, identity columns are supposed to behave as though they are backed by internal sequence generators. That means that commits and rollbacks are not supposed to affect identity values. The following scripts show that you can make an identity column produce the same value in two successive invocations. First run this script to generate a value and then rollback: connect 'jdbc:derby:db;create=true'; create procedure systemExit( in exitCode int ) language java parameter style java no sql external name 'java.lang.System.exit'; create table t( a int generated always as identity, b int ); autocommit off; select count from sys.syscolumns with rs; insert into t( b ) values ( 0 ); – here you see that the identity column has value 1 select * from t; rollback; select * from t; call systemExit( 0 ); Now run this script to generate a new value (which turns out to be the original value): connect 'jdbc:derby:db'; insert into t( b ) values ( 1 ); – here you see that the identity column again has value 1 select * from t;
          Hide
          Rick Hillegas added a comment -

          Here is another view on the bug. In this view we add another connection which confirms that the original "next value for" statement durably commits a change to SYSSEQUENCES. But after the system crash, that supposedly durable change has rolled back.

          1) Run this script. The select from the second thread shows that SYSSEQUENCES has changed:

          connect 'jdbc:derby:db;create=true' as dbo;
          create procedure systemExit( in exitCode int ) language java parameter style java no sql external name 'java.lang.System.exit';
          create sequence s;

          values next value for s;

          connect 'jdbc:derby:db';
          select currentvalue from sys.syssequences where sequencename='S';

          set connection dbo;
          call systemExit( 1 );

          2) Now run this script. You will see that the change to SYSSEQUENCES has been rolled back:

          connect 'jdbc:derby:db';
          select currentvalue from sys.syssequences where sequencename='S';

          Show
          Rick Hillegas added a comment - Here is another view on the bug. In this view we add another connection which confirms that the original "next value for" statement durably commits a change to SYSSEQUENCES. But after the system crash, that supposedly durable change has rolled back. 1) Run this script. The select from the second thread shows that SYSSEQUENCES has changed: connect 'jdbc:derby:db;create=true' as dbo; create procedure systemExit( in exitCode int ) language java parameter style java no sql external name 'java.lang.System.exit'; create sequence s; values next value for s; connect 'jdbc:derby:db'; select currentvalue from sys.syssequences where sequencename='S'; set connection dbo; call systemExit( 1 ); 2) Now run this script. You will see that the change to SYSSEQUENCES has been rolled back: connect 'jdbc:derby:db'; select currentvalue from sys.syssequences where sequencename='S';
          Hide
          Mike Matrigali added a comment -

          i am looking at the latest test case posted in comments. Looking at log traces is seems like update of catalog row does get done in a nested transaction, and commit record is written to stream, but does not look like a force of the log is happening at that point. Not sure why not, yet. For some internal/nested transactions this behavior is ok as rather get the performance of a nosync commit and we don't really care as long as either all or none of the transaction gets done. The is the case with btree splits. I think it also makes sense for identity columns as the nested transaction only is important once some other transaction that uses one of the value commits, which in turn will force this transaction.

          I don't think sequences should be using this optimization and thought that only would happen if they called commitNoSync() which I did not see in the code.

          Show
          Mike Matrigali added a comment - i am looking at the latest test case posted in comments. Looking at log traces is seems like update of catalog row does get done in a nested transaction, and commit record is written to stream, but does not look like a force of the log is happening at that point. Not sure why not, yet. For some internal/nested transactions this behavior is ok as rather get the performance of a nosync commit and we don't really care as long as either all or none of the transaction gets done. The is the case with btree splits. I think it also makes sense for identity columns as the nested transaction only is important once some other transaction that uses one of the value commits, which in turn will force this transaction. I don't think sequences should be using this optimization and thought that only would happen if they called commitNoSync() which I did not see in the code.
          Hide
          Mike Matrigali added a comment -

          I am not sure yet how to fix, but think I understand the issue now. At least as far back as 10.1, the system always does a no sync commit for any transaction that is not a user transaction. I don't think this is a problem for anything other than sequences, but welcome opinions.

          The comments in the code are correct but incomplete, I think the code was written before there were any nested user transactions. the
          internal and nested top transactions are store internal things that do not require flushing. I am guessing since this code was added
          we added the 2 new nested transactions, for the sql layer.

          I hacked the code below to always return true and the test case worked, but this is not correct fix.

          An easy fix that might work is to return true for user transactions and NestedRawUpdateUserTransaction. The problem with this change is it will
          slow down operations that use NestedRawUpdateUserTransaction but not require forcing the log on commit.

          The system already has commit and
          commitNoSync interfaces, so I think the right longterm fix would be to get rid of this code completely and change all the callers to correctly call
          commit or commitNoSync.

          The problem code is in xactfactory.java and then is called by Xact.java:
          /**
          Decide if a transaction of this contextId needs to flush the log when
          it commits
          */
          public boolean flushLogOnCommit(String contextName)

          { // // if this is a user transaction, flush the log // if this is an internal or nested top transaction, do not // flush, let it age out. // /* return (contextName == USER_CONTEXT_ID || contextName.equals(USER_CONTEXT_ID)); */ return(true); }
          Show
          Mike Matrigali added a comment - I am not sure yet how to fix, but think I understand the issue now. At least as far back as 10.1, the system always does a no sync commit for any transaction that is not a user transaction. I don't think this is a problem for anything other than sequences, but welcome opinions. The comments in the code are correct but incomplete, I think the code was written before there were any nested user transactions. the internal and nested top transactions are store internal things that do not require flushing. I am guessing since this code was added we added the 2 new nested transactions, for the sql layer. I hacked the code below to always return true and the test case worked, but this is not correct fix. An easy fix that might work is to return true for user transactions and NestedRawUpdateUserTransaction. The problem with this change is it will slow down operations that use NestedRawUpdateUserTransaction but not require forcing the log on commit. The system already has commit and commitNoSync interfaces, so I think the right longterm fix would be to get rid of this code completely and change all the callers to correctly call commit or commitNoSync. The problem code is in xactfactory.java and then is called by Xact.java: /** Decide if a transaction of this contextId needs to flush the log when it commits */ public boolean flushLogOnCommit(String contextName) { // // if this is a user transaction, flush the log // if this is an internal or nested top transaction, do not // flush, let it age out. // /* return (contextName == USER_CONTEXT_ID || contextName.equals(USER_CONTEXT_ID)); */ return(true); }
          Hide
          Mike Matrigali added a comment -

          patch not ready for commit. First step of proposed fix, this change causes all user level nested write transactions to flush commit log record. Next step is to isolate the flushing to just sequences as I don't understand the performance ramifications of other usages in SQL layer of the nested write transaction. Seems to fix ij repro in this JIRA, going to run tests over night to see if it has any obvious problems. needs some comments also.

          Show
          Mike Matrigali added a comment - patch not ready for commit. First step of proposed fix, this change causes all user level nested write transactions to flush commit log record. Next step is to isolate the flushing to just sequences as I don't understand the performance ramifications of other usages in SQL layer of the nested write transaction. Seems to fix ij repro in this JIRA, going to run tests over night to see if it has any obvious problems. needs some comments also.
          Hide
          Mike Matrigali added a comment -

          derby-5494_diff_1a.txt passed all tests, still more work needed for checkin. Note that this fix will decrease the performance of using sequences. The amount of the affect depends on how many "block" updates done per measured benchmark, and if the benchmark must wait on those updates. I think the worst case would be a single user insert benchmark that rarely commits, using one or more next sequence calls per inserted row. Large numbers of inserts per transaction is the usual case we recommend for users that want to insert as fast as possbile. I have not done the measurement recently but it use to be not unusual that you had to do > 10000 inserts per transaction to max out cpu on a single processor or you would just get I/O bottlenecked on the commit record. Make sure to measure on a machine that has proper disk syncing. After this fix each block update will have to wait for an I/O to hit disk at commit time.

          Show
          Mike Matrigali added a comment - derby-5494_diff_1a.txt passed all tests, still more work needed for checkin. Note that this fix will decrease the performance of using sequences. The amount of the affect depends on how many "block" updates done per measured benchmark, and if the benchmark must wait on those updates. I think the worst case would be a single user insert benchmark that rarely commits, using one or more next sequence calls per inserted row. Large numbers of inserts per transaction is the usual case we recommend for users that want to insert as fast as possbile. I have not done the measurement recently but it use to be not unusual that you had to do > 10000 inserts per transaction to max out cpu on a single processor or you would just get I/O bottlenecked on the commit record. Make sure to measure on a machine that has proper disk syncing. After this fix each block update will have to wait for an I/O to hit disk at commit time.
          Hide
          Mike Matrigali added a comment -

          This is the proposed code patch for this issue, it passes all tests on my machine (windows, xp). Before committing I am planning on including the crash test that rick has as part of DERBY-5493.

          Show
          Mike Matrigali added a comment - This is the proposed code patch for this issue, it passes all tests on my machine (windows, xp). Before committing I am planning on including the crash test that rick has as part of DERBY-5493 .
          Hide
          Kristian Waagan added a comment -

          I noticed this code in the commit mail:

          +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/store/raw/xact/XactFactory.java Tue Apr 17 18:40:15 2012
          @@ -943,12 +943,18 @@ public class XactFactory implements Tran
          public boolean flushLogOnCommit(String contextName)

          { // - // if this is a user transaction, flush the log + // if this is a user transaction, flush the log by default. + // if this is a nested user update transaction, flush log by default. // if this is an internal or nested top transaction, do not // flush, let it age out. + // + // In all cases log will not be flushsed by Xact.prepareCommit() + // if commitNoSync() has been called rather than commit. // - return (contextName == USER_CONTEXT_ID || - contextName.equals(USER_CONTEXT_ID)); + return (contextName == USER_CONTEXT_ID || + contextName.equals(USER_CONTEXT_ID) || + contextName == NESTED_UPDATE_USER_CONTEXT_ID || + contextName.equals(NESTED_UPDATE_USER_CONTEXT_ID)); }

          It appears that reference equality is used as an optimization, i.e. to avoid calling equals in many/some cases. With that in mind, would it make sense to group the == together first, and then the equals next?
          Also, a comment on the use of == for string comparison wouldn't hurt.

          Show
          Kristian Waagan added a comment - I noticed this code in the commit mail: +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/store/raw/xact/XactFactory.java Tue Apr 17 18:40:15 2012 @@ -943,12 +943,18 @@ public class XactFactory implements Tran public boolean flushLogOnCommit(String contextName) { // - // if this is a user transaction, flush the log + // if this is a user transaction, flush the log by default. + // if this is a nested user update transaction, flush log by default. // if this is an internal or nested top transaction, do not // flush, let it age out. + // + // In all cases log will not be flushsed by Xact.prepareCommit() + // if commitNoSync() has been called rather than commit. // - return (contextName == USER_CONTEXT_ID || - contextName.equals(USER_CONTEXT_ID)); + return (contextName == USER_CONTEXT_ID || + contextName.equals(USER_CONTEXT_ID) || + contextName == NESTED_UPDATE_USER_CONTEXT_ID || + contextName.equals(NESTED_UPDATE_USER_CONTEXT_ID)); } It appears that reference equality is used as an optimization, i.e. to avoid calling equals in many/some cases. With that in mind, would it make sense to group the == together first, and then the equals next? Also, a comment on the use of == for string comparison wouldn't hurt.
          Hide
          Knut Anders Hatlen added a comment -

          Alternatively, since at least OpenJDK (and I expect most other implementations) already shortcuts String.equals() on reference equality, just skip the reference check?

          Show
          Knut Anders Hatlen added a comment - Alternatively, since at least OpenJDK (and I expect most other implementations) already shortcuts String.equals() on reference equality, just skip the reference check?
          Hide
          Rick Hillegas added a comment -

          Hi Mike,

          Do you think work on this issue has wrapped up and it can be resolved? Thanks.

          Show
          Rick Hillegas added a comment - Hi Mike, Do you think work on this issue has wrapped up and it can be resolved? Thanks.
          Hide
          Mike Matrigali added a comment -

          I would like opinions on whether this should be backported to 10.8 or not. The downside is that performance will go down. I am leaning toward not backporting this or any of the sequence changes.

          There were a couple of comments after checkin, which I would rather address in a separate issue, but don't have time right now. Long term I think the routine in question should be removed with no "default" syncing behavior. All code should just make the expected commit calls that it wants.

          Show
          Mike Matrigali added a comment - I would like opinions on whether this should be backported to 10.8 or not. The downside is that performance will go down. I am leaning toward not backporting this or any of the sequence changes. There were a couple of comments after checkin, which I would rather address in a separate issue, but don't have time right now. Long term I think the routine in question should be removed with no "default" syncing behavior. All code should just make the expected commit calls that it wants.
          Hide
          Rick Hillegas added a comment -

          I don't think this fix should be backported in isolation. We would want to boost the pre-allocation range as well to retrieve the lost concurrency. Then we would want to warn users to always shutdown their databases gracefully in order to avoid leaking the longer preallocation range. Won't this fix degrade the concurrency of identity columns also? That might be a surprising consequence of upgrading to a supposedly stable bug-fix release.

          Show
          Rick Hillegas added a comment - I don't think this fix should be backported in isolation. We would want to boost the pre-allocation range as well to retrieve the lost concurrency. Then we would want to warn users to always shutdown their databases gracefully in order to avoid leaking the longer preallocation range. Won't this fix degrade the concurrency of identity columns also? That might be a surprising consequence of upgrading to a supposedly stable bug-fix release.
          Hide
          Rick Hillegas added a comment -

          Hi Mike,

          Have you reached a conclusion about whether this patch should be backported to 10.8? Thanks.

          Show
          Rick Hillegas added a comment - Hi Mike, Have you reached a conclusion about whether this patch should be backported to 10.8? Thanks.
          Hide
          Mike Matrigali added a comment -

          i do not think we should backport this change until DERBY-5780 is understood. Even then I am still leaning toward not backporting any sequence/identity changes in this area.

          Show
          Mike Matrigali added a comment - i do not think we should backport this change until DERBY-5780 is understood. Even then I am still leaning toward not backporting any sequence/identity changes in this area.
          Hide
          Mike Matrigali added a comment - - edited

          Here is proposed fix for performance degredation of identity columns caused by previous patch. All tests passed for me running sane, classes, write sync disabled, and no special handling for upgrade tests. I am concerned that tests took a lot longer (9+ hours) vs around 6 hours for runs in the past on this new machine doing sane, classes, write sync enabled, but I changed both the write sync option and the patch so don't have a good baseline. Am running more tests before commit.

          The patch changes some interfaces to allow creaters of nested user transactions to set the default sync on transaction end when they
          create the transaction, rather than just relying on type of transaction. The previous patch set the intended behavior for commit but
          abort did not pick up the intended behaviour and it turned out each insert of a identity column row did commit and then destroy which called abort. Gets rid of the previous routine which would check type of xact for every commit so should eliminate some small codepath also.

          Show
          Mike Matrigali added a comment - - edited Here is proposed fix for performance degredation of identity columns caused by previous patch. All tests passed for me running sane, classes, write sync disabled, and no special handling for upgrade tests. I am concerned that tests took a lot longer (9+ hours) vs around 6 hours for runs in the past on this new machine doing sane, classes, write sync enabled, but I changed both the write sync option and the patch so don't have a good baseline. Am running more tests before commit. The patch changes some interfaces to allow creaters of nested user transactions to set the default sync on transaction end when they create the transaction, rather than just relying on type of transaction. The previous patch set the intended behavior for commit but abort did not pick up the intended behaviour and it turned out each insert of a identity column row did commit and then destroy which called abort. Gets rid of the previous routine which would check type of xact for every commit so should eliminate some small codepath also.
          Hide
          Mike Matrigali added a comment -

          Tests all pass and I have verified that the nightly test performance is same before and after the change. plan on committing today unless there are any reviewers who don't think it should go in.

          Show
          Mike Matrigali added a comment - Tests all pass and I have verified that the nightly test performance is same before and after the change. plan on committing today unless there are any reviewers who don't think it should go in.
          Hide
          Rick Hillegas added a comment -

          I applied the derby-5494_3.diff patch and built insane jars. Then I re-ran the experiments described in the 5471-performance.html report attached to DERBY-5471. I observed the following:

          o The IDENTITY 10 and IDENTITY 1 tests now run at the same transaction rate on 10.8.2.2 and the patched trunk.

          o The SEQUENCE 10 and SEQUENCE 1 tests run at the same transaction rate on the trunk and the patched trunk.

          This evidence supports the following conclusions:

          1) The patch restores the concurrency of identity columns seen in 10.8.2.2.

          2) The patch does not affect the concurrency of sequence generators.

          So I say +1 to the patch. Thanks.

          Show
          Rick Hillegas added a comment - I applied the derby-5494_3.diff patch and built insane jars. Then I re-ran the experiments described in the 5471-performance.html report attached to DERBY-5471 . I observed the following: o The IDENTITY 10 and IDENTITY 1 tests now run at the same transaction rate on 10.8.2.2 and the patched trunk. o The SEQUENCE 10 and SEQUENCE 1 tests run at the same transaction rate on the trunk and the patched trunk. This evidence supports the following conclusions: 1) The patch restores the concurrency of identity columns seen in 10.8.2.2. 2) The patch does not affect the concurrency of sequence generators. So I say +1 to the patch. Thanks.
          Hide
          Rick Hillegas added a comment -

          Hi Mike,

          I see that you checked derby-5494_3.diff into the trunk at subversion revision 1344065. It does not appear to me that the fix has been ported to the 10.9 branch yet. Does some additional work need to be done before we port this to 10.9? Thanks.

          Show
          Rick Hillegas added a comment - Hi Mike, I see that you checked derby-5494_3.diff into the trunk at subversion revision 1344065. It does not appear to me that the fix has been ported to the 10.9 branch yet. Does some additional work need to be done before we port this to 10.9? Thanks.
          Hide
          Mike Matrigali added a comment - - edited

          i am hoping no additional work is necessary. I am waiting to see clean trunk results from the nightly tests across platforms that include change 1327471, and once they pass am happy to backport to
          10.9.

          Just realized that no public ibm tests have posted since 5/22. I saw it saying tuesday result and assumed it was yesterday.
          So at this point just have tinderbox nightly result which passed, and no other nightly results..

          looks like i missed the window for the sun runs.

          Show
          Mike Matrigali added a comment - - edited i am hoping no additional work is necessary. I am waiting to see clean trunk results from the nightly tests across platforms that include change 1327471, and once they pass am happy to backport to 10.9. Just realized that no public ibm tests have posted since 5/22. I saw it saying tuesday result and assumed it was yesterday. So at this point just have tinderbox nightly result which passed, and no other nightly results.. looks like i missed the window for the sun runs.
          Hide
          Rick Hillegas added a comment -

          The Oracle nightly tests on the trunk passed cleanly except for a heisenbug in the replication tests on JDK 1.6: http://dbtg.foundry.sun.com/derby/test/Daily/jvm1.6/testing/Limited/testSummary-1344319.html

          Show
          Rick Hillegas added a comment - The Oracle nightly tests on the trunk passed cleanly except for a heisenbug in the replication tests on JDK 1.6: http://dbtg.foundry.sun.com/derby/test/Daily/jvm1.6/testing/Limited/testSummary-1344319.html
          Hide
          Rick Hillegas added a comment -

          The IBM tests raised no errors either. Here are the Windows results: http://people.apache.org/~myrnavl/derby_test_results/main/windows/testSummary-1344525.html Here are the Linux results: http://people.apache.org/~myrnavl/derby_test_results/main/linux/testSummary-1344524.html Are we waiting for some other confirmation before backporting derby-5494_3.diff to 10.9? Thanks.

          Show
          Rick Hillegas added a comment - The IBM tests raised no errors either. Here are the Windows results: http://people.apache.org/~myrnavl/derby_test_results/main/windows/testSummary-1344525.html Here are the Linux results: http://people.apache.org/~myrnavl/derby_test_results/main/linux/testSummary-1344524.html Are we waiting for some other confirmation before backporting derby-5494_3.diff to 10.9? Thanks.
          Hide
          Mike Matrigali added a comment -

          Fixed in 10.10 and backported to unreleased 10.9 branch prior to 10.9 being released. Because of
          change in behavior in syncing and extent of changes I don't think we should backport the changes for
          DERBY-5780 or DERBY-5494 to 10.8 or previous.

          Show
          Mike Matrigali added a comment - Fixed in 10.10 and backported to unreleased 10.9 branch prior to 10.9 being released. Because of change in behavior in syncing and extent of changes I don't think we should backport the changes for DERBY-5780 or DERBY-5494 to 10.8 or previous.
          Hide
          Rick Hillegas added a comment -

          Changed the fix-in version to 10.9.1.0 so that this issue will appear in the 10.9.1.0 release notes.

          Show
          Rick Hillegas added a comment - Changed the fix-in version to 10.9.1.0 so that this issue will appear in the 10.9.1.0 release notes.
          Hide
          Mamta A. Satoor added a comment - - edited

          For both identity column and sequences, I ran two kinds of tests as explained below. I have run these tests 3 times each for 10.9 release(10.9.1.0) candidate and 10.8.1.2 release and have included the results below. The tests were run with write caching disabled.

          The 2 kinds of tests for sequences and identity columns were as follows
          1)default behavior : For the test, when we let it pick the default behavior, number of threads default to 1, preallocate defaults to 100 on 10.9 and 5 on 10.8, numberOfGenerator defaults to 1, tablesPerGenerator defaults to 1 and insertsPerTransaction default to 1
          2)In this kind of run, I passed following paramters to the test.- number of threads 1, preallocation of 1, numberOfGenerator to 1, tablesPerGenerator to 1 and insertsPerTransaction to 100

          Identity test(default behavior)
          java -Dderby.system.home=./systest/db1 org.apache.derbyTesting.perf.clients.Runner -driver org.apache.derby.jdbc.EmbeddedDriver -init -load seq_gen -load_opts debugging=0,identityTest=1 -gen b2b
          -----------------------------------------------------------------------------------------------------------
          10.9.1.0| 10.8.1.2
          -----------------------------------------------------------------------------------------------------------

          • of transactions | Average Thruput | - of transactions | Average Thruput
            -----------------------------------------------------------------------------------------------------------
            5015 | 83.58333333333333 | 3889 | 64.81666666666666
            -----------------------------------------------------------------------------------------------------------
            4697 | 78.28333333333333 | 4578 | 76.3
            -----------------------------------------------------------------------------------------------------------
            5150 | 85.83333333333333 | 5016 | 83.6
            -----------------------------------------------------------------------------------------------------------

          Sequence test(default behavior)
          java -Dderby.system.home=./systest/db4 org.apache.derbyTesting.perf.clients.Runner -driver org.apache.derby.jdbc.EmbeddedDriver -init -load seq_gen -load_opts debugging=0,identityTest=0 -gen b2b
          -----------------------------------------------------------------------------------------------------------
          10.9.1.0| 10.8.1.2
          -----------------------------------------------------------------------------------------------------------

          • of transactions | Average Thruput | - of transactions | Average Thruput
            -----------------------------------------------------------------------------------------------------------
            5228 | 87.13333333333334 | 4330 | 72.16666666666667
            -----------------------------------------------------------------------------------------------------------
            5206 | 86.76666666666667 | 5026 | 83.76527057882369
            -----------------------------------------------------------------------------------------------------------
            5010 | 83.5 | 4489 | 74.81541974300428
            -----------------------------------------------------------------------------------------------------------

          Identity test(number of threads 1, preallocation of 1, numberOfGenerator to 1, tablesPerGenerator to 1 and insertsPerTransaction to 100)
          java -Dderby.system.home=./systest/db7 -Dderby.language.sequence.preallocator=1 org.apache.derbyTesting.perf.clients.Runner -driver org.apache.derby.jdbc.EmbeddedDriver -init -load seq_gen -load_opts debugging=0,numberOfGenerators=1,tablesPerGenerator=1,insertsPerTransaction=100,identityTest=1 -gen b2b -threads 1
          -----------------------------------------------------------------------------------------------------------
          10.9.1.0| 10.8.1.2
          -----------------------------------------------------------------------------------------------------------

          • of transactions | Average Thruput | - of transactions | Average Thruput
            -----------------------------------------------------------------------------------------------------------
            1537 | 25.616666666666667 | 1086 | 18.099698338361026
            -----------------------------------------------------------------------------------------------------------
            1628 | 27.133333333333333 | 1518 | 25.3
            -----------------------------------------------------------------------------------------------------------
            1514 | 25.230810252308103 | 1472 | 24.533333333333335
            -----------------------------------------------------------------------------------------------------------

          Sequence test(number of threads 1, preallocation of 1, numberOfGenerator to 1, tablesPerGenerator to 1 and insertsPerTransaction to 100)
          java -Dderby.system.home=./systest/db11 -Dderby.language.sequence.preallocator=1 org.apache.derbyTesting.perf.clients.Runner -driver org.apache.derby.jdbc.EmbeddedDriver -init -load seq_gen -load_opts debugging=0,numberOfGenerators=1,tablesPerGenerator=1,insertsPerTransaction=100,identityTest=0 -gen b2b -threads 1
          -----------------------------------------------------------------------------------------------------------
          10.9.1.0| 10.8.1.2
          -----------------------------------------------------------------------------------------------------------

          • of transactions | Average Thruput | - of transactions | Average Thruput
            -----------------------------------------------------------------------------------------------------------
            50 | 0.8333333333333334 | 3054 | 50.9
            -----------------------------------------------------------------------------------------------------------
            47 | 0.7833333333333333 | 3033 | 50.55
            -----------------------------------------------------------------------------------------------------------
            49 | 0.8166666666666667 | 3000 | 50.0
            -----------------------------------------------------------------------------------------------------------

          Average time for each of the tests above
          -----------------------------------------------------------------------------------------------------------
          10.9.1.0| 10.8.1.2
          -----------------------------------------------------------------------------------------------------------
          Test type | # of transactions | Average Thruput | # of transactions | Average Thruput
          -----------------------------------------------------------------------------------------------------------
          Identity default | 5015 | 83.58333333333333 | 4578 | 76.3
          -----------------------------------------------------------------------------------------------------------
          Sequence default | 5206 | 86.76666666666667 | 4489 | 74.81541974300428
          -----------------------------------------------------------------------------------------------------------
          Identity prellocation of 1 | 1537 | 25.616666666666667 | 1472 | 24.533333333333335
          -----------------------------------------------------------------------------------------------------------
          Sequence prellocation of 1 | 49 | 0.8166666666666667 | 3033 | 50.55
          -----------------------------------------------------------------------------------------------------------

          Show
          Mamta A. Satoor added a comment - - edited For both identity column and sequences, I ran two kinds of tests as explained below. I have run these tests 3 times each for 10.9 release(10.9.1.0) candidate and 10.8.1.2 release and have included the results below. The tests were run with write caching disabled. The 2 kinds of tests for sequences and identity columns were as follows 1)default behavior : For the test, when we let it pick the default behavior, number of threads default to 1, preallocate defaults to 100 on 10.9 and 5 on 10.8, numberOfGenerator defaults to 1, tablesPerGenerator defaults to 1 and insertsPerTransaction default to 1 2)In this kind of run, I passed following paramters to the test.- number of threads 1, preallocation of 1, numberOfGenerator to 1, tablesPerGenerator to 1 and insertsPerTransaction to 100 Identity test(default behavior) java -Dderby.system.home=./systest/db1 org.apache.derbyTesting.perf.clients.Runner -driver org.apache.derby.jdbc.EmbeddedDriver -init -load seq_gen -load_opts debugging=0,identityTest=1 -gen b2b ----------------------------------------------------------------------------------------------------------- 10.9.1.0| 10.8.1.2 ----------------------------------------------------------------------------------------------------------- of transactions | Average Thruput | - of transactions | Average Thruput ----------------------------------------------------------------------------------------------------------- 5015 | 83.58333333333333 | 3889 | 64.81666666666666 ----------------------------------------------------------------------------------------------------------- 4697 | 78.28333333333333 | 4578 | 76.3 ----------------------------------------------------------------------------------------------------------- 5150 | 85.83333333333333 | 5016 | 83.6 ----------------------------------------------------------------------------------------------------------- Sequence test(default behavior) java -Dderby.system.home=./systest/db4 org.apache.derbyTesting.perf.clients.Runner -driver org.apache.derby.jdbc.EmbeddedDriver -init -load seq_gen -load_opts debugging=0,identityTest=0 -gen b2b ----------------------------------------------------------------------------------------------------------- 10.9.1.0| 10.8.1.2 ----------------------------------------------------------------------------------------------------------- of transactions | Average Thruput | - of transactions | Average Thruput ----------------------------------------------------------------------------------------------------------- 5228 | 87.13333333333334 | 4330 | 72.16666666666667 ----------------------------------------------------------------------------------------------------------- 5206 | 86.76666666666667 | 5026 | 83.76527057882369 ----------------------------------------------------------------------------------------------------------- 5010 | 83.5 | 4489 | 74.81541974300428 ----------------------------------------------------------------------------------------------------------- Identity test(number of threads 1, preallocation of 1, numberOfGenerator to 1, tablesPerGenerator to 1 and insertsPerTransaction to 100) java -Dderby.system.home=./systest/db7 -Dderby.language.sequence.preallocator=1 org.apache.derbyTesting.perf.clients.Runner -driver org.apache.derby.jdbc.EmbeddedDriver -init -load seq_gen -load_opts debugging=0,numberOfGenerators=1,tablesPerGenerator=1,insertsPerTransaction=100,identityTest=1 -gen b2b -threads 1 ----------------------------------------------------------------------------------------------------------- 10.9.1.0| 10.8.1.2 ----------------------------------------------------------------------------------------------------------- of transactions | Average Thruput | - of transactions | Average Thruput ----------------------------------------------------------------------------------------------------------- 1537 | 25.616666666666667 | 1086 | 18.099698338361026 ----------------------------------------------------------------------------------------------------------- 1628 | 27.133333333333333 | 1518 | 25.3 ----------------------------------------------------------------------------------------------------------- 1514 | 25.230810252308103 | 1472 | 24.533333333333335 ----------------------------------------------------------------------------------------------------------- Sequence test(number of threads 1, preallocation of 1, numberOfGenerator to 1, tablesPerGenerator to 1 and insertsPerTransaction to 100) java -Dderby.system.home=./systest/db11 -Dderby.language.sequence.preallocator=1 org.apache.derbyTesting.perf.clients.Runner -driver org.apache.derby.jdbc.EmbeddedDriver -init -load seq_gen -load_opts debugging=0,numberOfGenerators=1,tablesPerGenerator=1,insertsPerTransaction=100,identityTest=0 -gen b2b -threads 1 ----------------------------------------------------------------------------------------------------------- 10.9.1.0| 10.8.1.2 ----------------------------------------------------------------------------------------------------------- of transactions | Average Thruput | - of transactions | Average Thruput ----------------------------------------------------------------------------------------------------------- 50 | 0.8333333333333334 | 3054 | 50.9 ----------------------------------------------------------------------------------------------------------- 47 | 0.7833333333333333 | 3033 | 50.55 ----------------------------------------------------------------------------------------------------------- 49 | 0.8166666666666667 | 3000 | 50.0 ----------------------------------------------------------------------------------------------------------- Average time for each of the tests above ----------------------------------------------------------------------------------------------------------- 10.9.1.0| 10.8.1.2 ----------------------------------------------------------------------------------------------------------- Test type | # of transactions | Average Thruput | # of transactions | Average Thruput ----------------------------------------------------------------------------------------------------------- Identity default | 5015 | 83.58333333333333 | 4578 | 76.3 ----------------------------------------------------------------------------------------------------------- Sequence default | 5206 | 86.76666666666667 | 4489 | 74.81541974300428 ----------------------------------------------------------------------------------------------------------- Identity prellocation of 1 | 1537 | 25.616666666666667 | 1472 | 24.533333333333335 ----------------------------------------------------------------------------------------------------------- Sequence prellocation of 1 | 49 | 0.8166666666666667 | 3033 | 50.55 -----------------------------------------------------------------------------------------------------------
          Hide
          Rick Hillegas added a comment -

          Thanks for running the latest batch of experiments, Mamta. It is good to see that, on your platform, the 10.9.1 defaults result in slightly better throughput for sequences and identities than the previous 10.8.2 release. Thanks.

          Show
          Rick Hillegas added a comment - Thanks for running the latest batch of experiments, Mamta. It is good to see that, on your platform, the 10.9.1 defaults result in slightly better throughput for sequences and identities than the previous 10.8.2 release. Thanks.
          Hide
          Mamta A. Satoor added a comment -

          It looks like all the results between the 2 releases are very comparable except for sequences when the preallocation is set to 1. On 10.9.1.0, we got a thru put of 0.8166666666666667 where as on 10.8..1.2, we got thru put of 50.55 tx/sec

          Show
          Mamta A. Satoor added a comment - It looks like all the results between the 2 releases are very comparable except for sequences when the preallocation is set to 1. On 10.9.1.0, we got a thru put of 0.8166666666666667 where as on 10.8..1.2, we got thru put of 50.55 tx/sec
          Hide
          Rick Hillegas added a comment -

          Hi Mamta,

          Yes, that lower throughput for sequences when preallocation is set to 1 appears to be the cost of the correctness work done in 10.9. Thanks.

          Show
          Rick Hillegas added a comment - Hi Mamta, Yes, that lower throughput for sequences when preallocation is set to 1 appears to be the cost of the correctness work done in 10.9. Thanks.
          Hide
          Mike Matrigali added a comment -

          The perfomance results posted are along what I expect given the work gone into 10.9. Default behavior seems better or same for both
          identity and sequences.

          The preallocation of 1, single user sequence is the worst case I can think of. In order to insure that a sequence number is not given out
          twice a synchonous commit is done for each allocation, where it was asynchronous in 10.8. Not sure if we should document the
          50 fold slowdown in the worst case. If user does not touch tuning knobs we expect no slow down, and on this platform see a slight
          performance gain.

          Show
          Mike Matrigali added a comment - The perfomance results posted are along what I expect given the work gone into 10.9. Default behavior seems better or same for both identity and sequences. The preallocation of 1, single user sequence is the worst case I can think of. In order to insure that a sequence number is not given out twice a synchonous commit is done for each allocation, where it was asynchronous in 10.8. Not sure if we should document the 50 fold slowdown in the worst case. If user does not touch tuning knobs we expect no slow down, and on this platform see a slight performance gain.
          Hide
          Rick Hillegas added a comment -

          I would be happy to build a new release candidate if we wanted to add a release note explaining how to get better concurrency for sequences by adjusting the value of derby.language.sequence.preallocator.

          Note, however, that the derby.language.sequence.preallocator knob is added by 10.9. It does not appear in 10.8. The knob was added by derby-4437-05-aa-pluggablePreallocation.diff (revision 1138434), ported to 10.8 by revision 1141645, but backed out of 10.8 by derby-5448-01-aa-backoutConcurrencyChanges.diff (revision 1179717).

          So I would not expect that this knob would be set by users upgrading from 10.8. Users upgrading from 10.8 will get the new default preallocation size of 100.

          Mamta and I have run experiments with different settings of derby.language.sequence.preallocator. So far our only evidence is that the concurrency of sequences is better in 10.9 than in 10.8.

          At this point, I don't know what a release note would say other than "We expect that the concurrency of your sequence generators has gone up by upgrading to 10.9. If you want even better concurrency, consider adjusting derby.language.sequence.preallocator."

          Does that advice merit a release note? Is there some other advice we should give in a release note? Does this advice merit a new release candidate?

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - I would be happy to build a new release candidate if we wanted to add a release note explaining how to get better concurrency for sequences by adjusting the value of derby.language.sequence.preallocator. Note, however, that the derby.language.sequence.preallocator knob is added by 10.9. It does not appear in 10.8. The knob was added by derby-4437-05-aa-pluggablePreallocation.diff (revision 1138434), ported to 10.8 by revision 1141645, but backed out of 10.8 by derby-5448-01-aa-backoutConcurrencyChanges.diff (revision 1179717). So I would not expect that this knob would be set by users upgrading from 10.8. Users upgrading from 10.8 will get the new default preallocation size of 100. Mamta and I have run experiments with different settings of derby.language.sequence.preallocator. So far our only evidence is that the concurrency of sequences is better in 10.9 than in 10.8. At this point, I don't know what a release note would say other than "We expect that the concurrency of your sequence generators has gone up by upgrading to 10.9. If you want even better concurrency, consider adjusting derby.language.sequence.preallocator." Does that advice merit a release note? Is there some other advice we should give in a release note? Does this advice merit a new release candidate? Thanks, -Rick
          Hide
          Knut Anders Hatlen added a comment -

          I don't think it merits a new release candidate. We already mention this in the reference manual, and I don't think it's worthwhile spinning a new RC just to copy that information into the release notes.

          Show
          Knut Anders Hatlen added a comment - I don't think it merits a new release candidate. We already mention this in the reference manual, and I don't think it's worthwhile spinning a new RC just to copy that information into the release notes.
          Hide
          Mike Matrigali added a comment -

          I didn't realize that knob is new to 10.9 (at least for customer use). So I think it is less important to note this is a release note vs whatever we have in documentation.

          Just to verify the amount of preallocation is not stored in a catalog per table, correct? A user either hard or soft upgrading a sequence from
          10.8 is still going to see the new default 100 preallocation, correct?

          Show
          Mike Matrigali added a comment - I didn't realize that knob is new to 10.9 (at least for customer use). So I think it is less important to note this is a release note vs whatever we have in documentation. Just to verify the amount of preallocation is not stored in a catalog per table, correct? A user either hard or soft upgrading a sequence from 10.8 is still going to see the new default 100 preallocation, correct?
          Hide
          Rick Hillegas added a comment -

          The preallocation size is not persisted. In 10.9 the value defaults to 100 regardless of whether you hard or soft upgrade.

          Show
          Rick Hillegas added a comment - The preallocation size is not persisted. In 10.9 the value defaults to 100 regardless of whether you hard or soft upgrade.
          Hide
          Kathey Marsden added a comment -

          I think if we made a new release candidate, the performance improvement and relation to derby.language.sequence.preallocator would be worth mentioning in the release note. Also might want to mention that users seeking increased performance for identity columns might want to try implementing as sequences. That way we might encourage users to exercise sequences more in preparation for ultimately transitioning identity columns to use this code. If there is not a new release candidate, a blurb in the announcement might do just as well or even garner more attention.

          Show
          Kathey Marsden added a comment - I think if we made a new release candidate, the performance improvement and relation to derby.language.sequence.preallocator would be worth mentioning in the release note. Also might want to mention that users seeking increased performance for identity columns might want to try implementing as sequences. That way we might encourage users to exercise sequences more in preparation for ultimately transitioning identity columns to use this code. If there is not a new release candidate, a blurb in the announcement might do just as well or even garner more attention.

            People

            • Assignee:
              Mike Matrigali
              Reporter:
              Rick Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development