db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5780) identity column performance has degredated
Date Thu, 24 May 2012 21:56:59 GMT

    [ 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

        

Mime
View raw message