HashJoinStrategy leads to java.lang.OutOfMemoryError

• Key: DERBY-106
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: Unassigned
• Reporter: Gerald Khin
• Component/s: SQL
• Fix Version/s: 10.1.1.0

Description

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!

Comments

[Jack Klebanoff]

Do you know whether the memory overflow occurred during compilation or execution? Do you have a stack trace from the OutOfMemoryError? Do you know the sizes of the tables being joined?

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

[Jack Klebanoff]

Subversion revision 157861

[Rick Hillegas]

Assigning to SQL component.