[
https://issues.apache.org/jira/browse/DERBY-5415?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13135989#comment-13135989
]
Knut Anders Hatlen commented on DERBY-5415:
-------------------------------------------
I also see the described behaviour, but I don't think it's a bug.
The memory usage reported by the test is the JVM's memory usage, not Derby's.
It eventually stabilizes on 593 MB in my environment. If I bound the size of
the heap and permgen space that the JVM is allowed to allocate, for example by
setting ANT_OPTS="-Xmx16M -XX:MaxPermSize=16M", the memory usage stabilizes on
100 MB. There's no OutOfMemoryError, even with a small heap, so it doesn't look
like a memory leak.
The test case disables the statement cache. In that case, it is expected that
each call to prepareStatement() will generate a completely new statement and a
new query execution plan, which would explain why it generates lots of garbage
on the heap (which is also why the memory usage of the process increases, I
would assume). All of it should be eligible for garbage collection, though,
which is also supported by the observation that there is no OutOfMemoryError
when the heap size is reduced.
Finally, keeping a single PreparedStatement open and invoking execute()
multiple times on that single instance is actually the recommended way to
execute statements. Especially when the statement cache is disabled, the
performance is expected to be much better than if you recompile the statement
on every execution.
> Memory leak in statement cache of PreparedStatement
> ---------------------------------------------------
>
> Key: DERBY-5415
> URL: https://issues.apache.org/jira/browse/DERBY-5415
> Project: Derby
> Issue Type: Bug
> Components: JDBC, Services
> Affects Versions: 10.5.3.0, 10.7.1.1, 10.8.1.2
> Environment: Linux, java 1.6.0_27-b07
> Reporter: Robert Hoffmann
> Priority: Critical
>
> Hi,
> I) Description
> When making thousands of simple queries to one table using PreparedStatement,
> I have noticed quickly increasing memory usage (hundreds of MB within a few
> dozens of seconds): CASE A.
> I found that memory usage is NORMAL when I keep the PreparedStatement OPEN
> for all queries (CASE B).
> CASE A ("Closing and preparing statement -> leaking"):
> >>
> while(true) {
> PreparedStatement ps = con.prepareStatement("SELECT * from t where a=?");
> ps.setInt(1, r);
> ResultSet rs = ps.executeQuery();
> while (rs.next()) {
> rs.getInt("b");
> }
> rs.close();
> ps.close();
> }
> <<
> CASE B ("Keep prepared statement open -> steady memory"):
> >>
> PreparedStatement ps = con.prepareStatement("SELECT * from t where a=?");
> while(true) {
> ps.setInt(1, r);
> ResultSet rs = ps.executeQuery();
> while (rs.next()) {
> rs.getInt("b");
> }
> rs.close();
> // keep open: ps.close(); // close later
> }
> <<
> II) Reproducibility and heap histogram
> I can easily reproduce this problem in our production environment. And the
> heap of both cases is very distinct:
> CASE A:
> num #instances #bytes class name
> ----------------------------------------------
> 1: 1133492 57289984 [Ljava.lang.Object;
> 2: 1035688 53548872 [C
> 3: 249501 33051904 [I
> 4: 152208 21917952
> org.apache.derby.impl.jdbc.EmbedPreparedStatement40
> 5: 59773 20561912
> org.apache.derby.impl.sql.execute.BulkTableScanResultSet
> 6: 750585 18014040 java.util.ArrayList
> 7: 674840 16196160 java.lang.String
> 8: 989684 15834944 org.apache.derby.iapi.types.SQLInteger
> 9: 391939 15677560 org.apache.derby.impl.sql.GenericParameter
> 10: 538700 14375272
> [Lorg.apache.derby.iapi.types.DataValueDescriptor;
> 11: 59775 13389600
> org.apache.derby.impl.sql.execute.IndexRowToBaseRowResultSet
> 12: 59775 12433200
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet
> 13: 59775 9085800
> org.apache.derby.impl.store.access.btree.index.B2IForwardScan
> 14: 179325 8607600
> org.apache.derby.impl.store.raw.data.BaseContainerHandle
> 15: 351721 8441304 java.util.HashMap$Entry
> 16: 239117 7651744 java.util.HashMap$KeyIterator
> 17: 59775 6694800
> org.apache.derby.impl.jdbc.EmbedResultSet40
> 18: 239119 5738856
> org.apache.derby.impl.store.access.heap.HeapRowLocation
> 19: 179325 5738400
> org.apache.derby.impl.store.access.conglomerate.OpenConglomerateScratchSpace
> 20: 119550 5738400
> org.apache.derby.impl.store.access.heap.OpenHeap
> 21: 119548 5738240
> [[Lorg.apache.derby.iapi.types.DataValueDescriptor;
> ...
> CASE B:
> num #instances #bytes class name
> ----------------------------------------------
> 1: 224186 9471600 [C
> 2: 21030 8223200 [I
> 3: 105020 5553016 [Ljava.lang.Object;
> 4: 43650 4931368 <constMethodKlass>
> 5: 201157 4827768 java.lang.String
> 6: 174474 4187376 java.util.HashMap$Entry
> 7: 43650 3846512 <methodKlass>
> 8: 7654 3317816 [B
> 9: 65633 2663504 <symbolKlass>
> 10: 16143 2481304 [Ljava.util.HashMap$Entry;
> 11: 3442 2056408 <constantPoolKlass>
> 12: 79290 1902960 java.util.ArrayList
> 13: 3442 1554272 <instanceKlassKlass>
> 14: 45596 1459072
> org.apache.derby.impl.store.raw.data.StoredRecordHeader
> 15: 2890 1281888 <constantPoolCacheKlass>
> 16: 25536 1225728 at.intelservice.ie.IS_SText$SIsland
> 17: 45566 1093584
> org.apache.derby.impl.store.raw.data.RecordId
> 18: 28649 916768 java.util.LinkedHashMap$Entry
> 19: 1795 734400
> [Lorg.apache.derby.impl.store.raw.data.StoredRecordHeader;
> 20: 4025 611800
> org.apache.derby.impl.store.access.btree.index.B2IForwardScan
> 21: 14614 584560 java.util.HashMap
> 22: 12075 579600
> org.apache.derby.impl.store.raw.data.BaseContainerHandle
> 23: 34005 544080 java.lang.Integer
> 24: 4817 539504
> org.apache.derby.impl.jdbc.EmbedResultSet40
> ...
> III) Simple test app
> Unfortunately, I am unable to create a simple test that would work on my
> desktop. However if I set derby.language.statementCacheSize=0 then I get a
> similar phenotype as on our production server (i.e. CASE A).
> IV) Workaround
> Right now I am keeping the PreparedStatement open as a workaround but I am
> afraid this might lead to other problems.
> I hope this will help you to make Derby even better!
> Thank you very much for this great product and best regards,
> Robert
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
