Java JDBC: MySQL write Out of Memory

Question

Where is the memory leak in this code? This function is executed millions of times with an extensive usage of memory, causing an out of memory exception after 2.4million execusions.

public static void saveCall(Call call) {
        conn = getInstance();

        if (conn != null) {
            try {
                calendar.setTime(call.getDate());
                String sql = "INSERT INTO Calls(id, datetime, duration, customer_phone_id, partner_phone_id) "
                        + "VALUES(null, ?, ?, ?, ?)";
                PreparedStatement preparedStatement = conn
                        .prepareStatement(sql);
                preparedStatement.setString(1, dateFormat.format(calendar.getTime()));
                preparedStatement.setLong(2, call.getDuration());
                preparedStatement.setLong(3, call.getPhone().getPhoneNumber());
                preparedStatement.setLong(4, call.getPhonePartner()
                        .getPhoneNumber());

                preparedStatement.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

You may try to close your PreparedStatements. Also, Could you please show us your getInstance? — lifus
– lifus, Commented May 19, 2013 at 21:15
You're welcome. You may also end up using Connection Pool. I'm not a fan of getInstance method at all. Are you sure that your connection will be closed? — lifus
– lifus, Commented May 19, 2013 at 21:30
@lifus - if getInstance() uses the same connection over and over again, it does not matter. Lets assume that he has really fixed the problem ... rather than attempting to redesign his entire application without seeing the source code :-) — Stephen C
– Stephen C, Commented May 19, 2013 at 21:45
@StephenC - thats right, it was more like a tip. If 1)getInstance is able to handle situations when connection is no longer valid and 2)there is no need in several connections then i'm ok with it. I just wasn't sure that PreparedStaments are the only objects to blame. — lifus
– lifus, Commented May 19, 2013 at 21:53

Cyril · Accepted Answer · 2013-05-19 21:30:44Z

1

If your program make an extensive memory usage, there might be no leak but only a garbage collector problem. IE, your garbage came too late to free some space for creating new objects.

From here, you might want to profile your code when running your queries (visualvm or jconsole provided with any jdk). You shall see how your memory space is filled (garbage behavior and objects size).

Then, if needed, you will need to configure your jvm garbage collection The extensive documentation is here : http://www.oracle.com/technetwork/java/javase/gc-tuning-6-140523.html If you share your memory profile, we might help you configure it.

EDIT : There was a memory leak and I was wrong ;-)

answered May 19, 2013 at 21:30

Cyril

5474 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lifus Over a year ago

Yeah, close methods are there for a reason. GC's are great but not quite great enough to handle resource leaks.

lifus · Accepted Answer · 2013-05-19 22:56:14Z

My two cents

JDBC is tricky.

In this case, PreparedStatement should be closed in order to avoid resource leak.

public static void saveCall(Call call) {
    conn = getInstance();

    if (conn != null) {
        calendar.setTime(call.getDate());
        String sql = "INSERT INTO Calls(id, datetime, duration, customer_phone_id, partner_phone_id) "
                    + "VALUES(null, ?, ?, ?, ?)";

        PreparedStatement preparedStatement = null;            
        try {               
            conn.prepareStatement(sql);
            preparedStatement.setString(1, dateFormat.format(calendar.getTime()));
            preparedStatement.setLong(2, call.getDuration());
            preparedStatement.setLong(3, call.getPhone().getPhoneNumber());
            preparedStatement.setLong(4, call.getPhonePartner()
                    .getPhoneNumber());

            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }
    }
}

Here is Java 7 solution:

public static void saveCall(Call call) {
    conn = getInstance();

    if (conn != null) {
        calendar.setTime(call.getDate());
        String sql = "INSERT INTO Calls(id, datetime, duration, customer_phone_id, partner_phone_id) "
                    + "VALUES(null, ?, ?, ?, ?)";

        try (PreparedStatement preparedStatement = conn.prepareStatement(sql)) {                
            preparedStatement.setString(1, dateFormat.format(calendar.getTime()));
            preparedStatement.setLong(2, call.getDuration());
            preparedStatement.setLong(3, call.getPhone().getPhoneNumber());
            preparedStatement.setLong(4, call.getPhonePartner()
                    .getPhoneNumber());

            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Madhu Sharan · Accepted Answer · 2013-05-19 21:59:29Z

@Pascal Bayer : you should CLOSE your connections after the SQL operations. The modified code with close() would look something like the one below.

public static void saveCall(Call call) {
        conn = getInstance();

        if (conn != null) {
            try {
                calendar.setTime(call.getDate());
                String sql = "INSERT INTO Calls(id, datetime, duration, customer_phone_id, partner_phone_id) "
                        + "VALUES(null, ?, ?, ?, ?)";
                PreparedStatement preparedStatement = conn
                        .prepareStatement(sql);
                preparedStatement.setString(1, dateFormat.format(calendar.getTime()));
                preparedStatement.setLong(2, call.getDuration());
                preparedStatement.setLong(3, call.getPhone().getPhoneNumber());
                preparedStatement.setLong(4, call.getPhonePartner()
                        .getPhoneNumber());

                preparedStatement.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
              // good practice of closing connections as soon as 
              // the SQL operations are       completed
              if(!conn.isClosed())
                conn.close();
            }
        }
}

Cheers, Madhu.

Collectives™ on Stack Overflow

Java JDBC: MySQL write Out of Memory

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related