Derby
  1. Derby
  2. DERBY-5415

Memory leak in statement cache of PreparedStatement

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.5.3.0, 10.7.1.1, 10.8.1.2
    • Fix Version/s: None
    • Component/s: JDBC, Services
    • Environment:
      Linux, java 1.6.0_27-b07
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Performance

      Description

      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

        Activity

          People

          • Assignee:
            Unassigned
            Reporter:
            Robert Hoffmann
          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:

              Development