[
https://issues.apache.org/jira/browse/DERBY-6096?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13593431#comment-13593431
]
Kathey Marsden commented on DERBY-6096:
---------------------------------------
I am out today but thought I would post where I was on trying to get a
reproduction for the memory usage with Clob hash joins. I created this fixture
in memory.ClobMemTest. At one point I was getting an OOM on the query if
derby.language.maxMemoryPerTable wasn't set running -Xmx64M but then started
cleaning up and it no longer occurs. I will look more closely tomorrow but
just wanted to post where I am and get input on how to reproduce.
public void testClobHashJoin() throws SQLException {
Statement s = createStatement();
try {
// Setting maxMemoryPerTable to 0 allows the query to complete
// until OOM is fixed.
//println("setSystemProperty(\"derby.language.maxMemoryPerTable\",
\"0\")");
//setSystemProperty("derby.language.maxMemoryPerTable", "0");
s.executeUpdate("CREATE TABLE T1 (ID INT , NAME VARCHAR(30))");
s.executeUpdate("CREATE TABLE T2 (ID INT , CDATA CLOB(1G))");
PreparedStatement ps = prepareStatement("insert into t1
values(?,?)");
PreparedStatement ps2 = prepareStatement("insert into t2
values(?,?)");
// insert 8 long rows
for (int i = 1; i <= 8; i++) {
ps.setInt(1, i);
ps.setString(2, "name" + i);
ps.executeUpdate();
ps2.setInt(1, i);
ps2.setCharacterStream(2, new LoopingAlphabetReader(
LONG_CLOB_LENGTH), LONG_CLOB_LENGTH);
ps2.executeUpdate();
}
s.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
// Do a query. Force a hash join
PreparedStatement ps3 = prepareStatement("SELECT * FROM t1, t2
--DERBY-PROPERTIES joinStrategy=hash\n"
+ "where t1.id = t2.id AND t1.id < 8 ");
ResultSet rs = ps3.executeQuery();
int i = 0;
for (; rs.next(); i++) {
// just fetch don't materialize results
// derby.tests.trace prints memory usage
println("TotalMemory:" + Runtime.getRuntime().totalMemory()
+ " " + "Free Memory:"
+ Runtime.getRuntime().freeMemory());
}
assertEquals("Expected 7 rows, got + i", 7, i);
rs.close();
RuntimeStatisticsParser p = SQLUtilities
.getRuntimeStatisticsParser(s);
println(p.toString());
assertTrue(p.usedHashJoin());
} finally {
removeSystemProperty("derby.language.maxMemoryPerTable");
s.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
}
}
> DataTypeDescriptor.estimatedMemoryUsage() has no case for BLOB or CLOB so
> would underestimate memory usage for those types at zero
> -----------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-6096
> URL: https://issues.apache.org/jira/browse/DERBY-6096
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.3.0, 10.4.2.0, 10.5.3.0,
> 10.6.2.1, 10.7.1.1, 10.9.1.0, 10.10.0.0, 10.8.3.0
> Reporter: Kathey Marsden
>
> In discussion on derby-dev regarding how much memory is used for hash joins,
> Knut noted:
> I haven't verified, but I think HashJoinStrategy uses
> DataTypeDescriptor.estimatedMemoryUsage() to estimate how much memory
> the hash table will consume. That method has no case for BLOB or CLOB,
> so it looks as if it will return zero for LOB columns. If that's so, it
> will definitely overestimate how many rows fits in maxMemoryPerTable
> kilobytes if the rows contain LOBs.
> DataTypeDescriptor.estimatedMemoryUsage() should be updated to include BLOB
> and CLOB and we should try verify if this theory is correct with a
> reproduction.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
