1.4.188 complete shutdown still required to reduce database size...
142 views
Skip to first unread message
Kenton Garner
Aug 24, 2015, 2:08:17 PM8/24/15
to H2 Database
(moved to new thread for clarity)
Thomas,
I hate to keep coming back to this... But I have built the most recent version 1.4.188 and I have been doing more testing in regards to the continual growth of the database due to the temporary LOB references.
In my case, my database file seems to continue to grow even with your new LOB_TIMEOUT value;
I am continuing to issue the following statements every 10 minutes when the database file exceeds 50MB
However it rarely helps to reduce the db size.
In my most recent test the database file had reached 500MB.
After 30+ minutes of no db activity ( just active connections and the pseudo compact code from above / 10 mins. ) the db size did not change.
After closing the database connections ( and ultimately the database itself )... it took about a minute for the database to completely close and remove the lock file and then the file size reduced 300MB.
When I restarted the database and performed the pseudo compact code above it shrunk to 300K.
This behavior does not seem to correlate well with your comments in regards to the new LOB_TIMEOUT value and what I would have expected.
It still seems like a complete shutdown is required to reduce the file size.
At the risk of beating this subject to death - do you have any thoughts on this?
For the most part, It is the only issue and customer complaint that I get from my users concerning the H2 database implementation that I am using.
With my configuration "AUTO_SERVER=TRUE" I cannot find a way to ever cleanly close the database and reduce the database size. I need to be able to do this on the fly - with connections active.
My current connection URL:
"jdbc:h2:audit/msgfwd;COMPRESS=TRUE;AUTOCOMMIT=FALSE;TRACE_LEVEL_FILE=4;lock_timeout=60000;MVCC=FALSE;DB_CLOSE_ON_EXIT=TRUE;MV_STORE=TRUE;LOB_TIMEOUT=10000;AUTO_SERVER=TRUE;AUTO_SERVER_PORT=9999"
-Kent
Thomas,
I hate to keep coming back to this... But I have built the most recent version 1.4.188 and I have been doing more testing in regards to the continual growth of the database due to the temporary LOB references.
In my case, my database file seems to continue to grow even with your new LOB_TIMEOUT value;
I am continuing to issue the following statements every 10 minutes when the database file exceeds 50MB
SET EXCLUSIVE 1
SET RETENTION_TIME 0
CHECKPOINT SYNC
CHECKPOINT SYNC
SET RETENTION_TIME 45000
SET EXCLUSIVE 0
COMMIT
However it rarely helps to reduce the db size.
In my most recent test the database file had reached 500MB.
After 30+ minutes of no db activity ( just active connections and the pseudo compact code from above / 10 mins. ) the db size did not change.
After closing the database connections ( and ultimately the database itself )... it took about a minute for the database to completely close and remove the lock file and then the file size reduced 300MB.
When I restarted the database and performed the pseudo compact code above it shrunk to 300K.
This behavior does not seem to correlate well with your comments in regards to the new LOB_TIMEOUT value and what I would have expected.
It still seems like a complete shutdown is required to reduce the file size.
At the risk of beating this subject to death - do you have any thoughts on this?
For the most part, It is the only issue and customer complaint that I get from my users concerning the H2 database implementation that I am using.
With my configuration "AUTO_SERVER=TRUE" I cannot find a way to ever cleanly close the database and reduce the database size. I need to be able to do this on the fly - with connections active.
My current connection URL:
"jdbc:h2:audit/msgfwd;COMPRESS=TRUE;AUTOCOMMIT=FALSE;TRACE_LEVEL_FILE=4;lock_timeout=60000;MVCC=FALSE;DB_CLOSE_ON_EXIT=TRUE;MV_STORE=TRUE;LOB_TIMEOUT=10000;AUTO_SERVER=TRUE;AUTO_SERVER_PORT=9999"
-Kent
Thomas Mueller
Aug 27, 2015, 1:48:23 AM8/27/15
to h2-da...@googlegroups.com
Hi,
Could you post a simple, reproducible test base please? It sounds like a problem with LOBs.
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.
Thomas Mueller
Sep 1, 2015, 1:48:27 AM9/1/15
to h2-da...@googlegroups.com
Hi,
A simple, reproducible test case would be great!
Regards,
Thomas
--
Kenton Garner
Sep 1, 2015, 8:29:32 AM9/1/15
to h2-da...@googlegroups.com
Sorry, I have not had time to create the test case yet. My environment is the complete opposite of simple/easy - so I will have to work on that for you.
--
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/-qrmdC8T02Q/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.
Thomas Mueller
Oct 7, 2015, 1:47:32 AM10/7/15
to H2 Google Group
Hi,
This problem should now be fixed in the trunk. If you have time, could you test using the latest automated build, at http://h2database.com/html/build.html#automated ?
Regards,
Thomas
Kenton Garner
Oct 7, 2015, 9:09:46 AM10/7/15
to h2-da...@googlegroups.com
Thomas,
I have run a couple of tests and I think you have indeed fixed it.
I will keep testing, but for now I would just like to thank you so much for all of your effort.
For me this has been a huge issue that I think can now be laid to rest.
Thanks again,
Kent
Reply all
Reply to author
Forward
0 new messages