You are viewing a plain text version of this content. The canonical link for it is here.

Posted to derby-dev@db.apache.org by "Mike Matrigali (JIRA)" <ji...@apache.org> on 2012/05/23 23:27:40 UTC

[jira] [Created] (DERBY-5780) identity column performance has degredated

Mike Matrigali created DERBY-5780:
-------------------------------------

             Summary: 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


Mamta has run some performance tests on windows, with write cache disabled 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

[jira] [Commented] (DERBY-5780) identity column performance has degredated

Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.

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

Mamta A. Satoor commented on DERBY-5780:
----------------------------------------

I have done identity columns testing with write caching disabled on the device on 10.8.2.2 released jars and top of the trunk insane jars and here are the results. Next, I plan to do the similar testing for sequences.

********************
Identity column 10 threads with 100 preallocation

10.8 
Test run 1)
Number of transactions: 1676
Average throughput (tx/s):      27.932867785536907
2505 successful transactions, including warmup phase.
Test run 2)
Number of transactions: 1333
Average throughput (tx/s):      22.21444522214445
2077 successful transactions, including warmup phase.
Test run 3)
Number of transactions: 1576
Average throughput (tx/s):      26.225579924784505
2345 successful transactions, including warmup phase.


Trunk
Test run 1)
Number of transactions: 230
Average throughput (tx/s):      3.8333333333333335
351 successful transactions, including warmup phase.
Test run 2)
Number of transactions: 255
Average throughput (tx/s):      4.25
393 successful transactions, including warmup phase.
Test run 3)
Number of transactions: 242
Average throughput (tx/s):      4.033266112231463
360 successful transactions, including warmup phase.
********************


********************
Identity column 10 threads with 5 preallocation

10.8
Test run 1)
Number of transactions: 1559
Average throughput (tx/s):      25.983333333333334
2387 successful transactions, including warmup phase.
Test run 2)
Number of transactions: 1469
Average throughput (tx/s):      24.48292528457859
2299 successful transactions, including warmup phase.
Test run 3)
Number of transactions: 1397
Average throughput (tx/s):      23.283333333333335
2143 successful transactions, including warmup phase.


Trunk
Test run 1)
Number of transactions: 251
Average throughput (tx/s):      4.18110341151386
385 successful transactions, including warmup phase.
Test run 2)
Number of transactions: 241
Average throughput (tx/s):      4.0121863918624205
368 successful transactions, including warmup phase.
Test run 3)
Number of transactions: 248
Average throughput (tx/s):      4.133333333333334
387 successful transactions, including warmup phase.
********************



********************
Identity column 1 threads with 100 preallocation

10.8
Test run 1)
Number of transactions: 1493
Average throughput (tx/s):      24.88208922887189
2378 successful transactions, including warmup phase.
Test run 2)
Number of transactions: 1657
Average throughput (tx/s):      27.616666666666667
2459 successful transactions, including warmup phase.
Test run 3)
Number of transactions: 1599
Average throughput (tx/s):      26.65
2527 successful transactions, including warmup phase.


Trunk
Test run 1)
Number of transactions: 54
Average throughput (tx/s):      0.9
81 successful transactions, including warmup phase.
Test run 2)
Number of transactions: 44
Average throughput (tx/s):      0.7333211113148114
68 successful transactions, including warmup phase.
Test run 3)
Number of transactions: 52
Average throughput (tx/s):      0.8666522224629589
77 successful transactions, including warmup phase.
********************




********************
Identity column 1 threads with 5 preallocation

10.8
Test run 1)
Number of transactions: 1740
Average throughput (tx/s):      28.966206092891625
2492 successful transactions, including warmup phase.
Test run 2)
Number of transactions: 1203
Average throughput (tx/s):      20.049665838902683
2026 successful transactions, including warmup phase.
Test run 3)
Number of transactions: 1599
Average throughput (tx/s):      26.65
2302 successful transactions, including warmup phase.




Trunk
Test run 1)
Number of transactions: 53
Average throughput (tx/s):      0.8833333333333333
78 successful transactions, including warmup phase.
Test run 2)
Number of transactions: 51
Average throughput (tx/s):      0.8499858335694405
75 successful transactions, including warmup phase.
Test run 3)
Number of transactions: 46
Average throughput (tx/s):      0.7666666666666667
73 successful transactions, including warmup phase.
********************

                
> 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
>
> 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

[jira] [Updated] (DERBY-5780) identity column performance has degredated

Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.

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

Mamta A. Satoor updated DERBY-5780:
-----------------------------------

    Description: 
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.


  was:
Mamta has run some performance tests on windows, with write cache disabled 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.


    
> 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
>
> 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

[jira] [Updated] (DERBY-5780) identity column performance has degredated

Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.

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

Knut Anders Hatlen updated DERBY-5780:
--------------------------------------

    Fix Version/s:     (was: 10.9.1.1)
                   10.9.1.0

Changing fix version to make it clear that the fix went into the first release candidate of 10.9.1.
                
> 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.0, 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

[jira] [Commented] (DERBY-5780) identity column performance has degredated

Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.

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

Rick Hillegas commented on DERBY-5780:
--------------------------------------

I am in the middle of building the first 10.9 release candidate and this issue just turned up with priority Blocker. Since there is some confusion about the difference between priority and urgency, I need to ask this question: Are you flagging this issue as an issue which should block the 10.9 release until we understand the results you are seeing? Thanks.
                
> 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
>
> Mamta has run some performance tests on windows, with write cache disabled 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

[jira] [Commented] (DERBY-5780) identity column performance has degredated

Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.

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

Mamta A. Satoor commented on DERBY-5780:
----------------------------------------

Just wanted to post some more test results for identity columns.

I am testing identity columns with preallocation of 5 and with one thread as shown below
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

The result ot this test on 10.8.2.2
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.

The results at the top of trunk
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.

The results at the revision(1327218) where changes were committed for DERBY-5494
Test run 1)
Number of transactions: 606
Average throughput (tx/s):      10.099831669472175
921 successful transactions, including warmup phase.
Test run 2)
Number of transactions: 603
Average throughput (tx/s):      10.04983250279162
909 successful transactions, including warmup phase.
Test run 3)
Number of transactions: 563
Average throughput (tx/s):      9.383333333333333
882 successful transactions, including warmup phase.

The results right before changes were committed for DERBY-5494. The revision number I believe is 1327050
Test run 1)
Number of transactions: 5913
Average throughput (tx/s):      98.32058530096441
8666 successful transactions, including warmup phase.
Test run 2)
Number of transactions: 6290
Average throughput (tx/s):      104.831586140231
9305 successful transactions, including warmup phase.
Test run 3)
Number of transactions: 6676
Average throughput (tx/s):      111.26666666666667
9872 successful transactions, including warmup phase.

Atleast from this set of tests, it looks like performance degraded after the checkin for DERBY-5494 was made.
                
> 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
>
> Mamta has run some performance tests on windows, with write cache disabled 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

[jira] [Commented] (DERBY-5780) identity column performance has degredated

Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.

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

Mamta A. Satoor commented on DERBY-5780:
----------------------------------------

Jira was down so I couldn't post the results for sequeces earlier.

Here are the results for sequences testing with write caching disabled on the device on 10.8.2.2 released jars and top of the trunk insane jars.


********************
Sequence column 10 threads with 100 preallocation

java -Dderby.system.home=$derbyHome -Dderby.language.sequence.preallocator=100 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
Test run 1)
Number of transactions: 3682
Average throughput (tx/s):      61.3656439059349
5299 successful transactions, including warmup phase.
Test run 2)
Number of transactions: 3765
Average throughput (tx/s):      62.74895418409693
6020 successful transactions, including warmup phase.
Test run 3)
Number of transactions: 3671
Average throughput (tx/s):      61.1823136281062
5641 successful transactions, including warmup phase.


Trunk
Test run 1)
Number of transactions: 3608
Average throughput (tx/s):      60.13333333333333
5552 successful transactions, including warmup phase.
Test run 2)
Number of transactions: 3702
Average throughput (tx/s):      61.7
5610 successful transactions, including warmup phase.
Test run 3)
Number of transactions: 3988
Average throughput (tx/s):      66.46666666666667
5914 successful transactions, including warmup phase.
********************




********************
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
Test run 1)
Number of transactions: 4192
Average throughput (tx/s):      69.86666666666666
6293 successful transactions, including warmup phase.
Test run 2)
Number of transactions: 3927
Average throughput (tx/s):      65.44890918484693
6178 successful transactions, including warmup phase.
Test run 3)
Number of transactions: 3588
Average throughput (tx/s):      59.8
5418 successful transactions, including warmup phase.



Trunk
Test run 1)
Number of transactions: 442
Average throughput (tx/s):      7.3665438909351515
688 successful transactions, including warmup phase.
Test run 2)
Number of transactions: 434
Average throughput (tx/s):      7.233333333333333
674 successful transactions, including warmup phase.
Test run 3)
Number of transactions: 406
Average throughput (tx/s):      6.766666666666667
605 successful transactions, including warmup phase.
********************




********************
Sequence column 1 threads with 100 preallocation

java -Dderby.system.home=$derbyHome -Dderby.language.sequence.preallocator=100 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
Test run 1)
Number of transactions: 1475
Average throughput (tx/s):      24.583333333333332
2687 successful transactions, including warmup phase.
Test run 2)
Number of transactions: 2287
Average throughput (tx/s):      38.116031399476675
3546 successful transactions, including warmup phase.
Test run 3)
Number of transactions: 2488
Average throughput (tx/s):      41.46666666666667
3732 successful transactions, including warmup phase.


Trunk
Test run 1)
Number of transactions: 1797
Average throughput (tx/s):      29.95
2652 successful transactions, including warmup phase.
Test run 2)
Number of transactions: 2059
Average throughput (tx/s):      34.31666666666667
3041 successful transactions, including warmup phase.
Test run 3)
Number of transactions: 1907
Average throughput (tx/s):      31.782803619939667
2696 successful transactions, including warmup phase.
********************




********************
Sequence column 1 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 1


10.8
Test run 1)
Number of transactions: 2936
Average throughput (tx/s):      48.93333333333333
4275 successful transactions, including warmup phase.
Test run 2)
Number of transactions: 2548
Average throughput (tx/s):      42.46666666666667
3726 successful transactions, including warmup phase.
Test run 3)
Number of transactions: 2812
Average throughput (tx/s):      46.86588556857386
4026 successful transactions, including warmup phase.


Trunk
Test run 1)
Number of transactions: 222
Average throughput (tx/s):      3.699938334361094
343 successful transactions, including warmup phase.
Test run 2)
Number of transactions: 220
Average throughput (tx/s):      3.666605556574057
328 successful transactions, including warmup phase.
Test run 3)
Number of transactions: 214
Average throughput (tx/s):      3.566488342249554
314 successful transactions, including warmup phase.
********************




                
> 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
>
> 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

[jira] [Resolved] (DERBY-5780) identity column performance has degredated

Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.

     [ 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