db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Chris Wilson (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4620) Query optimizer causes OOM error on a complex query
Date Mon, 19 Apr 2010 14:36:53 GMT

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


Mime
View raw message