java.lang.OutOfMemoryError JDBC update

In this tutorial we show a code example, how you can avoid the java.lang.OutOfMemoryError when selecting or updating a huge database.

1. Increase the memory of the VM

If you are sure that you have enough RAM to process the whole database, you can try to assign more memory to the Java Virtual Machine.

1. Pass the following parameters when starting the JVM, where Xms is the minimum and Xmx the maximum available heap memory (k = kilobyte, m = megabyte, g = gigabyte):

   -Xms256m 
   -Xmx8g

2. If you are using eclipse, you can change this per default in the eclipse.ini file located in the root folder of your Eclipse installation:

   -startup
   plugins/org.eclipse.equinox.launcher_1.3.0.v20140415-2008.jar
   --launcher.library
   plugins/org.eclipse.equinox.launcher.win32.win32.x86_64_1.1.200.v20150204-1316
   -product
   org.eclipse.epp.package.jee.product
   --launcher.defaultAction
   openFile
   --launcher.XXMaxPermSize
   256M
   -showsplash
   org.eclipse.platform
   --launcher.XXMaxPermSize
   256m
   --launcher.defaultAction
   openFile
   --launcher.appendVmargs
   -vmargs
   -Dosgi.requiredJavaVersion=1.6
   -Xms40m
   -Xmx512m

You can use Runtime.totalMemory() to get the size of the JVM during runtime. This returns the total amount of memory in the JVM in bytes.

Remember that the JVM uses more memory than just the heap. E.g. Java methods, thread stacks, native handles and JVM internal data structures are allocated in a memory seperated from the heap.

2. Database size exceeds the internal memory

If you adapted the JVM memory parameters and still run into the java.lang.OutOfMemoryError exception, you can try to use the following trick. Selecting a certain amount of rows without explicitly doing so programmatically. These rows can be read, updated or deleted. We used this method on a database with about 100 GB size and the JVM memory allocation never exceeded 800 MB:

public int updateAll( Connection conn ) throws SQLException 
{
	Statement stmt = null;
	int maxLength = 20;
	int affectedRows = 0;
    
	// no autocommit for TYPE_FORWARD_ONLY
	conn.setAutoCommit(false);
	
	stmt = conn.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE );
	stmt.setFetchSize( 500 );
	
	ResultSet uprs = stmt.executeQuery( "SELECT * FROM bigDataTable" );
	
	while ( uprs.next() )
	{
	    String label = uprs.getString( "bigDataEvent" );
	
		if( label.length() > maxLength )
		{
			String newLabel = label.substring( 0, maxLength );
			
			uprs.updateString( "bigDataEvent", newLabel );
			uprs.updateRow();
			affectedRows++;
		}
	}
	    
	uprs.close();
	
	if (stmt != null)
	{
		stmt.close(); 
	}
	
	// no autocommit so dont forget to commit in the end
	conn.commit();
	    
	return affectedRows;
}

You can use the DBConnector class from here to retrieve a DB connection. This particular example reduces the amount of characters in unstructered event data to a fixed number (the first 20 in the example).

This code fetches 500 rows at once. After manipulating the event column, we update and write the new data back.

You can adapt the rows to fetch and adjust the performance vs memory workload.

If you have problems or questions, feel free to comment and ask.