[ 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
