db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (Commented) (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5415) Memory leak in statement cache of PreparedStatement
Date Fri, 24 Feb 2012 21:53:48 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5415?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13215961#comment-13215961
] 

Mamta A. Satoor commented on DERBY-5415:
----------------------------------------

Knut explained the behavior as expected because there was no statement caching and hence we
need to generate a new statement for every new query execution. Robert, should this be closed
as not a bug?
                
> 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
>              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