huge data-set results in exception: java.lang.OutOfMemoryError: GC overhead limit exceeded

[GoogleCodeExporter]

Dear Thomas, Dear List.

I would like to report the successful use of H2-Database as default RDBMS in 
manticore-trader, which is an open source financial trading system. For 
reference please see http://www.manticore-projects.com
H2-database provides me with very good performance on small data-sets (e.g. 
tick-data of several days, 30'000 rows). Thank you for providing this great 
software!

However as soon as I store huge data-sets (e.g. tick-data of two years, 4 Mill. 
rows) I can not retrieve even small sub-sets (e.g. tick-data of two years). The 
program will respond incredible slow and eat up all the ram until an error will 
occur:

Exception in thread "Timer-2" java.lang.OutOfMemoryError: GC overhead limit 
exceeded
    at org.h2.store.Data.readValue(Data.java:678)
    at org.h2.index.PageBtreeIndex.readRow(PageBtreeIndex.java:340)
    at org.h2.index.PageBtree.getRow(PageBtree.java:174)
    at org.h2.index.PageBtreeCursor.next(PageBtreeCursor.java:64)
    at org.h2.index.IndexCursor.next(IndexCursor.java:224)
    at org.h2.table.TableFilter.next(TableFilter.java:340)
    at org.h2.command.dml.Select.queryGroup(Select.java:307)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:574)
    at org.h2.command.dml.Query.query(Query.java:241)
    at org.h2.command.CommandContainer.query(CommandContainer.java:80)
    at org.h2.command.Command.executeQuery(Command.java:132)
    at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:76)
    at com.manticore.database.Quotes.getLastTimeStamp(Unknown Source)
    at com.manticore.database.Quotes.importTickData(Unknown Source)
    at com.manticore.database.Quotes.importFromUrl1(Unknown Source)
    at com.manticore.database.Quotes.importInstrumentTickData(Unknown Source)
    at com.manticore.database.Quotes.importInstrumentTickData(Unknown Source)
    at com.manticore.database.TickDataTimerTask.run(Unknown Source)
    at java.util.TimerThread.mainLoop(Timer.java:512)
    at java.util.TimerThread.run(Timer.java:462)

The query is plain simple:

select t1.id_instrument, t1.id_stock_exchange, t1."timestamp", t1.price, 
coalesce(t2.quantity,0) quantity from trader.tickdata t1 left join 
trader.volumedata t2 ON (    t1.id_instrument=t2.id_instrument AND 
t1.id_stock_exchange=t2.id_stock_exchange AND  t1."timestamp"=t2."timestamp" ) 
where t1.id_instrument=1 AND t1.id_stock_exchange=22 AND 
t1."timestamp">='2010-09-23 08:00:00'  AND t1."timestamp"<='2010-09-24 
22:00:00' ORDER BY "timestamp" ASC;

The machine I am using has 4 GB of RAM, more the 2 GB are free just for the 
database. It is a 32-bit linux system with sun-jdk 1.6.0_21-b06 and 
h2-1.2.136.jar.
I am using Postgresql as substitute which handles exactly the same situation 
very well (answers within 1 second). I also played around with some options, 
when calling java (-Xincg -Xmx1200m) but nothing helped.

So I would like to ask you for support. Whatever information you need I will 
provide of course.

Thank you in advance and best regards
Andreas

Original issue reported on code.google.com by andreas....@gmail.com on 24 Sep 2010 at 4:09

[GoogleCodeExporter]

Hi,

What is your database URL? Do you use any special settings (for example cache 
size)? 

Do you use views, and if yes, what are the view definitions? If not, could you 
verify this is really the statement that fails (the stack trace says it's a 
GROUP BY query that failed, the the query you specified isn't).

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 27 Sep 2010 at 5:04

[GoogleCodeExporter]

Hi Thomas,

thanks for the reply.
No specific parameters in the connection string, it is just plain 

jdbc:h2:file:${user.home}manticore-trader2

Also the mentioned query is the last query called before I get the exception. 
It is also the query, which relates to the huge data amount.

Best regards
Andreas

Original comment by andreas....@gmail.com on 1 Oct 2010 at 4:05

[GoogleCodeExporter]

The stack trace you posted can't happen with the query you posted, unless if 
one of the tables in the query is a view that contains an aggregate function or 
"group by". The stack trace contains Select.queryGroup which is never called 
for a query that doesn't contain "group by" or an aggregate function. Your 
query doesn't.

Please check your method 
com.manticore.database.Quotes.getLastTimeStamp(Unknown Source)

Original comment by thomas.t...@gmail.com on 1 Oct 2010 at 5:48

[GoogleCodeExporter]

thanks for the hint. This method looks like this:

 public Date getLastTimeStamp(long id_instrument, long id_stock_exchange) {
        Date last = null;

        try {
            Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            String sqlstr = " select " + " max(\"timestamp\") " + " from trader.tickdata " + " where id_instrument=" + id_instrument + " and id_stock_exchange=" + id_stock_exchange + " ; ";
            ResultSet rs = statement.executeQuery(sqlstr);
            if (rs.next()) {
                last = rs.getTimestamp(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Quotes.class.getName()).log(Level.SEVERE, null, ex);
        }

        return last;
    }

You see, no grouping there, but an aggregate function. Still I do not 
understand what is the matter with this? Looks sane for me.

Original comment by andreas....@gmail.com on 1 Oct 2010 at 2:07

[GoogleCodeExporter]

Hi,

Probably only very little memory was available when running this query. That 
means this query doesn't actually use much memory. Probably the problem was the 
query before that (a result set that is still open).

Therefore, I suggest to first find out what component actually uses up almost 
all memory. To do that, you need to analyze your application. See also 
http://h2database.com/html/build.html#support "For out-of-memory problems, 
please analyze the problem yourself first, for example using the command line 
option -XX:+HeapDumpOnOutOfMemoryError and a memory analysis tool such as the 
Eclipse Memory Analyzer (MAT)."

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 4 Oct 2010 at 6:01

[GoogleCodeExporter]

Thomas,

thank you very much for help, I really appreciate.
Still I wonder why postgresql handles exactly the same load without any hick-up 
using never more than 100 MByte of memory.

The problem definitely is in h2 as none of the query RETURN more then 10'000 
rows, while it has to search for several million rows. So it is IN the 
database, not related to the application.

As I said: I just switched between different databases which have identical 
content (same schema, same data).

The best!
Andreas

Original comment by andreas....@gmail.com on 5 Oct 2010 at 1:14

[GoogleCodeExporter]

Most likely PostgreSQL creates a temporary table for the problematic query. H2 
doesn't always create temporary tables in this case, for example it doesn't for 
DISTINCT queries. However, there is a feature to do that. To use it, you need 
to set the system property h2.maxMemoryRowsDistinct, for example to 10000. See 
also 
http://www.h2database.com/javadoc/org/h2/constant/SysProperties.html#h2.maxMemor
yRowsDistinct

I suggest to first find out what component actually uses up almost all memory. 
To do that, you need to analyze your application. See also 
http://h2database.com/html/build.html#support "For out-of-memory problems, 
please analyze the problem yourself first, for example using the command line 
option -XX:+HeapDumpOnOutOfMemoryError and a memory analysis tool such as the 
Eclipse Memory Analyzer (MAT)."

Original comment by thomas.t...@gmail.com on 7 Oct 2010 at 9:23

[GoogleCodeExporter]

Could you try if H2 version 1.3.146 (todays release) solves the problem?

Original comment by thomas.t...@gmail.com on 8 Nov 2010 at 9:57

[GoogleCodeExporter]

Could you try if H2 version 1.3.148 (todays release) solves the problem?
Please add a comment if not.

Original comment by thomas.t...@gmail.com on 12 Dec 2010 at 12:04

• Changed state: Fixed