OutOfMemoryError with clojure.contrib.sql and large result sets

117 views
Skip to first unread message

Ian Eure

unread,
May 14, 2009, 7:13:37 PM5/14/09
to Clojure
I'm trying to process mid/large result sets with Clojure, and not
having any success.

(ns foo
(:require [clojure.contrib.sql :as sql]))

(def *db* {:classname "com.mysql.jdbc.Driver"
:subprotocol "mysql"
:subname "//DSN"
:user "read"
:password "swordfish"})

(sql/with-connection *db*
(sql/with-query-results rs ["SELECT * FROM Foo;"]
(prn "Done")))

When I run this at the REPL, it dies with:
java.lang.OutOfMemoryError: Java heap space (NO_SOURCE_FILE:0)

I don't understand why this is OOMing. Even if it were trying to
buffer the whole result set - which I _really_ hope it's not - the
default 128mb JVM memory limit should be enough to hold it.

My dataset is 155k rows, each anywhere from 300 bytes to 1kb. It
should be 50-75mb for the whole thing. This is test data, and my full
dataset is 25m rows, so increasing the memory limit isn't a good
solution for me.

Any ideas?

Stephen C. Gilardi

unread,
May 14, 2009, 10:54:52 PM5/14/09
to clo...@googlegroups.com

On May 14, 2009, at 7:13 PM, Ian Eure wrote:

> I'm trying to process mid/large result sets with Clojure, and not
> having any success.
>
> (ns foo
> (:require [clojure.contrib.sql :as sql]))
>
> (def *db* {:classname "com.mysql.jdbc.Driver"
> :subprotocol "mysql"
> :subname "//DSN"
> :user "read"
> :password "swordfish"})
>
> (sql/with-connection *db*
> (sql/with-query-results rs ["SELECT * FROM Foo;"]
> (prn "Done")))
>
> When I run this at the REPL, it dies with:
> java.lang.OutOfMemoryError: Java heap space (NO_SOURCE_FILE:0)

I would expect the code above to use minimal memory, at most a tiny
fraction of what it would take to cache all the results. I tried a
similar experiment with Derby (based on the code in
clojure.contrib.sql.test). I used visualvm to profile the memory usage
of the read and in my experiment, the results fit the expectation.

I recommend you take a look at your setup using visualvm or yourkit or
the like. You'll be able to get more visibility into what kinds of
objects are taking up so much memory in your case.

--Steve

Ian Eure

unread,
May 15, 2009, 6:14:49 PM5/15/09
to clo...@googlegroups.com
I'm not having any luck at all. I tried visualvm, but it seems that it doesn't support memory profiling unless you're running JDK 6. I switched my default version (I'm on OS X here), and visualvm refuses to start:

2009-05-15 14:03:15.495 java[41205:10b] Apple AWT Startup Exception : *** -[NSCFArray insertObject:atIndex:]: attempt to insert nil
2009-05-15 14:03:15.496 java[41205:10b] Apple AWT Restarting Native Event Thread

I'm a Clojure newb, and I know even less about Java. What now, run it on Debian?

- Ian

Stephen C. Gilardi

unread,
May 15, 2009, 6:34:28 PM5/15/09
to clo...@googlegroups.com

On May 15, 2009, at 6:14 PM, Ian Eure wrote:

> I'm not having any luck at all. I tried visualvm, but it seems that
> it doesn't support memory profiling unless you're running JDK 6. I
> switched my default version (I'm on OS X here), and visualvm refuses
> to start:
>
> 2009-05-15 14:03:15.495 java[41205:10b] Apple AWT Startup
> Exception : *** -[NSCFArray insertObject:atIndex:]: attempt to
> insert nil
> 2009-05-15 14:03:15.496 java[41205:10b] Apple AWT Restarting Native
> Event Thread
>
> I'm a Clojure newb, and I know even less about Java. What now, run
> it on Debian?

I'm running Leopard (10.5.7) with Java 6.

I set my default Java to "Java SE 6 64-bit" for both Applications and
Applets using /Applications/Utilities/Java/Java Preferences.

Is this the Java version you have:

% java -version
java version "1.6.0_07"
Java(TM) SE Runtime Environment (build 1.6.0_07-b06-153)
Java HotSpot(TM) 64-Bit Server VM (build 1.6.0_07-b06-57, mixed mode)

I got my visualvm from

https://visualvm.dev.java.net/download.html

Version 1.1.1. I just launched it again here and it worked.

I also took a look at this issue with YourKit today. They have a free
15-day evaluation license available.

http://www.yourkit.com/download/

--Steve

Mark Addleman

unread,
May 18, 2009, 4:10:46 AM5/18/09
to Clojure
The problem is likely in the MySQL's JDBC driver. Some retrieve the
entire result set from the database on statement execute while others
are more true to the notion of a remote database cursor.

The JDBC API has a workaround for this problem: Use
Statement.setFetchSize(int) to limit the number of rows the JDBC
driver will try to retrieve from the database at any one time.

Stephen C. Gilardi

unread,
May 18, 2009, 7:53:55 PM5/18/09
to clo...@googlegroups.com

On May 18, 2009, at 4:10 AM, Mark Addleman wrote:

> The problem is likely in the MySQL's JDBC driver. Some retrieve the
> entire result set from the database on statement execute while others
> are more true to the notion of a remote database cursor.
>
> The JDBC API has a workaround for this problem: Use
> Statement.setFetchSize(int) to limit the number of rows the JDBC
> driver will try to retrieve from the database at any one time.

Thanks, Mark.

There's some interesting discussion here:

http://bugs.mysql.com/bug.php?id=18148

and here:

http://www.google.com/search?q=mysql+streaming

JDBC supports optional parameters for creating Statements. Using them,
one can communicate with the MySQL driver that the results should be
streamed rather than cached all at once.

I'm working on allowing clojure.contrib.sql users to specify these
options.

--Steve

Ian Eure

unread,
May 19, 2009, 1:11:31 PM5/19/09
to clo...@googlegroups.com
On May 15, 2009, at 3:34 PM, Stephen C. Gilardi wrote:

>
> On May 15, 2009, at 6:14 PM, Ian Eure wrote:
>
>> I'm not having any luck at all. I tried visualvm, but it seems that
>> it doesn't support memory profiling unless you're running JDK 6. I
>> switched my default version (I'm on OS X here), and visualvm
>> refuses to start:
>>
>> 2009-05-15 14:03:15.495 java[41205:10b] Apple AWT Startup
>> Exception : *** -[NSCFArray insertObject:atIndex:]: attempt to
>> insert nil
>> 2009-05-15 14:03:15.496 java[41205:10b] Apple AWT Restarting Native
>> Event Thread
>>
>> I'm a Clojure newb, and I know even less about Java. What now, run
>> it on Debian?
>
> I'm running Leopard (10.5.7) with Java 6.
>
> I set my default Java to "Java SE 6 64-bit" for both Applications
> and Applets using /Applications/Utilities/Java/Java Preferences.
>

Same here. However, it didn't update /System/Library/Frameworks/
JavaVM.framework/Versions/CurrentJDK; I fixed that by hand to point to
1.6.

I was running 10.5.6 when I tried first. I've since upgraded to
10.5.7, and there's no change.

> Is this the Java version you have:
>
> % java -version
> java version "1.6.0_07"
> Java(TM) SE Runtime Environment (build 1.6.0_07-b06-153)
> Java HotSpot(TM) 64-Bit Server VM (build 1.6.0_07-b06-57, mixed mode)
>

Yes:
$ java -version


java version "1.6.0_07"
Java(TM) SE Runtime Environment (build 1.6.0_07-b06-153)
Java HotSpot(TM) 64-Bit Server VM (build 1.6.0_07-b06-57, mixed mode)


> I got my visualvm from
>
> https://visualvm.dev.java.net/download.html
>
> Version 1.1.1. I just launched it again here and it worked.
>

Same here. Just redownloaded and verified the MD5 hash. I blew it away
and unpacked it again, thinking it might have written something to
disk which it didn't like. No luck.

Nothing Java-related in my environment, either.

- Ian

Reply all
Reply to author
Forward
0 new messages