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

        Robert Hoffmann created issue -
        Kristian Waagan made changes -
        Field Original Value New Value
        Bug behavior facts [Performance, Deviation from standard] [Performance]
        Urgency Urgent
        Component/s JDBC [ 11407 ]
        Component/s Services [ 11415 ]
        Robert Hoffmann made changes -
        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: 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
        ...

        CASE B:
        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;
        ...

        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
        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
        Robert Hoffmann made changes -
        Affects Version/s 10.7.1.1 [ 12315564 ]
        Robert Hoffmann made changes -
        Affects Version/s 10.5.3.0 [ 12314117 ]
        Mamta A. Satoor made changes -
        Labels derby_triage10_9
        Urgency Normal [ 10052 ]
        Issue & fix info Repro attached [ 10424 ]
        Priority Critical [ 2 ] Minor [ 4 ]
        Gavin made changes -
        Workflow jira [ 12633339 ] Default workflow, editable Closed status [ 12801114 ]

          People

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

            Dates

            • Created:
              Updated:

              Development