This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jul 12, 2013 7:39 AM by 938489 RSS

com.mysql.jdbc.JDBC4Connection suspected memory leak in java

938489 Newbie
Currently Being Moderated

We have a java application where we are using Bonecp pooling library. Below is how we setup the pooling.

 

[code]

try {
  
// setup the connection pool
  
BoneCPConfig config = new BoneCPConfig();
  config
.setJdbcUrl("jdbc:mysql://**.**.**.**:3306/test1");  config.setUsername("******");
  config
.setPassword("*******");  config.setMinConnectionsPerPartition(5);
  config
.setMaxConnectionsPerPartition(40);
  config
.setPartitionCount(1);
  connectionPool
= new BoneCP(config); // setup the connection pool 
  
}
  
catch (SQLException e) {
  e
.printStackTrace(System.out);
  
}

[/code]


Then for insert and update queries we do this and below we make sure closed the statement.


[code]

Statement stmt1 = null;
stmt1
= dbconn.createStatement();
String insertQuery3 =........
count
= stmt9.executeUpdate(insertQuery3);
try{
  
if ( stmt1!= null ){  stmt1.close();
}
  
else{
  
System.out.println("No stm1 exist");

}
}catch(SQLException ex){   System.out.println("SQLException has been caught for stmt1");
ex
.printStackTrace(System.out);
}

For select we do this 

Statement stmt2 = null;
stmt2
= dbconn.createStatement();
String selectQuery2= .........
ResultSet rs2 = stmt2.executeQuery(selectQuery2);

if(rs2.next())
{

}
try{
  
if ( rs2!= null ){  rs2.close();
  
}   else{
  
System.out.println("No rs2 exist");

}

  
if ( stmt2!= null ){  stmt2.close();
  
}   else{
  
System.out.println("No stm2 exist");

}
}catch(SQLException ex){   System.out.println("SQLException has been caught for stmt2");
ex
.printStackTrace(System.out);
}

[/code]



Thus we have ensured that every resultset and statement is closed immediately after its use. The problem now when we took few sample from MAT is show increasing memory usage as below.

Sample 1

[code]

 


 

21 instances of "com.mysql.jdbc.JDBC4Connection", loaded by "sun.misc.Launcher$ExtClassLoader @ 0xf000a4f0" occupy 11,793,200 (76.19%) bytes.

Biggest instances:

  • com.mysql.jdbc.JDBC4Connection @ 0xf016d728 - 958,096 (6.19%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf0094478 - 875,568 (5.66%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf0201ba0 - 816,048 (5.27%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf04ab3f0 - 754,016 (4.87%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf006ee40 - 727,024 (4.70%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf0076a90 - 663,872 (4.29%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf04ad490 - 618,200 (3.99%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf00b7bd0 - 616,608 (3.98%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf0467bc0 - 612,544 (3.96%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf015fcf0 - 598,400 (3.87%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf021c830 - 584,992 (3.78%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf0097818 - 561,224 (3.63%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf01a27c0 - 531,816 (3.44%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf00bea28 - 531,416 (3.43%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf01c2d80 - 522,184 (3.37%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf04693e0 - 482,992 (3.12%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf012b158 - 453,256 (2.93%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf147f438 - 424,656 (2.74%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf15ff7d0 - 187,008 (1.21%) bytes.

 

Keywords
com.mysql.jdbc.JDBC4Connection
sun.misc.Launcher$ExtClassLoader @ 0xf000a4f0

 

file:///C:/Documents%20and%20Settings/ss/Local%20Settings/Temp/report2060944296830228239/pages/18.html# Reference Pattern
Class NameShallow HeapRetained HeapPercentage
402080.00%

[/code]

 

Sample 2

[code]

 

21 instances of "com.mysql.jdbc.JDBC4Connection", loaded by "sun.misc.Launcher$ExtClassLoader @ 0xf000a4f0" occupy 13,827,392 (78.27%) bytes.

Biggest instances:

  • com.mysql.jdbc.JDBC4Connection @ 0xf016d728 - 1,093,888 (6.19%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf0094478 - 957,888 (5.42%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf0201ba0 - 916,672 (5.19%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf04ab3f0 - 878,392 (4.97%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf006ee40 - 841,136 (4.76%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf015fcf0 - 758,336 (4.29%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf04ad490 - 747,240 (4.23%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf0076a90 - 735,712 (4.16%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf00b7bd0 - 700,912 (3.97%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf0467bc0 - 681,336 (3.86%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf00bea28 - 668,080 (3.78%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf021c830 - 658,816 (3.73%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf0097818 - 638,208 (3.61%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf01a27c0 - 605,376 (3.43%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf01c2d80 - 582,104 (3.29%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf04693e0 - 558,344 (3.16%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf147f438 - 523,952 (2.97%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf012b158 - 508,232 (2.88%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf1603618 - 276,944 (1.57%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf15ff7d0 - 275,288 (1.56%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf148fd70 - 220,536 (1.25%) bytes.

 

Keywords
com.mysql.jdbc.JDBC4Connection
sun.misc.Launcher$ExtClassLoader @ 0xf000a4f0

 

file:///C:/Documents%20and%20Settings/ss/Local%20Settings/Temp/report8548522855535006676/pages/18.html# Reference Pattern
Class NameShallow HeapRetained HeapPercentage
402080.00%

[/code]

Sample 3

 

[code]

 

 


 

21 instances of "com.mysql.jdbc.JDBC4Connection", loaded by "sun.misc.Launcher$ExtClassLoader @ 0xf000a4f0" occupy 17,339,632 (80.79%) bytes.

Biggest instances:

  • com.mysql.jdbc.JDBC4Connection @ 0xf016d728 - 1,228,048 (5.72%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf0094478 - 1,144,056 (5.33%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf0201ba0 - 1,126,120 (5.25%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf04ab3f0 - 1,074,552 (5.01%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf006ee40 - 993,912 (4.63%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf0076a90 - 931,512 (4.34%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf015fcf0 - 930,952 (4.34%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf04ad490 - 918,176 (4.28%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf00bea28 - 874,512 (4.07%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf0467bc0 - 846,368 (3.94%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf00b7bd0 - 838,448 (3.91%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf021c830 - 799,184 (3.72%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf0097818 - 791,256 (3.69%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf01a27c0 - 763,264 (3.56%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf01c2d80 - 745,088 (3.47%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf012b158 - 710,704 (3.31%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf04693e0 - 704,072 (3.28%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf147f438 - 680,400 (3.17%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf1603618 - 458,472 (2.14%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf148fd70 - 390,984 (1.82%) bytes.
  • com.mysql.jdbc.JDBC4Connection @ 0xf15ff7d0 - 389,552 (1.82%) bytes.

 

Keywords
com.mysql.jdbc.JDBC4Connection
sun.misc.Launcher$ExtClassLoader @ 0xf000a4f0

 

file:///C:/Documents%20and%20Settings/ss/Local%20Settings/Temp/report3656754586920669017/pages/18.html# Reference Pattern
Class NameShallow HeapRetained HeapPercentage
402080.00%

[/code]

  • 1. Re: com.mysql.jdbc.JDBC4Connection suspected memory leak in java
    masijade Explorer
    Currently Being Moderated

    Um, no you do not "make sure closed the statement".


    something like the below will "make sure closed the statement".

     

    try {

      Statement stmt = con.createStatement();

      try {

        -- perform queries

      } finally {

        stmt.close();

      }

    } catch (SQLException sqle) {

      -- error handling

    }


    and you also still need to close the connection (even when it is from a pool), when close is called on a pooled connection (as the connection object is usually a wrapper around a "real" connection object) simply returns the connection to the pool.  If you do not call close, the connection never gets returned to the pool, and THAT is probably what your "leak" is.


  • 2. Re: com.mysql.jdbc.JDBC4Connection suspected memory leak in java
    gimbal2 Guru
    Currently Being Moderated

    Well said.

     

    It is very much unproductive to blame tech - 999/1000 times its a bug in your own code so assume that is so and you'll find that issues resolves themselves a lot quicker and smoother.

  • 3. Re: com.mysql.jdbc.JDBC4Connection suspected memory leak in java
    938489 Newbie
    Currently Being Moderated

    Dear Masijade,

                          Actually I was confuse with where to put and not to put the try and catch. Below is how my skeleton of my codes will be looking like. I just put one sample query there. If you notice at the very end finally I already have even now dbconn.close(); which closes the pooling connection. The problem  I am worried now is that incase there is any exception how will it be rollbacked. Because if you notice now I lump all the queries under one big try and catch but with your suggestion I have to do try and catch for each query right?

     

    [code]

    BoneCP connectionPool = null;

      class ConnectionHandler implements Runnable {

     

     

        private Socket receivedSocketConn1;

        ConnectionHandler(Socket receivedSocketConn1) {

          this.receivedSocketConn1=receivedSocketConn1;

        }

        Connection dbconn = null;

      

        public void run() { // etc

         BufferedWriter writeBuffer = null;

         BufferedReader readBuffer = null;

         String capturedMessage="";

       

         try{

            dbconn = connectionPool.getConnection();

            dbconn.setAutoCommit(false);

          

            while ((nextChar=readBuffer.read()) != -1){          

              capturedMessage += (char) nextChar;

             

              if (nextChar == '*')

              {

               try{

                  

                    //all queries here.

                    try{            

                        Statement stmt1 = null;

                        stmt1 = dbconn.createStatement();

                        try{

                          String updateCommand1 = //query

                          count = stmt1.executeUpdate(updateCommand1);

                        }

                        finally{

                          if( stmt1 != null ){

                           stmt1.close();

                         }

                        else{

                        System.out.println("stmt1 is null in finally close");

                         }

                       }

                   }

                   catch(SQLException ex){

                     ex.printStackTrace(System.out);

                   }

                   dbconn.commit

                }

               catch (SQLException ex){

                    ex.printStackTrace(System.out);

                    try{  

                  dbconn.rollback();

               }

               catch (Exception rollback){  

                  rollback.printStackTrace(System.out);

                  }

           }

           catch (Exception e){

               e.printStackTrace(System.out);

               try{  

                  dbconn.rollback();

               }

               catch (Exception rollback){  

                  rollback.printStackTrace(System.out);

                 }

           }

           finally

         {

                  

         }

              

              

            }

         catch (SocketTimeoutException ex){

               ex.printStackTrace();

         }

         catch (IOException ex){

               ex.printStackTrace();

         }

         catch (Exception ex){

               ex.printStackTrace(System.out);

         }    

          finally{

            try{

             if ( dbconn != null ){

               dbconn.close();

             }

             else{

              System.out.println("dbConn is null in finally close");

             }

            }

            catch(SQLException ex){

                ex.printStackTrace();

            }

            try{

              if ( writeBuffer != null ){

                writeBuffer.close();

             }

             else{

              System.out.println("w is null in finally close");

             }

            }

            catch(IOException ex){

                ex.printStackTrace(System.out);

            }

           }

          }

        }

    [/code]

  • 4. Re: com.mysql.jdbc.JDBC4Connection suspected memory leak in java
    938489 Newbie
    Currently Being Moderated

    Dear Gimbal,

                      I dont intend to blame the tech either but just kind of lost as I was not too sure with MAT's results. I am trying to learn and where it is pointing too. Sorry for that.

  • 5. Re: com.mysql.jdbc.JDBC4Connection suspected memory leak in java
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated

    Close the result set, close the statement, close the connection.

     

    ALWAYS close every single one.  ALWAYS do it in that order.

     

    Where 'always' means that all normal code paths and all exception paths do it.

  • 6. Re: com.mysql.jdbc.JDBC4Connection suspected memory leak in java
    938489 Newbie
    Currently Being Moderated

    Dear Jschell,

                       I have been closing it accordingly but the masijade said it was not closing thus I have put my full skeleton codes below but I am worried how will it rollback incase there is any issue? Any suggestion for my new codes as shown below. Thank you.

  • 7. Re: com.mysql.jdbc.JDBC4Connection suspected memory leak in java
    masijade Explorer
    Currently Being Moderated

    Here another skeleton for what I think it is you want

     

    Socket whatever = ....;
    Connection conn = null;
    try {
      conn = .....;
    Statement stmt = ...;
    try {
      while (/*readsocket*/) {
    // concatenate -- I must say this is very bad performance, you might want to change how you are doing this
    // to at least use a stringbuilder but reading char by cahr is also very inefficient, can you not wrap it in a
    // BufferedReader, at least?
        if (!/*socket read condition*/) continue;  // notice the NOT "!" here
          // perform query
    } // while
      } finally {
        try { stmt.close(); } catch (Exception e) { /*log this you can't really do anything else*/ }
      }
      conn.commit();
    } catch (SQLException sqle) {
      if (conn != null) try { conn.rollback(); } catch (Exception e) { /*log this you can't really do anything else*/ }
    // log
    } catch (IOException ioe) {
    // conn should definitely not be null here as the io attempts don't come until after it is created
      try { conn.rollback(); } catch (Exception e) { /*log this you can't really do anything else*/ }
      // any other needed recovery
    // log
    } finally {
      if (conn != null) try { conn.close(); } catch (Exception e) { /*log this you can't really do anything else*/ }
    }
    
    
  • 8. Re: com.mysql.jdbc.JDBC4Connection suspected memory leak in java
    Joe Weinstein Expert
    Currently Being Moderated

    Read the meanings of the pool config parameters you have set. You can expect the pool to be making and keeping connections, so memory for them will increase up to the limit of connections you've set.

  • 9. Re: com.mysql.jdbc.JDBC4Connection suspected memory leak in java
    938489 Newbie
    Currently Being Moderated

    Dear Masijade,

                         Thank you for the skeleton. When you say wrap it in BufferedReader I dont quite get you as you can see the string will be ending with an '*' char. So what else can be done here? I cant you use readline either ? I dont get you here // notice the NOT "!" here  ? Now the problem with your skeleton is that I got many more function doing different processing which depends on the captured string. So how can I just manage the whole thing with one single stmt because I read before they said implement a new resultset and statement and close it immediately after its use. In those function also I run insert,select and update statements too. Where I put this throws Exception so incase any of its query have issue then the whole thing can be rollback? So now when I move to the mechanism I tend to loose all this? Hope you could see my issue here.

  • 10. Re: com.mysql.jdbc.JDBC4Connection suspected memory leak in java
    938489 Newbie
    Currently Being Moderated

    Dear Joe,

                  IF that is the case then I guess there is no leak here? But what worries me is for e.g .I take this 3 different values from each of my sample above I can see that for this connection the bytes are keep increasing this worries me? Any explanation to this?

     

    com.mysql.jdbc.JDBC4Connection @ 0xf016d728 - 958,096 (6.19%) bytes.

     

     

    com.mysql.jdbc.JDBC4Connection @ 0xf016d728 - 1,093,888 (6.19%) bytes.

     

     

    com.mysql.jdbc.JDBC4Connection @ 0xf016d728 - 1,228,048 (5.72%) bytes.

  • 11. Re: com.mysql.jdbc.JDBC4Connection suspected memory leak in java
    masijade Explorer
    Currently Being Moderated

    Look at the API for BufferedInputStream.  you can STILL read character for character using that but you won't create any (or at least A LOT less) native IO doing so as the information will be in a buffer.  When you read character for character from a "raw" stream, as you are doing now, you create native IO processes for EVERY read and THAT is EXTREMELY bad for performance.

     

    Also, commit, and rollback function for the entire length of the session.  So, do everything you need to do and perform the commit only at the end, and the rollback whenever there is an exception, and make sure that the entire process actually stops there (either by catching everything in ONE ()or one series of) catch block(s) or returning from the catch block, etc.  You REALLY need to learn some more basic java programming (namely standard control flow logic), though.

  • 12. Re: com.mysql.jdbc.JDBC4Connection suspected memory leak in java
    gimbal2 Guru
    Currently Being Moderated

    938489 wrote:

     

    Dear Joe,

                  IF that is the case then I guess there is no leak here? But what worries me is for e.g .I take this 3 different values from each of my sample above I can see that for this connection the bytes are keep increasing this worries me?

    Can you explain to yourself why that worries you when you don't know what this class actually does under the hood? I see something different: only 14mb is being used. That's nothing. Now if you would let this application run for a few days and that number has shot up to 100mb+, THEN it is time to start worrying.

     

    In other words: stop worrying and guessing and start measuring.

  • 13. Re: com.mysql.jdbc.JDBC4Connection suspected memory leak in java
    938489 Newbie
    Currently Being Moderated

    Dear Gimbal2,

                         Well I know I am new into this area of analysis. Sorry if I am trying to be proactive and trying to look out for measurement such as this which keep increasing and worries me. I cant find any good guidance on telling us how much memory is safe and not safe for instance you said 100mb is dangerous but unfortunately I dont find this sort of guidance any where else. I am trying to learn to measure as what am I doing is looking into further details of the mat analaysis. Is there any other route I should take to learn up on this ?

  • 14. Re: com.mysql.jdbc.JDBC4Connection suspected memory leak in java
    938489 Newbie
    Currently Being Moderated

    Dear Gimbal,

                       I guess you are referring to this right BufferedInputStream (Java Platform SE 7 ) . So then must I also change BufferedWriter ? I also saw this link http://stackoverflow.com/questions/5713857/bufferedinputstream-to-string-conversion.but it requires to set the this byte[] contents = new byte[1024]; . So incase its more then 1024 what exactly will happen then?

                      Currently I am catching everything into one big try and catch where I rollback incase there is any errors found a long the way. What I am not able to solve for instance I have done this changes done as below. This is actually within a bigger try and catch with has the rollback. So in case there is an exception in this inner try and catch how will the outer catch rollback the entire transaction here is where I need some help. Currently I only do the commit towards the end not for everything one query that I am pretty much sure and understand well.

     

    [code]

    try{           

                        Statement stmt1 = null;

                        stmt1 = dbconn.createStatement();

                        try{

                          String updateCommand1 = //query

                          count = stmt1.executeUpdate(updateCommand1);

                        }

                        finally{

                          if( stmt1 != null ){

                           stmt1.close();

                         }

                        else{

                        System.out.println("stmt1 is null in finally close");

                         }

                       }

                   }

                   catch(SQLException ex){

                     ex.printStackTrace(System.out);

                   }

    [/code]

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points