db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Robert Hoffmann (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5415) Memory leak in statement cache of PreparedStatement
Date Sun, 18 Sep 2011 10:44:09 GMT

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

Robert Hoffmann commented on DERBY-5415:
----------------------------------------

'Good news' – I have created a standalone test case.

1)	Download the zip archive (~10mb) from: 
http://cbio.mskcc.org/~hoffmann/testcases/derby_test.zip

2)	Unzip, enter dir and run ant
>ant

3)	This will start the test - it’s self explanatory I believe.
The zip contains a test db (./data), but you can create a new one by deleting it and starting
the application like before.

4) I have tested the application in different environments:

a) Linux CentOs
java version "1.6.0_27"
Java(TM) SE Runtime Environment (build 1.6.0_27-b07)
Java HotSpot(TM) Server VM (build 20.2-b06, mixed mode)

b) OS X 
java version "1.6.0_26"
Java(TM) SE Runtime Environment (build 1.6.0_26-b03-384-10M3425)
Java HotSpot(TM) 64-Bit Server VM (build 20.1-b02-384, mixed mode)

I hope this will make your job a lot easier. 

Thank you very much,
Robert



> 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.
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

Mime
View raw message