4

I have a database with +8million records which I need to processes in particular ways which is written in Java. After looking some stuff up, I found following related posts:

This is my code that returns the items stored in the column Tags of my MySQL database:

public ResultSet getAllTags() {
    String query = "SELECT Tags FROM dataset";
    ResultSet rs = null;

    try {
        connection = ConnectionFactory.getConnection(DATABASE);
        preparedStatement = connection.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        preparedStatement.setFetchSize(Integer.MIN_VALUE);
        rs = preparedStatement.executeQuery(query);
        // following line is for testing, to see what comes out of the resultset
        System.out.println("output: " + rs.getString(1));
        return rs;
    } catch (Exception ex) {
        ex.printStackTrace();
        return null;
    } finally {
        closeAll();
    }
}

Here I return the ResultSet in order for me to process each line in the rs.next() loop. However, at the line of rs = preparedStatement.executeQuery(query); it starts to eat all my computer's free memory (I work on Mac OSX with 8GB of RAM. With only Eclipse open I have +/- 5GB left free, when running application it goes down till < 100MB free) making me to shut down the database connection and application etc... So I assume this can be called a Memory Leakage?

Can someone explain what I'm doing wrong and why this issue occurs even when I follow the instructions found on other pages with similar amount of records?

Community
  • 1
  • 1
RazorAlliance192
  • 722
  • 9
  • 23

2 Answers2

5

The only thing you're doing wrong is to use a stupid database driver (MySQL), which by default reads the whole resultset in memory.

Try using the useCursorFetch and defaultFetchSize properties described in http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html to avoid that, and you should be able to iterate through the rows without fetching everything in memory (not tested though).

Note that the line

System.out.println("output: " + rs.getString(1));

will throw an exception, since you haven't called next() yet in the result set. Also note that, if closeAll() closes the connection, the caller won't be able to iterate through the result set, since it will be closed. You should execute the iteration before closing the connection.

Note that the documentation of the driver says:

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and cannot allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

To enable this functionality, create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
          java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

But you're used TYPE_SCROLL_SENSITIVE and not TYPE_FORWARD_ONLY.

Community
  • 1
  • 1
JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • I will have a deeper look on this. I am using Mac OS X and the database tool Sequel Pro which is for MySQL. Maybe you can recommend me an equivalent databse tool like Sequel Pro which uses a smarter database driver? – RazorAlliance192 Mar 22 '14 at 12:25
  • Isn't it also wrong to return the `ResultSet`? This depends on the caller having to remember to close it. And if that is forgotten, memory leaks happen. Also, to be able to close the `ResultSet`, the `Connection` should be open. I may be wrong in the case of _disconnected_ result sets. – adarshr Mar 22 '14 at 12:28
  • @adarshr: that's why I said: *You should execute the iteration before closing the connection.*. – JB Nizet Mar 22 '14 at 12:29
  • @RazorAlliance192: I don't think there is any other driver for MySQL. The driver has nothing to do with the tool you use to browse your database. – JB Nizet Mar 22 '14 at 12:30
  • Thanks this helped! But indeed, I need to rethink my implemenation strategy so I don't have to return the resultset – RazorAlliance192 Mar 22 '14 at 13:09
1

Did you consider positive values for fetch sizes, 256/512/1024/2048. I would expect setting a negative value for fetch size having no effect, however this could vary by driver implementation and you should verify the actual behavior in the driver documentation.

    public void setFetchSize(int rows) throws SQLException {
    synchronized (checkClosed().getConnectionMutex()) {
        if (((rows < 0) && (rows != Integer.MIN_VALUE))
                || ((this.maxRows != 0) && (this.maxRows != -1) && (rows > this
                        .getMaxRows()))) {
            throw SQLError.createSQLException(
                    Messages.getString("Statement.7"), //$NON-NLS-1$
                    SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); //$NON-NLS-1$ //$NON-NLS-2$
        }

        this.fetchSize = rows;
    }
}
Bhaskar
  • 594
  • 2
  • 11