Closed GoogleCodeExporter closed 9 years ago
GoogleCodeExporter
commented
9 years ago 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,
ThomasOriginal comment by thomas.t...@gmail.com on 27 Sep 2010 at 5:04
GoogleCodeExporter
commented
9 years ago 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
commented
9 years ago 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
commented
9 years ago 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
commented
9 years ago 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,
ThomasOriginal comment by thomas.t...@gmail.com on 4 Oct 2010 at 6:01
GoogleCodeExporter
commented
9 years ago 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!
AndreasOriginal comment by andreas....@gmail.com on 5 Oct 2010 at 1:14
GoogleCodeExporter
commented
9 years ago 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
commented
9 years ago 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
commented
9 years ago 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
Original issue reported on code.google.com by
andreas....@gmail.comon 24 Sep 2010 at 4:09