Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <18508445.13021271688711248.JavaMail.jira@thor>
Date: Mon, 19 Apr 2010 10:51:51 -0400 (EDT)
From: "Kristian Waagan (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-4620) Query optimizer causes OOM error on a
 complex query
In-Reply-To: <25672450.32181271596825647.JavaMail.jira@thor>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

Kristian Waagan updated DERBY-4620:
-----------------------------------

    Comment: was deleted

(was: From the HPROF dump you referenced, I get the (partial) histogram below. Doesn't seem too bad to me.
Two questions:
 o What was the max heap size set to when you got this OOME?
 o What was the Derby page cache size set to? (looks like maybe the default of 1000?)

If this is a JVM bug, it will be hard to - and maybe not predictable - to make a work-around for it in Derby.


Instance Count    Size (KB) Class
233'173               7'286 class org.apache.derby.iapi.types.SQLVarchar
100'376               7'018 class [Lorg.apache.derby.iapi.types.DataValueDescriptor;
  5'276               5'533 class [B
365'401               4'282 class [[C
118'855               3'714 class org.apache.derby.iapi.types.SQLChar
705'875               3'446 class org.apache.derby.iapi.types.SQLInteger
158'660               1'859 class org.apache.derby.iapi.types.SQLDecimal
 85'835               1'437 class [Ljava.lang.Object;
 70'744               1'105 class java.util.HashMap$Entry
 50'995                 957 class [C
 84'979                 746 class org.apache.derby.iapi.types.SQLLongint
 40'015                 664 class org.apache.derby.impl.store.raw.data.StoredRecordHeader
216'015                 632 class org.apache.derby.iapi.types.SQLSmallint
    307                 600 class [Ljava.util.HashMap$Entry;
 38'259                 597 class org.apache.derby.iapi.types.SQLTimestamp
 13'270                 518 class org.apache.derby.iapi.types.SQLClob
 40'018                 468 class org.apache.derby.impl.store.raw.data.RecordId
 70'179                 274 class org.apache.derby.iapi.store.access.KeyHasher
 13'298                 207 class org.apache.derby.impl.store.access.heap.HeapRowLocation
  1'000                 175 class org.apache.derby.impl.store.raw.data.StoredPage
 14'866                 174 class java.util.ArrayList
  2'487                 170 class [I
  1'000                 164 class [Lorg.apache.derby.impl.store.raw.data.StoredRecordHeader;
  2'104                 156 class java.lang.Class
  1'542                 110 class [S
 12'753                  99 class org.apache.derby.iapi.types.SQLDate
  3'939                  61 class java.lang.String
    368                  46 class [Ljava.util.concurrent.ConcurrentHashMap$HashEntry;)

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