[
https://issues.apache.org/jira/browse/DERBY-5780?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mike Matrigali resolved DERBY-5780.
-----------------------------------
Resolution: Fixed
Fix Version/s: 10.10.0.0
10.9.1.1
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.
> identity column performance has degredated
> ------------------------------------------
>
> Key: DERBY-5780
> URL: https://issues.apache.org/jira/browse/DERBY-5780
> Project: Derby
> Issue Type: Bug
> Components: Store
> Affects Versions: 10.10.0.0
> Reporter: Mike Matrigali
> Assignee: Mike Matrigali
> Priority: Blocker
> Labels: derby_backport_reject_10_8
> Fix For: 10.9.1.1, 10.10.0.0
>
>
> Mamta has run some performance tests on windows, with (Enable write caching
> on the device AND Turn off Windows write-cache buffer flushing on the device)
> that shows
> performance degredation of identity columns. See results posted in
> DERBY-5471, copied here:
> Rick, first of all, I want to apologize for not responding to this jira
> sooner. I allowed other jiras to prioritize over this but I finally got
> around to running a long set of tests and here are the results of them. I
> have truned off debugging prints in these test runs.
> I ran lots of test as explained below on released 10.8.2.2 jars and top of
> the trunk(revision 1341151) insane jars with write caching off and the test
> results between the 2 set of jars do vary. I have Windows7 service pack1
> laptop(Intel(R) Core(TM) i7-2720QM CPU @2.20GHz, 8GB RAM) with IBM jdk 1.6
> I chose to run with preallocation sizes of 5 and 20 because I think the
> default preallocation for 10.8 is 5 and for trunk is 20.
> Variations of test(with 3 test runs of each on 10.8 and trunk jars)
> 1)Identity column 10 threads with 20 preallocation
> java -Dderby.system.home=$derbyHome -Dderby.language.sequence.preallocator=20
> org.apache.derbyTesting.perf.clients.Runner -driver
> org.apache.derby.jdbc.EmbeddedDriver -init -load seq_gen -load_opts
> debugging=0,numberOfGenerators=5,tablesPerGenerator=10,insertsPerTransaction=100,identityTest=1
> -gen b2b -threads 10
> 10.8 Runs
> Test run 1)
> Number of transactions: 10630
> Average throughput (tx/s): 177.16666666666666
> 15427 successful transactions, including warmup phase.
> Test run 2)
> Number of transactions: 10432
> Average throughput (tx/s): 173.86376893718437
> 15282 successful transactions, including warmup phase.
> Test run 3)
> Number of transactions: 10377
> Average throughput (tx/s): 172.89524983755143
> 14908 successful transactions, including warmup phase.
> Trunk Runs
> Test run 1)
> Number of transactions: 3002
> Average throughput (tx/s): 50.021661612290465
> 4542 successful transactions, including warmup phase.
> Test run 2)
> Number of transactions: 2924
> Average throughput (tx/s): 48.733333333333334
> 4370 successful transactions, including warmup phase.
> Test run 3)
> Number of transactions: 2547
> Average throughput (tx/s): 42.45
> 4023 successful transactions, including warmup phase.
> 2)Identity column 10 threads with 5 preallocation
> java -Dderby.system.home=$derbyHome -Dderby.language.sequence.preallocator=5
> org.apache.derbyTesting.perf.clients.Runner -driver
> org.apache.derby.jdbc.EmbeddedDriver -init -load seq_gen -load_opts
> debugging=0,numberOfGenerators=5,tablesPerGenerator=10,insertsPerTransaction=100,identityTest=1
> -gen b2b -threads 10
> 10.8 Runs
> Test run 1)
> Number of transactions: 10677
> Average throughput (tx/s): 177.92627649646712
> 15222 successful transactions, including warmup phase.
> Test run 2)
> Number of transactions: 11188
> Average throughput (tx/s): 186.46355894068432
> 15744 successful transactions, including warmup phase.
> Test run 3)
> Number of transactions: 10571
> Average throughput (tx/s): 176.18039699338345
> 15012 successful transactions, including warmup phase.
> Trunk Runs
> Test run 1)
> Number of transactions: 2453
> Average throughput (tx/s): 40.88333333333333
> 3716 successful transactions, including warmup phase.
> Test run 2)
> Number of transactions: 2531
> Average throughput (tx/s): 42.18333333333333
> 3668 successful transactions, including warmup phase.
> Test run 3)
> Number of transactions: 3076
> Average throughput (tx/s): 51.266666666666666
> 4615 successful transactions, including warmup phase.
> 3)Identity column 1 thread with 20 preallocation
> java -Dderby.system.home=$derbyHome -Dderby.language.sequence.preallocator=20
> org.apache.derbyTesting.perf.clients.Runner -driver
> org.apache.derby.jdbc.EmbeddedDriver -init -load seq_gen -load_opts
> debugging=0,numberOfGenerators=5,tablesPerGenerator=10,insertsPerTransaction=100,identityTest=1
> -gen b2b -threads 1
> 10.8 Runs
> Test run 1)
> Number of transactions: 5865
> Average throughput (tx/s): 97.75
> 8773 successful transactions, including warmup phase.
> Test run 2)
> Number of transactions: 5753
> Average throughput (tx/s): 95.88173530441159
> 8359 successful transactions, including warmup phase.
> Test run 3)
> Number of transactions: 6326
> Average throughput (tx/s): 105.43333333333334
> 9231 successful transactions, including warmup phase.
> Trunk Runs
> Test run 1)
> Number of transactions: 526
> Average throughput (tx/s): 8.766666666666667
> 801 successful transactions, including warmup phase.
> Test run 2)
> Number of transactions: 546
> Average throughput (tx/s): 9.1
> 831 successful transactions, including warmup phase.
> Test run 3)
> Number of transactions: 572
> Average throughput (tx/s): 9.533174447092549
> 841 successful transactions, including warmup phase.
> 4)Identity column 1 thread with 5 preallocation
> java -Dderby.system.home=$derbyHome -Dderby.language.sequence.preallocator=5
> org.apache.derbyTesting.perf.clients.Runner -driver
> org.apache.derby.jdbc.EmbeddedDriver -init -load seq_gen -load_opts
> debugging=0,numberOfGenerators=5,tablesPerGenerator=10,insertsPerTransaction=100,identityTest=1
> -gen b2b -threads 1
> 10.8 Runs
> Test run 1)
> Number of transactions: 5948
> Average throughput (tx/s): 99.13333333333334
> 8799 successful transactions, including warmup phase.
> Test run 2)
> Number of transactions: 5658
> Average throughput (tx/s): 94.3
> 8337 successful transactions, including warmup phase.
> Test run 3)
> Number of transactions: 6314
> Average throughput (tx/s): 105.21755070072822
> 9197 successful transactions, including warmup phase.
> Trunk Runs
> Test run 1)
> Number of transactions: 470
> Average throughput (tx/s): 7.833202779953668
> 737 successful transactions, including warmup phase.
> Test run 2)
> Number of transactions: 548
> Average throughput (tx/s): 9.133333333333333
> 814 successful transactions, including warmup phase.
> Test run 3)
> Number of transactions: 559
> Average throughput (tx/s): 9.315424610051993
> 851 successful transactions, including warmup phase.
> 5)Sequence column 10 threads with 20 preallocation
> java -Dderby.system.home=$derbyHome -Dderby.language.sequence.preallocator=20
> org.apache.derbyTesting.perf.clients.Runner -driver
> org.apache.derby.jdbc.EmbeddedDriver -init -load seq_gen -load_opts
> debugging=0,numberOfGenerators=5,tablesPerGenerator=10,insertsPerTransaction=100,identityTest=0
> -gen b2b -threads 10
> 10.8 Runs
> Test run 1)
> Number of transactions: 20978
> Average throughput (tx/s): 349.6275062082299
> 31469 successful transactions, including warmup phase.
> Test run 2)
> Number of transactions: 20496
> Average throughput (tx/s): 341.6
> 30196 successful transactions, including warmup phase.
> Test run 3)
> Number of transactions: 17433
> Average throughput (tx/s): 290.545157580707
> 24772 successful transactions, including warmup phase.
> Trunk Runs
> Test run 1)
> Number of transactions: 470
> Average throughput (tx/s): 7.833202779953668
> 737 successful transactions, including warmup phase.
> Test run 2)
> Number of transactions: 548
> Average throughput (tx/s): 9.133333333333333
> 814 successful transactions, including warmup phase.
> Test run 3)
> Number of transactions: 559
> Average throughput (tx/s): 9.315424610051993
> 851 successful transactions, including warmup phase.
> 6)Sequence column 10 threads with 5 preallocation
> java -Dderby.system.home=$derbyHome -Dderby.language.sequence.preallocator=5
> org.apache.derbyTesting.perf.clients.Runner -driver
> org.apache.derby.jdbc.EmbeddedDriver -init -load seq_gen -load_opts
> debugging=0,numberOfGenerators=5,tablesPerGenerator=10,insertsPerTransaction=100,identityTest=0
> -gen b2b -threads 10
> 10.8 Runs
> Test run 1)
> Number of transactions: 19927
> Average throughput (tx/s): 332.11113148114197
> 30250 successful transactions, including warmup phase.
> Test run 2)
> Number of transactions: 21048
> Average throughput (tx/s): 350.8
> 31331 successful transactions, including warmup phase.
> Test run 3)
> Number of transactions: 20707
> Average throughput (tx/s): 345.1166666666667
> 30251 successful transactions, including warmup phase.
> Trunk Runs
> Test run 1)
> Number of transactions: 7038
> Average throughput (tx/s): 117.29804503258279
> 13041 successful transactions, including warmup phase.
> Test run 2)
> Number of transactions: 10129
> Average throughput (tx/s): 168.8138531024483
> 15210 successful transactions, including warmup phase.
> Test run 3)
> Number of transactions: 13775
> Average throughput (tx/s): 229.58333333333334
> 19609 successful transactions, including warmup phase.
> 7)Sequence column 1 thread with 20 preallocation
> java -Dderby.system.home=$derbyHome -Dderby.language.sequence.preallocator=20
> org.apache.derbyTesting.perf.clients.Runner -driver
> org.apache.derby.jdbc.EmbeddedDriver -init -load seq_gen -load_opts
> debugging=0,numberOfGenerators=5,tablesPerGenerator=10,insertsPerTransaction=100,identityTest=0
> -gen b2b -threads 1
> 10.8 Runs
> Test run 1)
> Number of transactions: 12989
> Average throughput (tx/s): 216.47972533791105
> 19048 successful transactions, including warmup phase.
> Test run 2)
> Number of transactions: 12521
> Average throughput (tx/s): 208.68333333333334
> 18560 successful transactions, including warmup phase.
> Test run 3)
> Number of transactions: 11970
> Average throughput (tx/s): 199.5
> 17763 successful transactions, including warmup phase.
> Trunk Runs
> Test run 1)
> Number of transactions: 6853
> Average throughput (tx/s): 114.21476308728188
> 10286 successful transactions, including warmup phase.
> Test run 2)
> Number of transactions: 7277
> Average throughput (tx/s): 121.2813119781337
> 10733 successful transactions, including warmup phase.
> Test run 3)
> Number of transactions: 7293
> Average throughput (tx/s): 121.55
> 10772 successful transactions, including warmup phase.
> 8)Sequence column 1 thread with 5 preallocation
> java -Dderby.system.home=$derbyHome -Dderby.language.sequence.preallocator=5
> org.apache.derbyTesting.perf.clients.Runner -driver
> org.apache.derby.jdbc.EmbeddedDriver -init -load seq_gen -load_opts
> debugging=0,numberOfGenerators=5,tablesPerGenerator=10,insertsPerTransaction=100,identityTest=0
> -gen b2b -threads 1
> 10.8 Runs
> Test run 1)
> Number of transactions: 12059
> Average throughput (tx/s): 200.93979637745156
> 17215 successful transactions, including warmup phase.
> Test run 2)
> Number of transactions: 11071
> Average throughput (tx/s): 184.51359144014268
> 16884 successful transactions, including warmup phase.
> Test run 3)
> Number of transactions: 12073
> Average throughput (tx/s): 201.21331311144814
> 17489 successful transactions, including warmup phase.
> Trunk Runs
> Test run 1)
> Number of transactions: 2479
> Average throughput (tx/s): 41.31597806703222
> 3673 successful transactions, including warmup phase.
> Test run 2)
> Number of transactions: 2657
> Average throughput (tx/s): 44.2825952900785
> 3921 successful transactions, including warmup phase.
> Test run 3)
> Number of transactions: 2518
> Average throughput (tx/s): 41.96596723387943
> 3698 successful transactions, including warmup phase.
>
> > Stress test for identity columns and sequence seem to be taking longer on
> > trunk compared to 10.8.2.2 RC3
> > --------------------------------------------------------------------------------------------------------
> >
> > Key: DERBY-5471
> > URL: https://issues.apache.org/jira/browse/DERBY-5471
> > Project: Derby
> > Issue Type: Task
> > Components: Test
> > Affects Versions: 10.9.1.0
> > Environment: Windows XP version 2.18
> > Genuine Intel(R) CPU T2600 dual core @2.16GHz 2.00GB of RAM
> > $ java -version
> > java version "1.6.0"
> > Java(TM) SE Runtime Environment (build pwi3260sr9fp1-20110208_03(SR9 FP1))
> > IBM J9 VM (build 2.4, JRE 1.6.0 IBM J9 2.4 Windows XP x86-32
> > jvmwi3260sr9-201102
> > 03_74623 (JIT enabled, AOT enabled)
> > J9VM - 20110203_074623
> > JIT - r9_20101028_17488ifx3
> > GC - 20101027_AA)
> > JCL - 20110203_01
> > Reporter: Mamta A. Satoor
> > Attachments: 5471-performance.html
> >
> >
> > I have been trying to run org.apache.derbyTesting.perf.clients.Runner
> > (which provides ways to stress test sequence generator and identity
> > columns) on trunk and 10.8.2.2 RC3 to compare the performance and I find
> > that it takes almost double the time for the tests to finish on trunk.
> > Additionally, the identity column test consistently ran into lock timeouts
> > on trunk. I am running with insane jars on trunk and 10.8.2.2 RC3. The test
> > in question is not in official jars for the release candidate so I manually
> > copied them to 10.8.2.2 RC3 environment during my test(basically copied the
> > entire org.apache.derbyTesting.perf.clients.Runner directory from trunk to
> > 10.8.2.2 RC3 environment).
> > Command to do sequence stress testing is as follows
> > java org.apache.derbyTesting.perf.clients.Runner -driver
> > org.apache.derby.jdbc.EmbeddedDriver -init -load seq_gen -load_opts
> > debugging=1,numberOfGenerators=5,tablesPerGenerator=10,insertsPerTransaction=100,identityTest=0
> > -gen b2b -threads 10
> > Command to do identity column stress testing is as follows
> > time java org.apache.derbyTesting.perf.clients.Runner -driver
> > org.apache.derby.jdbc.EmbeddedDriver -init -load seq_gen -load_opts
> > debugging=1,numberOfGenerators=5,tablesPerGenerator=10,insertsPerTransaction=100,identityTest=1
> > -gen b2b -threads 10
> > An average run on 10.8.2.2 RC3 for sequence stress test is about a minute
> > and 46 secs. On trunk, on an average it takes 2 and half minutes
> > An average run on 10.8.2.2 RC3 for identity stress test is about a minute
> > and 50 secs. On trunk, on an average it takes 3minsutes and 30 secs. Also,
> > on trunk, this test runs into lock timeouts.
> > I was wondering if this is the right behavior. The performance should be
> > better in trunk because of pre-allocation of range for sequences and
> > identity columns(which defaults to 20) but unless I have missed something
> > in my tests, the results don't show the performance improvement.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
