db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Robert Hoffmann (Commented) (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5415) Memory leak in statement cache of PreparedStatement
Date Sun, 26 Feb 2012 17:46:48 GMT

    [ 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

       

Mime
View raw message