[
https://issues.apache.org/jira/browse/DERBY-4620?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12858504#action_12858504
]
Chris Wilson commented on DERBY-4620:
-------------------------------------
Thanks Bryan! This does indeed look like a very useful tool. I use Eclipse
anyway so I just integrated it.
It looks like you were bang on about the hash joins too. The tool says:
One instance of "org.apache.derby.impl.sql.execute.GroupedAggregateResultSet"
loaded by "sun.misc.Launcher$AppClassLoader @ 0x8bcf0c18" occupies 56,391,208
(84.51%) bytes. The memory is accumulated in one instance of
"org.apache.derby.impl.sql.execute.HashJoinResultSet" loaded by
"sun.misc.Launcher$AppClassLoader @ 0x8bcf0c18".Keywords
org.apache.derby.impl.sql.execute.HashJoinResultSet
org.apache.derby.impl.sql.execute.GroupedAggregateResultSet
sun.misc.Launcher$AppClassLoader @ 0x8bcf0c18
Shortest Paths To the Accumulation Point
Class Name Shallow Heap Retained Heap
* org.apache.derby.impl.sql.execute.HashJoinResultSet @ 0x8c340338
224 37,137,944
\
* leftResultSet org.apache.derby.impl.sql.execute.HashJoinResultSet @
0x8c340418
224 38,803,032
.\
* leftResultSet org.apache.derby.impl.sql.execute.HashJoinResultSet @
0x8c3404f8
224 42,779,744
..\
* leftResultSet org.apache.derby.impl.sql.execute.HashJoinResultSet @
0x8c3405d8
224 49,585,056
...\
* leftResultSet org.apache.derby.impl.sql.execute.HashJoinResultSet @
0x8c3406b8
224 56,390,424
....\
* source org.apache.derby.impl.sql.execute.ProjectRestrictResultSet @
0x8c340798
200 56,390,624
.....\
* originalSource, source
org.apache.derby.impl.sql.execute.GroupedAggregateResultSet @ 0x8c340860
240 56,391,208
......+
* <Java Local> java.lang.Thread @ 0x8bcf0e18 main Thread
104 116,856
......+
* source org.apache.derby.impl.sql.execute.ProjectRestrictResultSet @
0x8c340950 ยป
200 200
......\
* Total: 2 entries
> Query optimizer causes OOM error on a complex query
> ---------------------------------------------------
>
> Key: DERBY-4620
> URL: https://issues.apache.org/jira/browse/DERBY-4620
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.3.0
> Environment: java version "1.6.0_17"
> Java(TM) SE Runtime Environment (build 1.6.0_17-b04)
> Java HotSpot(TM) Client VM (build 14.3-b01, mixed mode, sharing)
> Linux rocio.xxx 2.6.24-27-generic #1 SMP Fri Mar 12 01:10:31 UTC 2010 i686
> GNU/Linux
> Reporter: Chris Wilson
> Attachments: query-plan.txt
>
>
> I have a query that generates about 2,000 rows in a summary table. It runs
> fast enough (~30 seconds) on MySQL. The same query on Derby runs for about 10
> minutes and then fails with an OutOfMemoryError.
> I have created a test case to reproduce the problem. It's not minimal because
> it relies on a rather large dataset to reproduce it, and it's not trivial,
> but I don't mind doing a bit of work trimming it if someone can point me in
> the necessary direction.
> You can check out the test case from our Subversion server here:
> http://rita.wfplogistics.org/svn/trackrita/rita/doc/derby-oom-slow-query
> which includes a pre-built Derby database in "testdb.derby". If this database
> is deleted, "test.sh" will recreate it, but that takes about 10-15 minutes.
> Just modify the script "test.sh" to point to your Derby libraries, and run it
> (or just execute the commands in "movement_complete.sql") to demonstrate the
> problem. You can view the source of that file online here:
> http://rita.wfplogistics.org/trac/browser/rita/conf/movement_complete.sql
> The first "insert into movement_complete" (starting around line 4) takes
> about 15 seconds to complete and inserts 5890 rows. The second, starting
> around line 54, does not complete in reasonable time on Derby. On MySQL, it
> runs in 28 seconds and inserts 2038 rows. On Derby, after 10 minutes I get:
> JAVA ERROR: java.lang.OutOfMemoryError: Java heap space
> ij> ERROR X0Y67: Cannot issue rollback in a nested connection when there
> is a pending operation in the parent connection.
> (process exits)
> It does not output the query plan in this case.
> Following the suggestion of Bryan Pendleton, I tried increasing the JVM
> memory limit from the default to 1024m, and this allows the query to finish
> executing quite quickly. I guess that means that the optimiser is just taking
> a lot of memory to
> optimise the query, and it spends forever in GC before finally hitting OOM
> and giving up when using the default settings.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
