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 "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2009/05/18 12:33:45 UTC
[jira] Created: (DERBY-4233) Lower tablescan performance right
after database creation in 10.5
Lower tablescan performance right after database creation in 10.5
-----------------------------------------------------------------
Key: DERBY-4233
URL: https://issues.apache.org/jira/browse/DERBY-4233
Project: Derby
Issue Type: Bug
Components: Performance, SQL
Affects Versions: 10.5.1.1
Environment: Solaris Express Community Edition snv_114 X86
java version "1.6.0_13"
Java(TM) SE Runtime Environment (build 1.6.0_13-b03)
Java HotSpot(TM) Server VM (build 11.3-b02, mixed mode)
Reporter: Knut Anders Hatlen
I noticed that a table scan test performed significantly worse
(10-20%) in Derby 10.5.1.1 than in Derby 10.4.2.0. I only see this if
the test creates and populates a fresh database. To reproduce, put
derbyTesting.jar from 10.5.1.1 and derby.jar from the release you want
to test in your classpath, and execute the following shell commands:
# make sure the database is removed so that we create a fresh one
test -d db && rm -rf db
# run test for five minutes (+ one minute warm-up)
java -server -Dderby.storage.pageCacheSize=25000 \
org.apache.derbyTesting.perf.clients.Runner \
-load sr_select -load_opts nonIndexed -wt 60 -rt 300 -init
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4233) Lower tablescan performance right
after database creation in 10.5
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4233?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-4233:
----------------------------------
Not sure what is going on here, but wanted to comment on the next+fetch -> fetchNext() change. The fetchNext() interface was specifically added (a long time ago) as a performance enhancement and is definitely preferred whenever it can be substitued for a next + fetch call.
It saves both procedure calls, code execution and number of times latches are required.
If someone wants to do more work on this one, it does seem likely this is some sort of jit issue. So maybe experimenting with setting jvm options that start jit'ing earlier on both before and after change may show up something.
> Lower tablescan performance right after database creation in 10.5
> -----------------------------------------------------------------
>
> Key: DERBY-4233
> URL: https://issues.apache.org/jira/browse/DERBY-4233
> Project: Derby
> Issue Type: Bug
> Components: Performance, SQL
> Affects Versions: 10.5.1.1
> Environment: Solaris Express Community Edition snv_114 X86
> java version "1.6.0_13"
> Java(TM) SE Runtime Environment (build 1.6.0_13-b03)
> Java HotSpot(TM) Server VM (build 11.3-b02, mixed mode)
> Reporter: Knut Anders Hatlen
> Priority: Minor
>
> I noticed that a table scan test performed significantly worse
> (10-20%) in Derby 10.5.1.1 than in Derby 10.4.2.0. I only see this if
> the test creates and populates a fresh database. To reproduce, put
> derbyTesting.jar from 10.5.1.1 and derby.jar from the release you want
> to test in your classpath, and execute the following shell commands:
> # make sure the database is removed so that we create a fresh one
> test -d db && rm -rf db
> # run test for five minutes (+ one minute warm-up)
> java -server -Dderby.storage.pageCacheSize=25000 \
> org.apache.derbyTesting.perf.clients.Runner \
> -load sr_select -load_opts nonIndexed -wt 60 -rt 300 -init
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4233) Lower tablescan performance right
after database creation in 10.5
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4233?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-4233:
---------------------------------
Bug behavior facts: [Performance, Regression] (was: [Regression])
> Lower tablescan performance right after database creation in 10.5
> -----------------------------------------------------------------
>
> Key: DERBY-4233
> URL: https://issues.apache.org/jira/browse/DERBY-4233
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.1
> Environment: Solaris Express Community Edition snv_114 X86
> java version "1.6.0_13"
> Java(TM) SE Runtime Environment (build 1.6.0_13-b03)
> Java HotSpot(TM) Server VM (build 11.3-b02, mixed mode)
> Reporter: Knut Anders Hatlen
> Priority: Minor
>
> I noticed that a table scan test performed significantly worse
> (10-20%) in Derby 10.5.1.1 than in Derby 10.4.2.0. I only see this if
> the test creates and populates a fresh database. To reproduce, put
> derbyTesting.jar from 10.5.1.1 and derby.jar from the release you want
> to test in your classpath, and execute the following shell commands:
> # make sure the database is removed so that we create a fresh one
> test -d db && rm -rf db
> # run test for five minutes (+ one minute warm-up)
> java -server -Dderby.storage.pageCacheSize=25000 \
> org.apache.derbyTesting.perf.clients.Runner \
> -load sr_select -load_opts nonIndexed -wt 60 -rt 300 -init
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4233) Lower tablescan performance right
after database creation in 10.5
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4233?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-4233:
--------------------------------------
Priority: Minor (was: Major)
Lowering the priority since the problem appears to be very limited. The change that triggered the problem looks harmless, so it may be reasonable to suspect that external factors are more likely to be the real cause (for example an instability in the JVM's optimizer and the change just happens to make it pick the suboptimal choice more frequently in this particular situation).
> Lower tablescan performance right after database creation in 10.5
> -----------------------------------------------------------------
>
> Key: DERBY-4233
> URL: https://issues.apache.org/jira/browse/DERBY-4233
> Project: Derby
> Issue Type: Bug
> Components: Performance, SQL
> Affects Versions: 10.5.1.1
> Environment: Solaris Express Community Edition snv_114 X86
> java version "1.6.0_13"
> Java(TM) SE Runtime Environment (build 1.6.0_13-b03)
> Java HotSpot(TM) Server VM (build 11.3-b02, mixed mode)
> Reporter: Knut Anders Hatlen
> Priority: Minor
>
> I noticed that a table scan test performed significantly worse
> (10-20%) in Derby 10.5.1.1 than in Derby 10.4.2.0. I only see this if
> the test creates and populates a fresh database. To reproduce, put
> derbyTesting.jar from 10.5.1.1 and derby.jar from the release you want
> to test in your classpath, and execute the following shell commands:
> # make sure the database is removed so that we create a fresh one
> test -d db && rm -rf db
> # run test for five minutes (+ one minute warm-up)
> java -server -Dderby.storage.pageCacheSize=25000 \
> org.apache.derbyTesting.perf.clients.Runner \
> -load sr_select -load_opts nonIndexed -wt 60 -rt 300 -init
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-4233) Lower tablescan performance right after
database creation in 10.5
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4233?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen closed DERBY-4233.
-------------------------------------
Resolution: Invalid
The code that was changed is not executed during the period when the test counts transactions, and I can't see that it leaves resources open or changes the structure of the database in any way, so I'm closing the issue as invalid under the assumption that it's a jit issue. We can reopen if we see evidence that suggests otherwise.
> Lower tablescan performance right after database creation in 10.5
> -----------------------------------------------------------------
>
> Key: DERBY-4233
> URL: https://issues.apache.org/jira/browse/DERBY-4233
> Project: Derby
> Issue Type: Bug
> Components: Performance, SQL
> Affects Versions: 10.5.1.1
> Environment: Solaris Express Community Edition snv_114 X86
> java version "1.6.0_13"
> Java(TM) SE Runtime Environment (build 1.6.0_13-b03)
> Java HotSpot(TM) Server VM (build 11.3-b02, mixed mode)
> Reporter: Knut Anders Hatlen
> Priority: Minor
>
> I noticed that a table scan test performed significantly worse
> (10-20%) in Derby 10.5.1.1 than in Derby 10.4.2.0. I only see this if
> the test creates and populates a fresh database. To reproduce, put
> derbyTesting.jar from 10.5.1.1 and derby.jar from the release you want
> to test in your classpath, and execute the following shell commands:
> # make sure the database is removed so that we create a fresh one
> test -d db && rm -rf db
> # run test for five minutes (+ one minute warm-up)
> java -server -Dderby.storage.pageCacheSize=25000 \
> org.apache.derbyTesting.perf.clients.Runner \
> -load sr_select -load_opts nonIndexed -wt 60 -rt 300 -init
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4233) Lower tablescan performance right
after database creation in 10.5
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4233?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12710327#action_12710327 ]
Knut Anders Hatlen commented on DERBY-4233:
-------------------------------------------
After a binary search I pinpointed it to this commit:
------------------------------------------------------------------------
r666901 | dag | 2008-06-12 01:41:43 +0200 (Thu, 12 Jun 2008) | 4 lines
DERBY-3678 StackOverflowException in deadlock trace
Patch derby-3678-3 which solves the issue and adds a new regression test.
------------------------------------------------------------------------
The only engine code touched by that commit was in impl.sql.catalog,
which is consistent with the observation that the problem is only
manifested if a fresh database is created.
> Lower tablescan performance right after database creation in 10.5
> -----------------------------------------------------------------
>
> Key: DERBY-4233
> URL: https://issues.apache.org/jira/browse/DERBY-4233
> Project: Derby
> Issue Type: Bug
> Components: Performance, SQL
> Affects Versions: 10.5.1.1
> Environment: Solaris Express Community Edition snv_114 X86
> java version "1.6.0_13"
> Java(TM) SE Runtime Environment (build 1.6.0_13-b03)
> Java HotSpot(TM) Server VM (build 11.3-b02, mixed mode)
> Reporter: Knut Anders Hatlen
>
> I noticed that a table scan test performed significantly worse
> (10-20%) in Derby 10.5.1.1 than in Derby 10.4.2.0. I only see this if
> the test creates and populates a fresh database. To reproduce, put
> derbyTesting.jar from 10.5.1.1 and derby.jar from the release you want
> to test in your classpath, and execute the following shell commands:
> # make sure the database is removed so that we create a fresh one
> test -d db && rm -rf db
> # run test for five minutes (+ one minute warm-up)
> java -server -Dderby.storage.pageCacheSize=25000 \
> org.apache.derbyTesting.perf.clients.Runner \
> -load sr_select -load_opts nonIndexed -wt 60 -rt 300 -init
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4233) Lower tablescan performance right
after database creation in 10.5
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4233?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12712081#action_12712081 ]
Knut Anders Hatlen commented on DERBY-4233:
-------------------------------------------
I haven't been able to find anything in the DERBY-3678 patch that
should be causing a performance regression, but I have managed to
identify which part of the patch that changes the performance. If the
call to ScanController.fetchNext() in
DataDictionaryImpl.getDescriptorViaIndexMinion() is changed back to
ScanController.next() + ScanController.fetch(), the performance goes
back to normal. I have no idea why, as the work performed by the two
is almost identical.
Some more observations: The throughput seems to vary between two
levels. In general, it tends to be at the higher level when running
without the 3678 patch and at the lower level when running with
it. But in some cases a run with the 3678 patch may have throughput at
the higher level, and vice versa without the patch.
Also, if the test reuses the database and the tables instead of
creating new ones, I see that the throughput is at an even higher
level, and the throughput is the same with and without the 3678
patch. This kind of makes sense, since the JVM uses statistics to
perform optimization, and it's more likely to make good decisions for
select statements if the statistics haven't been polluted by the
initial inserts.
> Lower tablescan performance right after database creation in 10.5
> -----------------------------------------------------------------
>
> Key: DERBY-4233
> URL: https://issues.apache.org/jira/browse/DERBY-4233
> Project: Derby
> Issue Type: Bug
> Components: Performance, SQL
> Affects Versions: 10.5.1.1
> Environment: Solaris Express Community Edition snv_114 X86
> java version "1.6.0_13"
> Java(TM) SE Runtime Environment (build 1.6.0_13-b03)
> Java HotSpot(TM) Server VM (build 11.3-b02, mixed mode)
> Reporter: Knut Anders Hatlen
>
> I noticed that a table scan test performed significantly worse
> (10-20%) in Derby 10.5.1.1 than in Derby 10.4.2.0. I only see this if
> the test creates and populates a fresh database. To reproduce, put
> derbyTesting.jar from 10.5.1.1 and derby.jar from the release you want
> to test in your classpath, and execute the following shell commands:
> # make sure the database is removed so that we create a fresh one
> test -d db && rm -rf db
> # run test for five minutes (+ one minute warm-up)
> java -server -Dderby.storage.pageCacheSize=25000 \
> org.apache.derbyTesting.perf.clients.Runner \
> -load sr_select -load_opts nonIndexed -wt 60 -rt 300 -init
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.