.mv.db file size grows large

[pishen tsai]

Hello,

I'm using Anorm (in Play framework 2.3.6) to store one table with 450000 rows in H2 (1.4.182).

The table is created by "CREATE TABLE terms (name VARCHAR(20) PRIMARY KEY, df INT)"

After frequently INSERT and UPDATE all the data into the table, the .mv.db file size grows up to 20GB.
Then, I close the JDBC connection and open it again, and the .mv.db file size drop back to 44MB.
Any idea why the file size can grow and drop so much? I have checked the content of table, and the data seems unchanged.

I'm not sure if it's the problem of transaction log, but by default, Anorm use JDBC with auto-commit, so each INSERT and UPDATE should be in one single transaction?

Anorm: https://playframework.com/documentation/2.3.x/ScalaAnorm

Thanks,
pishen

[Thomas Mueller]

Hi,

It is a known problem that the .mv.db file grows large, and I'm working on reducing the file size. However it shouldn't grow _that_ large. Could you post your test case please?

Regards,

Thomas

--
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 post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[pishen tsai]

Hi,

I have surveyed a bit and found that h2 need to compact the database when the JDBC connection is closed.
Hence I tried to close and reopen the connection every 1000~2000 INSERT/UPDATE.
Now the size of .mv.db grows much slower, but the size is still larger than 44M, maybe I have to enlarge the shutdown delay for the DB to compact more. May tried that later.

Thanks,
pishen

Thomas Mueller於 2014年12月1日星期一UTC+8下午2時50分53秒寫道：

Hi,

It is a known problem that the .mv.db file grows large, and I'm working on reducing the file size. However it shouldn't grow _that_ large. Could you post your test case please?

Regards,

Thomas

On Sunday, November 30, 2014, pishen tsai <pish...@gmail.com> wrote:

Hello,

I'm using Anorm (in Play framework 2.3.6) to store one table with 450000 rows in H2 (1.4.182).

The table is created by "CREATE TABLE terms (name VARCHAR(20) PRIMARY KEY, df INT)"

After frequently INSERT and UPDATE all the data into the table, the .mv.db file size grows up to 20GB.
Then, I close the JDBC connection and open it again, and the .mv.db file size drop back to 44MB.
Any idea why the file size can grow and drop so much? I have checked the content of table, and the data seems unchanged.

I'm not sure if it's the problem of transaction log, but by default, Anorm use JDBC with auto-commit, so each INSERT and UPDATE should be in one single transaction?

Anorm: https://playframework.com/documentation/2.3.x/ScalaAnorm

Thanks,
pishen

--
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+unsubscribe@googlegroups.com.

[Thomas Mueller]

Hi,

Could you try again with the latest version (without re-opening) please? It's probably not fully solved, but it should be much better now (maybe half the size).

Regards,

Thomas

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

[Thomas Mueller]

Hi,

Could you try again with the latest version of H2?

(Please not there is a bug that prevents index usage if you use auto-increment column; but I think you don't use this, so it shouldn't affect you).

Regards,

Thomas

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

[pishen tsai]

Thanks, I will try it in days, and will report the result after that.

Regards,

Pishen

You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/dqONbAmK9-M/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

[pishen tsai]

The .mv.db file is not growing that large (now around 200M) even without re-opening this time.

But I got an Exception that I have no idea why...

org.h2.jdbc.JdbcSQLException: General error: "java.lang.IllegalStateException: Negative position -1847 [1.4.183/6]" [50000-183]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.183.jar:1.4.183]
        at org.h2.message.DbException.get(DbException.java:168) ~[h2-1.4.183.jar:1.4.183]
        at org.h2.message.DbException.convert(DbException.java:295) ~[h2-1.4.183.jar:1.4.183]
        at org.h2.mvstore.db.MVTableEngine$1.uncaughtException(MVTableEngine.java:93) ~[h2-1.4.183.jar:1.4.183]
        at org.h2.mvstore.MVStore.writeInBackground(MVStore.java:2369) ~[h2-1.4.183.jar:1.4.183]
Caused by: java.lang.IllegalStateException: Negative position -1847 [1.4.183/6]
        at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:768) ~[h2-1.4.183.jar:1.4.183]
        at org.h2.mvstore.MVStore.readPageChunkReferences(MVStore.java:1262) ~[h2-1.4.183.jar:1.4.183]
        at org.h2.mvstore.MVStore.collectReferencedChunks(MVStore.java:1240) ~[h2-1.4.183.jar:1.4.183]
        at org.h2.mvstore.MVStore.collectReferencedChunks(MVStore.java:1244) ~[h2-1.4.183.jar:1.4.183]
        at org.h2.mvstore.MVStore.collectReferencedChunks(MVStore.java:1244) ~[h2-1.4.183.jar:1.4.183]

Regards,

Pishen

[Thomas Mueller]

Hi,

Is this exception reproducible with version 1.4.184, with a fresh database? If yes, would it be possible to send me or post a reproducible test case?

Regards,

Thomas

[pishen tsai]

Sorry, the exception only happens when closing a connection with a large amount (114508) of MERGE queries executed, and I couldn't post the test case for you.
The table only contains three columns as "name VARCHAR PRIMARY KEY, df INT, category_counts VARCHAR"

This is reproducible in both 1.4.183 and 1.4.184.

In 1.4.184 I got
org.h2.jdbc.JdbcSQLException: General error: "java.lang.IllegalStateException: Negative position -1593 [1.4.184/6]" [50000-184]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.184.jar:1.4.184]
        at org.h2.message.DbException.get(DbException.java:168) ~[h2-1.4.184.jar:1.4.184]
        at org.h2.message.DbException.convert(DbException.java:295) ~[h2-1.4.184.jar:1.4.184]
        at org.h2.mvstore.db.MVTableEngine$1.uncaughtException(MVTableEngine.java:93) ~[h2-1.4.184.jar:1.4.184]
        at org.h2.mvstore.MVStore.writeInBackground(MVStore.java:2374) ~[h2-1.4.184.jar:1.4.184]
Caused by: java.lang.IllegalStateException: Negative position -1593 [1.4.184/6]
        at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:773) ~[h2-1.4.184.jar:1.4.184]
        at org.h2.mvstore.MVStore.readPageChunkReferences(MVStore.java:1267) ~[h2-1.4.184.jar:1.4.184]
        at org.h2.mvstore.MVStore.collectReferencedChunks(MVStore.java:1245) ~[h2-1.4.184.jar:1.4.184]
        at org.h2.mvstore.MVStore.collectReferencedChunks(MVStore.java:1249) ~[h2-1.4.184.jar:1.4.184]
        at org.h2.mvstore.MVStore.collectReferencedChunks(MVStore.java:1249) ~[h2-1.4.184.jar:1.4.184]

I'm falling back to 1.4.182 now and I found that the .mv.db file is no longer that large (around 500M), not sure if I suddenly change anything.

Regards,

Pishen

[Thomas Mueller]

Hi,

I think I found and fixed this problem now. The fix is in the trunk. I also have a test case to reproduce the issue. From the stack trace it's relatively clear it is the same issue.

Regards,

Thomas

[sim]

Hi,

I have been testing a real life application where use H2 1.4.186 with mvstore on. The application 
receives about 60 concurrent requests per minute, updates corresponding fields in the rows and reply.
 It takes approximately 12-18 ms to handle one request. My plan is to increase the number of requests
up to 2-3 thousands. Everything works nice, but I have a question about reusing of the unused space in the db. Clear, that in a such of the application db is never closed and so never compacted, but it should reuse the unused space. Now, every day db grows up to 20-30 Mb and I have to compact it manually(~2Mb after compacting), so what is the db configuration to prevent it from uncontrolled growing? Thank you. 

[Thomas Mueller]

Hi,

Yes, there is the "retention_time" setting. Did you try that already? I would be interested how much this helps in your case.

Regards,

Thomas

--

[sim]

Hi Thomas,

Does this setting work when db is open?  On my observations db reduces its size when closed,
and in my case it is open all the time. Or am I mistaken?

[Thomas Mueller]

Hi,

See the documentation at http://h2database.com/html/grammar.html#set_retention_time

> Does this setting work when db is open?

Yes

> On my observations db reduces its size when closed

It should ensure that the database file doesn't grow as large, as less old data is kept.

> in my case it is open all the time

That's fine.

Regards,

Thomas

[sim]

Thomas, thank you for the advice. I ran lots of tests and found out that db stops to grow if retention_time
not more than 30000. Thank you very much indeed.

Regards,
Sergei

On Wednesday, March 18, 2015 at 8:47:16 AM UTC+2, Thomas Mueller wrote:

Hi,

See the documentation at http://h2database.com/html/grammar.html#set_retention_time

> Does this setting work when db is open?

Yes

> On my observations db reduces its size when closed

It should ensure that the database file doesn't grow as large, as less old data is kept.

> in my case it is open all the time

That's fine.

Regards,

Thomas

On Tuesday, March 17, 2015, sim <sim...@mail.ru> wrote:

Hi Thomas,

Does this setting work when db is open?  On my observations db reduces its size when closed,
and in my case it is open all the time. Or am I mistaken?
 

On Tuesday, March 17, 2015 at 7:05:57 PM UTC+2, Thomas Mueller wrote:

Hi,

Yes, there is the "retention_time" setting. Did you try that already? I would be interested how much this helps in your case.

Regards,

Thomas

--
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+unsubscribe@googlegroups.com.