Java-Gaming.org    
Featured games (79)
games approved by the League of Dukes
Games in Showcase (477)
Games in Android Showcase (106)
games submitted by our members
Games in WIP (533)
games currently in development
News: Read the Java Gaming Resources, or peek at the official Java tutorials
 
    Home     Help   Search   Login   Register   
Pages: [1]
  ignore  |  Print  
  OOME on MySQL query before using the ResultSet  (Read 2063 times)
0 Members and 1 Guest are viewing this topic.
Online Riven
« League of Dukes »

JGO Overlord


Medals: 743
Projects: 4
Exp: 16 years


Hand over your head.


« Posted 2008-09-09 19:21:52 »

I have this simple table, with lots of rows (well... about 400.000) with less than 100 bytes per row.

So I connect to the database, create a Statement, and call Statement.executeQuery(String)

1  
2  
3  
4  
5  
6  
7  
8  
9  
public static void main(String[] args) throws Exception
{
   Class.forName("com.mysql.jdbc.Driver");
   java.sql.Connection con = DriverManager.getConnection(args[0], args[1], args[2]);
   String sql = "SELECT `id` FROM `SomeTable` WHERE `something` <> 'something else'";
   Statement stmt = sqlCon.createStatement();
   stmt.setFetchSize(64);
   ResultSet result = stmt.executeQuery(sql);
}



After a few seconds, I get the infamous OOME:
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
        at com.mysql.jdbc.Buffer.getBytes(Buffer.java:198)
        at com.mysql.jdbc.Buffer.readLenByteArray(Buffer.java:318)
        at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1375)
        at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2369)
        at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:451)
        at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2076)
        at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1451)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1787)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3206)
        at com.mysql.jdbc.Statement.executeQuery(Statement.java:1232)
        at touchetracks.Launcher.main(Launcher.java:16)


As you can see, the code crashes at executeQuery(...) so I'm not even travering the ResultSet.
It seems like it's loading all rows into RAM, before returning.

When I add a condition, to reduce the amount of rows in the ResultSet, the problem goes away - but I need almost the whole table in this case.


Can anybody be kind enough to point me at the obvious thing I forgot?

Hi, appreciate more people! Σ ♥ = ¾
Learn how to award medals... and work your way up the social rankings
Offline cylab

JGO Ninja


Medals: 38



« Reply #1 - Posted 2008-09-09 20:21:23 »

The MySQL jdbc driver is quite bad with this things. I am afraid you have to use "LIMIT [offset,] row_count" in your statement and traverse the chunks yourself.

Mathias - I Know What [you] Did Last Summer!
Online Riven
« League of Dukes »

JGO Overlord


Medals: 743
Projects: 4
Exp: 16 years


Hand over your head.


« Reply #2 - Posted 2008-09-09 20:31:45 »

 Shocked

Using LIMITs is easy, but kinda 'blegh' from a technical point of view (and... how big should the chunk size be).

Ensuring memory usage will be low (it's a Virtual Private Server so free memory is low), forces me to use a fairly small chunk size which is (probably) much slower than 1 query.



Can I connect to a MySQL database through another driver, or shouldn't I even bother...

Hi, appreciate more people! Σ ♥ = ¾
Learn how to award medals... and work your way up the social rankings
Games published by our own members! Check 'em out!
Legends of Yore - The Casual Retro Roguelike
Online Riven
« League of Dukes »

JGO Overlord


Medals: 743
Projects: 4
Exp: 16 years


Hand over your head.


« Reply #3 - Posted 2008-09-09 21:37:47 »

Great... these is an "ORDER BY" clause in my query, so that makes every query with a LIMIT roughly as fast at the original query (without LIMIT)

So splitting my query over N limits, makes execution time grow by facter N.

Nice way to kill your server!


*grmbl*

Hi, appreciate more people! Σ ♥ = ¾
Learn how to award medals... and work your way up the social rankings
Online Riven
« League of Dukes »

JGO Overlord


Medals: 743
Projects: 4
Exp: 16 years


Hand over your head.


« Reply #4 - Posted 2008-09-09 21:52:47 »

Hurray for Google...

1  
2  
3  
4  
         Statement stmt = con.createStatement();
         stmt.setFetchSize(Integer.MIN_VALUE);  <-------
         ResultSet result = stmt.executeQuery(sql);
         return SQLUtil.grabIterator(stmt, result);


... did the job.

Hi, appreciate more people! Σ ♥ = ¾
Learn how to award medals... and work your way up the social rankings
Offline erikd

JGO Ninja


Medals: 16
Projects: 4
Exp: 14 years


Maximumisness


« Reply #5 - Posted 2008-09-09 21:57:18 »

Hehe, I was *just* about to post the very same thing  Grin

Online Riven
« League of Dukes »

JGO Overlord


Medals: 743
Projects: 4
Exp: 16 years


Hand over your head.


« Reply #6 - Posted 2008-09-09 22:40:51 »

Seems like I'm not the only one doing raw SQL in Java, in this world of JEE and at least a dozen abstraction layers.


Well the workaround is not that good, streaming rows this way is rather slow, it seems to fetch 1 row at a time...

Hi, appreciate more people! Σ ♥ = ¾
Learn how to award medals... and work your way up the social rankings
Offline erikd

JGO Ninja


Medals: 16
Projects: 4
Exp: 14 years


Maximumisness


« Reply #7 - Posted 2008-09-09 23:17:55 »

Have you tried this:

"When using Connector/J 5.0.1 along with more recent builds of the MySQL server, you can
add "useCursorFetch=true" to your JDBC url parameters, and the driver will fetch rows in
batches of size setFetchSize() as defined in the JDBC API."

Online Riven
« League of Dukes »

JGO Overlord


Medals: 743
Projects: 4
Exp: 16 years


Hand over your head.


« Reply #8 - Posted 2008-09-09 23:41:56 »

{no-parameters}64k rows fetchOOME
{no-parameters}MIN_VALUE rows fetch27s
{no-parameters}1 row fetchOOME
---------------------------------------------------------------------------------------------
useCursorFetch=true64k rows fetch41s
useCursorFetch=trueMIN_VALUE rows fetch24s
useCursorFetch=true1 row fetch65s

Hi, appreciate more people! Σ ♥ = ¾
Learn how to award medals... and work your way up the social rankings
Pages: [1]
  ignore  |  Print  
 
 
You cannot reply to this message, because it is very, very old.

 

Add your game by posting it in the WIP section,
or publish it in Showcase.

The first screenshot will be displayed as a thumbnail.

pw (22 views)
2014-07-24 01:59:36

Riven (21 views)
2014-07-23 21:16:32

Riven (17 views)
2014-07-23 21:07:15

Riven (20 views)
2014-07-23 20:56:16

ctomni231 (48 views)
2014-07-18 06:55:21

Zero Volt (44 views)
2014-07-17 23:47:54

danieldean (35 views)
2014-07-17 23:41:23

MustardPeter (38 views)
2014-07-16 23:30:00

Cero (53 views)
2014-07-16 00:42:17

Riven (53 views)
2014-07-14 18:02:53
HotSpot Options
by dleskov
2014-07-08 03:59:08

Java and Game Development Tutorials
by SwordsMiner
2014-06-14 00:58:24

Java and Game Development Tutorials
by SwordsMiner
2014-06-14 00:47:22

How do I start Java Game Development?
by ra4king
2014-05-17 11:13:37

HotSpot Options
by Roquen
2014-05-15 09:59:54

HotSpot Options
by Roquen
2014-05-06 15:03:10

Escape Analysis
by Roquen
2014-04-29 22:16:43

Experimental Toys
by Roquen
2014-04-28 13:24:22
java-gaming.org is not responsible for the content posted by its members, including references to external websites, and other references that may or may not have a relation with our primarily gaming and game production oriented community. inquiries and complaints can be sent via email to the info‑account of the company managing the website of java‑gaming.org
Powered by MySQL Powered by PHP Powered by SMF 1.1.18 | SMF © 2013, Simple Machines | Managed by Enhanced Four Valid XHTML 1.0! Valid CSS!