Return-Path: X-Original-To: apmail-db-derby-dev-archive@www.apache.org Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 7FE469C65 for ; Fri, 24 Feb 2012 21:54:13 +0000 (UTC) Received: (qmail 11059 invoked by uid 500); 24 Feb 2012 21:54:13 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 11037 invoked by uid 500); 24 Feb 2012 21:54:13 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 11028 invoked by uid 99); 24 Feb 2012 21:54:13 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 24 Feb 2012 21:54:13 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED,T_RP_MATCHES_RCVD X-Spam-Check-By: apache.org Received: from [140.211.11.116] (HELO hel.zones.apache.org) (140.211.11.116) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 24 Feb 2012 21:54:10 +0000 Received: from hel.zones.apache.org (hel.zones.apache.org [140.211.11.116]) by hel.zones.apache.org (Postfix) with ESMTP id 361A2339F58 for ; Fri, 24 Feb 2012 21:53:49 +0000 (UTC) Date: Fri, 24 Feb 2012 21:53:49 +0000 (UTC) From: "Mamta A. Satoor (Updated) (JIRA)" To: derby-dev@db.apache.org Message-ID: <974423630.17053.1330120429222.JavaMail.tomcat@hel.zones.apache.org> In-Reply-To: <1146621145.34490.1316177888853.JavaMail.tomcat@hel.zones.apache.org> Subject: [jira] [Updated] (DERBY-5415) Memory leak in statement cache of PreparedStatement MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 X-Virus-Checked: Checked by ClamAV on apache.org [ https://issues.apache.org/jira/browse/DERBY-5415?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Mamta A. Satoor updated DERBY-5415: ----------------------------------- Priority: Minor (was: Critical) Issue & fix info: Repro attached Urgency: Normal Labels: derby_triage10_9 (was: ) > 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 > 5: 201157 4827768 java.lang.String > 6: 174474 4187376 java.util.HashMap$Entry > 7: 43650 3846512 > 8: 7654 3317816 [B > 9: 65633 2663504 > 10: 16143 2481304 [Ljava.util.HashMap$Entry; > 11: 3442 2056408 > 12: 79290 1902960 java.util.ArrayList > 13: 3442 1554272 > 14: 45596 1459072 org.apache.derby.impl.store.raw.data.StoredRecordHeader > 15: 2890 1281888 > 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