db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Gerald Khin (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-106) HashJoinStrategy leads to java.lang.OutOfMemoryError
Date Mon, 20 Dec 2004 08:04:22 GMT
     [ http://nagoya.apache.org/jira/browse/DERBY-106?page=comments#action_56877 ]
     
Gerald Khin commented on DERBY-106:
-----------------------------------

The system property derby.language.maxMemoryPerTable is the system property I asked for. Setting
it to 0 works like a charm and turns the hash join strategy off. So I'm happy and the bug
can be closed. Perhaps this system property should be mentioned somewhere in the derby tuning
manual.

For completenesss, if someone is still interested on details:

The SQL query of interest is this one:

SELECT c.* FROM AttrOcc c WHERE  EXISTS (SELECT 'X' FROM Occ p WHERE c.OccID=p.ID AND p.DISCRIMINATOR=1)

As you can see, there are two tables involved: A table Occ and a table Attrocc whereas the
latter has a foreign key 
constraint on column OccID that refers to the primary key column ID of table Occ:

CREATE TABLE Occ (
  ID CHAR(15) PRIMARY KEY,
  DISCRIMINATOR NUMERIC(10),
  ...
)

CREATE TABLE AttrOcc (
  ID CHAR(15) PRIMARY KEY,
  OCCID CHAR(15) NOT NULL REFERENCES OCC ON DELETE CASCADE,
  ...
)

There is no index on column Occ.DISCRIMINATOR.

Table Occ has 267661 rows and table AttrOcc has 153084 rows.

My testprogram runs with -Xmx128m.

If I use default derby.language.maxMemoryPerTable (i.e.) 1024K, then the SQL statement above
leads to that OutOfMemoryError. 
And the query take 192s for execution.

If I create a compound index on Occ(ID,DISCRIMINATOR) and use default derby.language.maxMemoryPerTable
(i.e.) 1024K, then
it needs a bit less memory, so that it runs with -Xmx128. And it takes 140s to execute. So
this is slighly better.

If I set derby.language.maxMemoryPerTable to 0 (and without index on Occ(ID,DISCRIMINATOR)),
then memory consumption is
minimal: -Xmx48 is sufficient. And it takes only 19s to execute (This is an order of magnitude
better than the 
derby.language.maxMemoryPerTable=1024 variant).




> HashJoinStrategy leads to java.lang.OutOfMemoryError
> ----------------------------------------------------
>
>          Key: DERBY-106
>          URL: http://nagoya.apache.org/jira/browse/DERBY-106
>      Project: Derby
>         Type: Bug
>     Reporter: Gerald Khin

>
> My application is running out of memory: I encounterd a java.lang.OutOfMemoryError. I
used -Xmx256M. Unfortunatley, I cannot spend an arbitrary amount of JVM memory. 
> Then, I commented out the line in class OptimizerFactoryImpl which was adding the HashJoinStrategy
to the set of Join strategies:
> 		if (joinStrategySet == null)
> 		{
> //			JoinStrategy[] jss = new JoinStrategy[2];
> 			JoinStrategy[] jss = new JoinStrategy[1];
> 			jss[0] = new NestedLoopJoinStrategy();
> //			jss[1] = new HashJoinStrategy();
> 			joinStrategySet = jss;
> 		}
> And with these changes the OutOfMemoryError has gone away! And it works even with -Xmx128M!!!
> So I guess that there is a major memory issue with this HashJoin strategy implementation.
> If it turns out to be too complicated to make the memory consumption more predicatble
or even bounded to some configurable limit, then I need at least as a workaround a way to
turn off the HashJoin strategy completely: I did it by patching and building my own derby.jar,
but if there would be an official solution with some kind of switch like a system property,
it would be great!

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://nagoya.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira


Mime
View raw message