MySQL Java - java.lang.OutOfMemoryError

Author

java.lang.OutOfMemoryError - LONG (with test case)

Przemysław Klein

2006-02-25, 9:41 am

Hi All.

This test case tries to perform following steps:
1. connect to database
2. create table with 9 columns - col1 int, col2 varchar(255), col3 text,
col4 int, col5 varchar(255), col6 text, col7 int, col8 varchar(255),
col9 text
3. populate it with 200 rows - same values
('1','2','3','4','5'
,'6','7','8','9')
4. try to retrieve 'col1' from every row 300*100 times
5. disconnect from database

Unfortunately it throws java.lang.OutOfMemoryError during step 4. As you
can see, we don't store retrieved data, so we don't consume any memory.
We run this test under Borland OptimizeIt Profiler and ito shows that
99,9% memory consumes mysql connector.

MySQL server: mysql Ver 14.7 Distrib 4.1.10, for pc-linux-gnu (i686)
jdbc: mysql-connector-java-3.1.12-bin.jar

Thanks for any help,
Przemek

------------------------------------------------------------------------

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
* MySQL JDBC connector test. This test causes:<br>
* <code>Exception in thread "main" java.lang.OutOfMemoryError: Java
heap space</code>
*
* @author Oskar Sawicki
*
*/
public class MySQLTest {

private final static String DB_NAME = "test";

private final static String DB_USER = "root";

private final static String DB_PASSWD = "";

private final static String TABLE_NAME = "test";

/**
* Main method. Makes connection and executes testing query.
*
* @param args
* @throws SQLException
* @throws ClassNotFoundExcepti
on
* @throws InstantiationExcepti
on
* @throws IllegalAccessExcepti
on
*/
public static void main(String[] args) throws SQLException,
ClassNotFoundExcepti
on, InstantiationExcepti
on,
IllegalAccessExcepti
on {

String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost/" + DB_NAME + "?user=" +
DB_USER
+ "&password=" + DB_PASSWD
+ "&useUnicode=true& characterEncoding=la
tin2";

Class.forName(driver).newInstance();

Connection conn = DriverManager.getConnection(url);

String sql = "drop table if exists " + TABLE_NAME + ";";
Statement stmt = conn.createStatement();
stmt.execute(sql);

sql = "CREATE TABLE "
+ TABLE_NAME
+ " (col1 int, col2 varchar(255), col3 text, col4 int,
col5 varchar(255), col6 text, col7 int, col8 varchar(255), col9 text)";
stmt = conn.createStatement();
stmt.execute(sql);

for (int i = 0; i <= 200; i++) {
sql = "insert into "
+ TABLE_NAME
+ " values ('1','2','3','4','5'
,'6','7','8','9')";
stmt = conn.createStatement();
stmt.execute(sql);
}
for (int i = 0; i <= 300; i++) {
System.out.println("iteration: 100*" + i);
System.out.flush();

for (int j = 0; j <= 100; j++) {
sql = "SELECT * FROM " + TABLE_NAME + ";";
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
rs.getString("col1");
}
}
}

conn.close();
}
}

--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe: http://lists.mysql.com/java?unsub=m...sie.nctu.edu.tw

Mark Matthews

2006-02-25, 9:41 am

Przemysław Klein wrote:
> Hi All.
>
> This test case tries to perform following steps:
> 1. connect to database
> 2. create table with 9 columns - col1 int, col2 varchar(255), col3 text,
> col4 int, col5 varchar(255), col6 text, col7 int, col8 varchar(255),
> col9 text
> 3. populate it with 200 rows - same values
> ('1','2','3','4','5'
,'6','7','8','9')
> 4. try to retrieve 'col1' from every row 300*100 times
> 5. disconnect from database
>
> Unfortunately it throws java.lang.OutOfMemoryError during step 4. As you
> can see, we don't store retrieved data, so we don't consume any memory.
> We run this test under Borland OptimizeIt Profiler and ito shows that
> 99,9% memory consumes mysql connector.
>
> MySQL server: mysql Ver 14.7 Distrib 4.1.10, for pc-linux-gnu (i686)
> jdbc: mysql-connector-java-3.1.12-bin.jar
>
> Thanks for any help,
> Przemek
[snip]

> for (int j = 0; j <= 100; j++) {
> sql = "SELECT * FROM " + TABLE_NAME + ";";
> stmt = conn.createStatement();
> ResultSet rs = stmt.executeQuery(sql);
> while (rs.next()) {
> rs.getString("col1");
> }
> }
> }

Przemysław,

You're creating result sets and statements here that don't ever get
closed, and won't be GC'd until you close the connection (or run out of
memory, in your case).

There's no reason to create thousands of statements, one will be fine,
create it before you enter the loop. Close the result sets in a finally
block. (basically your code has created a memory leak because it's not
respecting the lifecycle of JDBC objects).

Your code should look something like this:

try {
stmt = conn.createStatement();

for (int j = 0; j <= 100; j++) {
sql = "SELECT * FROM " + TABLE_NAME + ";";

ResultSet rs = null;

try {
rs = stmt.executeQuery(sql);
while (rs.next()) {
rs.getString("col1");
}
} finally {
if (rs != null) {
rs.close();
}
}
}
}
} finally {
if (stmt != null)
stmt.close();
}

-Mark

--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe: http://lists.mysql.com/java?unsub=m...sie.nctu.edu.tw

Przemysław Klein

2006-02-25, 9:41 am

Thanks A LOT for answers. It was a great lesson for me:)

Przemek

Mark Matthews wrote:

> Przemysław Klein wrote:
>
>
> [snip]
>
>
>
> Przemysław,
>
> You're creating result sets and statements here that don't ever get
> closed, and won't be GC'd until you close the connection (or run out
> of memory, in your case).
>
> There's no reason to create thousands of statements, one will be fine,
> create it before you enter the loop. Close the result sets in a
> finally block. (basically your code has created a memory leak because
> it's not respecting the lifecycle of JDBC objects).
>
> Your code should look something like this:
>
> try {
> stmt = conn.createStatement();
>
> for (int j = 0; j <= 100; j++) {
> sql = "SELECT * FROM " + TABLE_NAME + ";";
>
> ResultSet rs = null;
>
> try {
> rs = stmt.executeQuery(sql);
> while (rs.next()) {
> rs.getString("col1");
> }
> } finally {
> if (rs != null) {
> rs.close();
> }
> }
> }
> }
> } finally {
> if (stmt != null)
> stmt.close();
> }
>
>
> -Mark
>

--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe: http://lists.mysql.com/java?unsub=m...sie.nctu.edu.tw

Sponsored Links