Database Stability Issues

[Jason]

I currently use H2 for a logging/monitoring application which runs
24x7 and I am experiencing two issues:

1) The size of the database constantly grows, and once the size hits
around 250MB it refuses to accept new connections. If the database is
shut down and re-opened the size of the database shrinks and it
becomes responsive again.

2) My second issue is that of a long start up time. One sites h2
database takes about one hour to open. I enabled logging and it
appears that the database is replaying every transaction since it was
created. I see lots of lines like the following:

11-23 17:35:16 pageStore: log redo - table:175 key:34191721
...
11-23 17:41:19 pageStore: updateRecord page[89022] b-tree leaf table:
13 entries:17
11-23 17:41:20 index: T10_DATA remove ( /* key:5977917 */ 5977917,
'0102D2918A90517BB06', 'Intel(R) ICH10 Family USB Universal Host
Controller - 3A68', 'Universal Serial Bus controllers', '9.0.0.1005',
'Intel', '2-25-2008', 'usbuhci.sys', 'Enabled')
11-23 17:41:20 pageStore: log redo - table:10 key:5977918

My theory is that these two issues have something to do with MVCC. The
documentation states that if one enables MVCC then the transaction log
must fit in memory. Is memory here the heap space? What about disk?
One of my theories here is that the database is constantly growing as
a result of the transaction log being persisted in addition to the
data.

[Noel Grandin]

Both of those problems sound like one of more of the connections are not calling commit() after inserting data into the
database, holding the transaction open.

[Jason]

I have found a temporary fix to the long start up time: simply connect
to the database with MVCC off. This action seems to clear the
transaction log after an hour or two. Any re-opening of the database
after this initial connection will be nice and quick. In theory one
could connect with MVCC on, but even with my heap set to 1GB my test
application was running out of memory because it was loading the
entire transaction log into memory.

I am still curious on how this log gets so large. I attempted to
reproduce the issue with a mini example and observed that if there is
an INSERT with no commit, it is automatically rolled back if:
* The client application exists (I am guessing that the rollback is
part of the connection cleanup in the database server).
* The database is shut down gracefully. There appears to be a shutdown
hook that rolls back uncommitted transactions on shutdown.

The only way I was able to get some items into the transaction log by
doing a kill -9 on the H2 server process while INSERTS were being
executed.

I also had suspicions similar to Noels. My first suspicion was there
there might also be a connection leak, I also examined the code
looking for missed commit statements but have yet to find any. The
exception handling in my clients persistence code is not the best, so
perhaps some more research will turn up some conditions where a commit
or rollback would be missed.

[Noel Grandin]

Also note that the mvcc option in h2 is still in beta, so there might still be bugs lurking in edge cases.

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>
>

[zing]

I'm also having trouble with long startup times - but the earlier
comments about starting with MVCC off doesn't make work for me.
According to the H2 documentation, MVCC is off by default, and I've
never turned it on.

How can I truncate the log without starting the database over from
scratch?

[Noel Grandin]

a long startup time is also something that would happen if a connection is holding a transaction open for a long time.

the trace options might help you find this:
http://www.h2database.com/html/features.html#trace_options

[andreis]

> How can I truncate the log without starting the database over from> scratch?

CHECKPOINT http://www.h2database.com/html/grammar.html#checkpoint and
probably http://www.h2database.com/html/grammar.html#set_max_log_size
At least in my case, when time to create an index is about 50sec and
every index creation stays in the log, recovery after crash took too
long time. But issuing 'CHECKPOINT' fixed that.

[Thomas Mueller]

Hi,

> 2) My second issue is that of a long start up time

I guess there is a connection with an open transaction (an uncommitted change) that is blocking the transaction log to shrink. Or multiple connections. This would also explain why it gets slower over time. To find out, you could use:

    jps -l (to get the process id)
    jmap -histo <pid>

This will list the live objects. The live connections are JdbcConnection.

Then you can use the trace feature to find out which connection stays open. To do that, append ;trace_level_file=3 to the database URL - all JDBC API calls are then logged in the .trace.db file.

The database actually "knows" which connection (well... session) has an open transaction. Possibly it should log the session id for such cases. I will add a feature request: "Long running transactions: log session id when detected." - But using jmap -histo and "trace_level_file=3" should solve the problem as well.

> The documentation states that if one enables MVCC then the 

> transaction log must fit in memory.

Only the undo log (the list of uncommitted changes).

Checkpoint shouldn't be required (it is automatic). If you have a simple, standalone test case where opening a database takes a very long time please tell me.

Regards,
Thomas