H2/Hibernate Taking a long time to start up

[jay]

We have an application in which we are storing a lot of blobs. Each
Blob is about 500k. We are using Hibernate to do the O/R mapping for
us. It appears when the database gets somewhat sizeable (500 to 1000
of these blobs), the next time we start the application. the database
sometimes takes five minutes or so to start up. I'm not sure if it's
hibernate or H2 or the combination that is causing the problem. It
does seem to be related to the size of the database though. Any ideas
why that might be?

Jay

[tend...@gmail.com]

Sorry, it's not for the solution. I faced same situation today
and i found why my 500M DB's loading is so slow and sometimes not
working.

I analyzed the db session using TRACE_LEVEL_SYSTEM_OUT=2,
and found h2 went slow when just connecting and making a new session,
so hibernate has nothing to do with this issue.

i profiled h2 code, and found this:

MetaRecord.java:
void execute(Database db, Session systemSession, DatabaseEventListener
listener) throws SQLException {
try {
Prepared command = systemSession.prepare(sql);
command.setObjectId(id);
command.setHeadPos(headPos);
command.update(); // this line is problematic
}
}

and this is the error message from that line:
11-15 17:40:54 database: CREATE INDEX PUBLIC.SEARCH_IDX ON
PUBLIC.REPORT(TIME_TYPE, USER_ID, EVENT_DATE)
11-15 17:40:54 database: opening /h2db/data
Exception in thread "main" org.h2.jdbc.JdbcSQLException: General
error: java.lang.OutOfMemoryError: Java heap space; SQL statement:
CREATE INDEX PUBLIC.SEARCH_IDX ON PUBLIC.REPORT(TIME_TYPE, USER_ID,
EVENT_DATE) [HY000-61]
at org.h2.message.Message.getSQLException(Message.java:89)
at org.h2.message.Message.convert(Message.java:174)
at org.h2.engine.MetaRecord.execute(MetaRecord.java:70)
at org.h2.engine.Database.open(Database.java:525)
at org.h2.engine.Database.<init>(Database.java:190)
at org.h2.engine.Engine.openSession(Engine.java:50)
at org.h2.engine.Engine.getSession(Engine.java:99)
at org.h2.engine.Session.createSession(Session.java:140)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:928)
at org.h2.Driver.connect(Driver.java:52)
at java.sql.DriverManager.getConnection(DriverManager.java:
525)
at java.sql.DriverManager.getConnection(DriverManager.java:
171)
....

It seems that H2 tries to reconstruct all db structure to memory when
connecting.
And it shows some error when creating new index of 500MB table.
I don't know why, but i believe there's some solution for this issue.

thanx.

[Thomas Mueller]

Hi,

> It seems that H2 tries to reconstruct all db structure to memory when
connecting.

I guess you are not using memory tables? If yes that would be the
problem. If not, I don't know what the problem could be just now.
Could you send me the database?

To find out what takes so long, I suggest to use a Java profiler. A
simple solution is to use

java -Xrunhprof:cpu=samples,depth=8 ...

Or you could use the YourKit Java Profiler.

Thanks,
Thomas