db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (Commented) (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5415) Memory leak in statement cache of PreparedStatement
Date Wed, 26 Oct 2011 13:59:32 GMT

    [ 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

        

Mime
View raw message