SQL performance with complex query

[Chris Wilson <ch...@aptivate.org>]

Dear sirs,

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 download the test case 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)

I think this could be a case where the query optimizer could use some 
work? Is it useful for me to file a bug report somewhere, e.g. in JIRA? 
Can anyone suggest how I could work around the OOM error?

Cheers, Chris.
-- 
Aptivate | http://www.aptivate.org | Phone: +44 1223 760887
The Humanitarian Centre, Fenner's, Gresham Road, Cambridge CB1 2ES

Aptivate is a not-for-profit company registered in England and Wales
with company number 04980791.

Re: SQL performance with complex query

[Brett Wooldridge <br...@gmail.com>]

Please file a JIRA issue.  I don't think preparing a query plan should
consume that much memory, or generate that much garbage.

Brett

Sent from my iPhone

On Apr 18, 2010, at 1:41, Chris Wilson <ch...@aptivate.org> wrote:

> Hi Bryan and all,
>
> On Sat, 17 Apr 2010, Bryan Pendleton wrote:
>
>>> I think this could be a case where the query optimizer could use
>>> some work?
>>> Is it useful for me to file a bug report somewhere, e.g. in JIRA?
>>
>> Yes, it would be particularly helpful if you could provide a complete
>> standalone test case which demonstrates the problem.
>
> I believe I have done so; the link that I provided in my email to the
> sources of the test case from Subversion should run on any Unix system
> with Bash, Sun Java and the Derby libraries available. It has four
> source
> files (a shell script and three SQL scripts) and a copy of the Derby
> database to save you developers the time of reloading the fixture data
> used in the test.
>
>> If that's not possible, then perhaps you could gather query plan
>> output
>> and post that; perhaps the Derby optimizer is choosing a particularly
>> poor query plan.
>
> OK, this is interesting. With the default JVM settings, it crashes
> with
> OOM before outputting the query plan. However, with -Xmx1024m, it
> finishes in reasonable time, and does output the plan, which I've
> attached.
>
> 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? Does this bear looking
> into? Should I file a JIRA issue?
>
> Also the recommended page at
> (http://wiki.apache.org/db-derby/PerformanceDiagnosisTips) contains a
> broken link to "Working with Derby properties"
> (http://db.apache.org/derby/docs/dev/tuning/ctunsetprop34818.html)
> which
> is currently 404.
>
> Cheers, Chris.
> --
> Aptivate | http://www.aptivate.org | Phone: +44 1223 760887
> The Humanitarian Centre, Fenner's, Gresham Road, Cambridge CB1 2ES
>
> Aptivate is a not-for-profit company registered in England and Wales
> with company number 04980791.
> <query-plan.txt>

Re: SQL performance with complex query

[Chris Wilson <ch...@aptivate.org>]

Hi Bryan and all,

On Sat, 17 Apr 2010, Bryan Pendleton wrote:

> > I think this could be a case where the query optimizer could use some work?
> > Is it useful for me to file a bug report somewhere, e.g. in JIRA? 
> 
> Yes, it would be particularly helpful if you could provide a complete
> standalone test case which demonstrates the problem.

I believe I have done so; the link that I provided in my email to the 
sources of the test case from Subversion should run on any Unix system 
with Bash, Sun Java and the Derby libraries available. It has four source 
files (a shell script and three SQL scripts) and a copy of the Derby 
database to save you developers the time of reloading the fixture data 
used in the test.

> If that's not possible, then perhaps you could gather query plan output 
> and post that; perhaps the Derby optimizer is choosing a particularly 
> poor query plan.

OK, this is interesting. With the default JVM settings, it crashes with 
OOM before outputting the query plan. However, with -Xmx1024m, it 
finishes in reasonable time, and does output the plan, which I've 
attached.

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? Does this bear looking 
into? Should I file a JIRA issue?

Also the recommended page at 
(http://wiki.apache.org/db-derby/PerformanceDiagnosisTips) contains a 
broken link to "Working with Derby properties" 
(http://db.apache.org/derby/docs/dev/tuning/ctunsetprop34818.html) which 
is currently 404.

Cheers, Chris.
-- 
Aptivate | http://www.aptivate.org | Phone: +44 1223 760887
The Humanitarian Centre, Fenner's, Gresham Road, Cambridge CB1 2ES

Aptivate is a not-for-profit company registered in England and Wales
with company number 04980791.

Re: SQL performance with complex query

[Bryan Pendleton <bp...@gmail.com>]

> I think this could be a case where the query optimizer could use some 
> work? Is it useful for me to file a bug report somewhere, e.g. in JIRA? 

Yes, it would be particularly helpful if you could provide a complete
standalone test case which demonstrates the problem.

If that's not possible, then perhaps you could gather query plan output
and post that; perhaps the Derby optimizer is choosing a particularly poor
query plan.

> Can anyone suggest how I could work around the OOM error?

You can control Derby's memory usage by simply allocating more memory to
your JVM when you run Derby, typically by specifying -Xmx. Try something
like -Xmx1024m and see if that avoids the out-of-memory problem.

Of course, you don't want to specify a number larger than the actual
amount of physical memory on your computer, so if your computer only has,
e.g., 512m of physical memory, use something like -Xmx384m

You can find some pointers to suggestions about studying Derby performance
issues here: http://wiki.apache.org/db-derby/PerformanceDiagnosisTips

thanks,

bryan