[
https://issues.apache.org/jira/browse/DERBY-5415?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13216798#comment-13216798
]
Robert Hoffmann commented on DERBY-5415:
----------------------------------------
1) Disproportionate use of resources
It is true that there is no OutOfMemoryError, but I believe that the amount of
RAM consumption is way out of proportion with the functionality provided.
Please consider the situation under which we noticed the problem. Our process
actually needs a lot of memory for quick dictionary lookups. So if we set
Xmx=2000m we actually intend to use the reserved memory for that purpose and
not to sustain the RDBMS.
So although Derby has no OutOfMemError, our code certainly does (in competition
with Derby).
It makes planning the memory consumption of our code impossible. So by it’s
indirect effects, I think it is justified to call it a (serious) bug.
2) Statement cache
Just to make sure, when we noticed the problem in our production environment,
we played with the statement cache setting to no avail. So the memory filled
up, even with the cache set to some value. (Only, I could not reproduce this in
a standalone test application. So for the test case, I found that disabling the
cache could mimic the effect.)
3) Workaround
Keeping the statement open throughout the process works (and Derby runs stable
and super fast – love it!). However, to be honest it is the first time ever I
had to even consider keeping a statement open and it certainly increases code
complexity (compared to the simple finally clause).
4) Something is wrong
My guess is that cleaning out unused statements from cache does not work quite
optimal or is not triggered/enforced often enough. And this is even more
curious since disabling the cache has no effect at all, i.e. statements still
accumulate in memory.
Again, thank you for this great product and all your efforts!
> 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: Minor
> Labels: derby_triage10_9
>
> 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
