JDBC: OutOfMemoryError when running the `insert` query on MySQL

Question

Please have a look at the below code

KeyWordTableCreator.java

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;

public class KeyWordTableCreator 
{
    public KeyWordTableCreator(String str)
    {
        File file = new File(str);

        try
        {
            readAndUpdate(file);
        }
        catch(IOException io)
        {
            io.printStackTrace();
        }
    }

    public void readAndUpdate(File file) throws IOException
    {
        DatabaseConnector db = new DatabaseHandler();
        db.makeConnection();

        BufferedReader br = new BufferedReader(new FileReader(file));
        String str = "";
        int index=0;

        label1:
        while((str=br.readLine())!=null)
        {
            label2:
            for(int i=0;i<str.length();i=i+3)
            {

                String word = str.substring(i, i+3);
                //  System.out.println(word);
                int insert = db.insert(index, word);

                if(insert<0)
                {
                    System.out.println("Operation Broke");
                    break label1;
                }

            }


            System.out.println("Index Updated: "+index);
            //System.out.println(String.valueOf("Total words in this run: "+tempCounter));
            index++;

            if((index%100)==0)
            {
                System.gc();
            }

        }

        db.closeConnection();
        System.out.println("Completed");
    }
}

DatabaseConnector.java

public interface DatabaseConnector {

    public void makeConnection();
    public void closeConnection();
    public int insert(int index, String hashWord);

}

DatabaseHandler.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;



    public class DatabaseHandler implements DatabaseConnector
    {
        private Connection con;

        @Override
        public void makeConnection() 
        {
           try
            {
                 Class.forName("com.mysql.jdbc.Driver");

                 con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xxx","xxx","");

                 //System.out.println("Connection Established");
            }
            catch(Exception e)
            {
                e.printStackTrace();
            }
        }

        @Override
        public void closeConnection() 
        {
            try
            {
                con.close();
            }
            catch(Exception e)
            {
                e.printStackTrace();
            }
        }

        @Override
        public int insert(int index, String hashWord)
        {
            String str="";
            int result=0;
            int returnResult=0;

            try
            {
                con.setAutoCommit(false);
                PreparedStatement ps = con.prepareStatement("insert into Key_Word (indexVal,hashed_word) values(?,?)");
                ps.setInt(1, index);
                ps.setString(2, hashWord);

                result = ps.executeUpdate();
                con.commit();

                if(result>0)
                {
                    returnResult = 1;
                }
                else
                {
                    returnResult = -1;
                    System.out.println( index+" failed to update");
                }


            }
            catch(SQLException s)
            {
                returnResult = -1;
                s.printStackTrace();
                try {
                    con.rollback();
                    System.out.println(index+" Exception Occured. Update Failed");

                } catch (SQLException ex) {
                    ex.printStackTrace();
                    str = index+" Exception Occured. Update Failed. Rollback Failed";
                }
            }

            return returnResult;
        }

    }

The database insert() method is running inside the KeyWordCreator. Basically, inside its loops, the database insert() method will be called for 150 million times. But, this is giving me OutOfMemory Exception after running for sometime.

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
        at com.mysql.jdbc.MysqlIO.unpackField(MysqlIO.java:766)
        at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:431)
        at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3245)

        at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2413)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2836)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2828)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2777)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1651)
        at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3966)
        at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3943)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java
:2399)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java
:2366)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java
:2350)
        at xxx.DatabaseHandler.insert(DatabaseHandler.java:64)
        at xxx.KeyWordTableCreator.readAndUpdate(KeyWordTableCreator.ja
va:51)
        at xxx.KeyWordTableCreator.<init>(KeyWordTableCreator.java:25)
        at xxx.Main.main(Main.java:26)

C:\Users\Dev\Documents\xxx\Key-Word Table Creator\dist>

How can I solve this?

PS: I have ran this program by using the max memory I can (java -jar -Xmx1g) but this doesn't work.

I think such huge numbers of `inserts` should be done via batches with a help of transactions — nikis, Apr 25 '14 at 19:08
this could help you... http://serverfault.com/questions/3952/what-should-i-know-before-going-live-with-an-innodb-database/3979#3979 — Pat B, Apr 25 '14 at 19:12
Why are you creating the prepared statement in the insert() method? You are creating/destroying that statement a gazillion times, which completely defeats the purpose of a prepared statement. create it once outside the method and pass it in, then just update the parameter values. — OldProgrammer, Apr 25 '14 at 19:15

score 2 · Accepted Answer · edited May 23 '17 at 12:28

2

In your implementation of the insert() method, be sure to call

ps.close();
result.close();

before you leave the method. Otherwise, all those statement and result set objects build up in memory until your code breaks.

Alternately, you could reuse the PreparedStatement object with each insert(). This shouldn't be necessary to prevent the OutOfMemoryError, but it might gain you some execution efficiency. For advice on that, see Reusing a PreparedStatement multiple times.

edited May 23 '17 at 12:28

Community

1
1

answered Apr 25 '14 at 19:09

frances

1,232
10
17

I have set the `setAutoCommit()` to false. So even I use batches to do this work, I have to use `commit()` method right? – PeakGen Apr 25 '14 at 19:42
Yes. You'll want to call conn.commit() just once before you close the connection. That will be far more efficient than autocommit after each insert. There's less of an efficiency penalty to autocommit if you implement batches, but you may still want to make sure that you can recover properly from an interrupted process. If some of your batches were committed and others not, recovery might be harder. – frances Apr 25 '14 at 19:49

JDBC: OutOfMemoryError when running the `insert` query on MySQL

1 Answers1