DB file size grows in an unexpected size (H2 version 1.4)

[Jürgen Pingel]

Hello,

we detect that the DB file size grows in an unexpected size. 

Following scenario:

 we have one DB file with ~30 tables where only ~10 tables regulary get changed (add, update and delete)

Before open the DB we log out the file size of the DB - mostly it was ~ 1GB.

But at some point the open DB grows up to ~5 GB - and for sure we don’t add so much data.

Does anybody know why that could happen?

Also we notice the following exception in the trace log file if we close the database:

org.h2.jdbc.JdbcSQLNonTransientException: Allgemeiner Fehler: "Allgemeiner Fehler: ""java.lang.IllegalStateException: File corrupt reading chunk at position 236937216 [1.4.200/6]""

General error: ""java.lang.IllegalStateException: File corrupt reading chunk at position 236937216 [1.4.200/6]"" [50000-200]"

General error: "Allgemeiner Fehler: ""java.lang.IllegalStateException: File corrupt reading chunk at position 236937216 [1.4.200/6]""

General error: ""java.lang.IllegalStateException: File corrupt reading chunk at position 236937216 [1.4.200/6]"" [50000-200]"; SQL statement:

shutdown [50000-200]

        at org.h2.engine.Database.throwLastBackgroundException(Database.java:2221)

        at org.h2.engine.Session.close(Session.java:945)

        at org.h2.engine.Session.suspend(Session.java:935)

        at org.h2.engine.Database.closeAllSessionsExcept(Database.java:1325)

        at org.h2.engine.Database.setExclusiveSession(Database.java:2571)

        at org.h2.command.dml.TransactionCommand.update(TransactionCommand.java:89)

        at org.h2.command.CommandContainer.update(CommandContainer.java:198)

        at org.h2.command.Command.executeUpdate(Command.java:251)

        at org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:168)

        at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:126)

What happen there in H2 if such happen? Could this explain why the file grows up in that way?

Thanks for any help,

  Jürgen

[Andreas Reichel]

Hi Juergen,

while I am just a regular user, but not a developer, I have had a similar discussion a few months ago and I understood that there may be a problem with "Write Amplification" for large transactions on indexed tables (any DML affecting many records). 

The advice given was to break such large DMLs into smaller chunks, which I did not find easy since there is no "RETURNING" facility in H2.

Alternatively you would have to remove indexes, execute your DML and create indexes afresh.

Good luck and cheers

Andreas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/4d99b80e-10ee-4f21-92b9-aaca08934a57n%40googlegroups.com.

[Andreas Reichel]

Just to cheer you up: I just turned a 2 GByte database into a 15 GByte Database -- by running a "DELETE" (!) statement on a heavily indexed table.

The good news is: the latest 2.2.214 does not break/corrupt as easily as the 1.2.xxx line did when interrupting such statements hard (killing the VM).

 Cheers

Andreas