FYI, we were using 1.4.184 together with a JDK 1.8.0u11, on a CentOS 6, and
the database kept growing by chunks of ~ 10 MB. We have upgraded to JDK
1.8.0u31, and although the file keeps growing, it is now by tiny chunks of
kilobytes. So much better. I just hope there is some kind of runtime
cleanup going on, because it would still go over 1.0 GB in 30 days :(
Is there a way to launch some SQL commands at runtime to make the database
shrink?
Damien.
On Wednesday, 4 February 2015 23:01:07 UTC+1, Max Lord wrote:
>
> I also had problems with an mvstore db growing out of control (about 3GB
> for 1M rows). I updated to 1.4.184, reimported, and it was much smaller
> (100MB).
>
> So the recent changes have had a very positive effect.
>
> Unfortunately, I was using 1.4.178 because that was the current jar
> bundled with the jdbc-h2 ruby gem. That version doesn't seem like such a
> good default.
>
> On Monday, February 2, 2015 at 2:42:28 PM UTC-5, Damien Coraboeuf wrote:
>>
>> I have replaced by BLOB columns by BINARY(32000) ones (more than enough
>> in our case). After exporting the database in SQL ('script' command),
>> recreating a blank database and reimporting the SQL ('runscript'), I went
>> from 1.7 Gb to 17 Mb.
>>
>> I'll monitor the database in the next days to see if the inflation starts
>> again.
>>
>> Damien.
>>
>> On Monday, 2 February 2015 17:40:49 UTC+1, Damien Coraboeuf wrote:
>>>
>>> Hi,
>>>
>>> Speaking of real world example - we are using H2 1.4.x to hold results
>>> for a continuous delivery chain. With 1.4.177, our database was > 600 Mb,
>>> and after a 'shutdown defrag', we went down to... 11 Mb. We switched to
>>> 1.4.184 but now, the database has grown up to 1.7 Gb. That's a serious
>>> issue for us :(
>>>
>>> The URL we use is:
>>>
>>> jdbc:h2:/opt/ontrack/database/data;MODE=MYSQL
>>>
>>> Damien.
>>>
>>> On Monday, 5 January 2015 18:15:56 UTC+1, Thomas Mueller wrote:
>>>>
>>>> Hi,
>>>>
>>>> OK, that's nice! There is still quite a lot of room for improvements,
>>>> and I don't consider this completely fixed, but will not work on it with
>>>> very high priority any longer.
>>>>
>>>> Regards,
>>>> Thomas
>>>>
>>>>
>>>> On Sunday, December 21, 2014, Steve McLeod <[email protected]> wrote:
>>>>
>>>>> Hi Thomas,
>>>>>
>>>>> The database file size in 1.4.184 is much, much better than in earlier
>>>>> 1.4.x releases.
>>>>>
>>>>> I've done some trials and these are my findings:
>>>>>
>>>>> 1.3.176: Fully loaded database after shutdown is 317 Mb
>>>>> 1.4.184: Fully loaded database after shutdown is 380 Mb
>>>>>
>>>>> This seems reasonable.
>>>>>
>>>>>
>>>>> On Friday, 19 December 2014 17:15:29 UTC+8, Thomas Mueller wrote:
>>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> Version 1.4.184 should produce smaller database files than previous
>>>>>> version (1.4.x - 1.4.182), maybe half or a third of the old file size.
>>>>>> It
>>>>>> would be great to get some real-world results!
>>>>>>
>>>>>> Regards,
>>>>>> Thomas
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Tue, May 6, 2014 at 6:24 PM, Thomas Mueller <
>>>>>> [email protected]> wrote:
>>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> Some initial results: you can shrink the database by running
>>>>>>> "shutdown compact" or "shutdown defrag". Each time this is run, it
>>>>>>> shrinks
>>>>>>> a few MB (up to some point, of course). This works, but it's relatively
>>>>>>> slow. Now the task is to make it faster. There are two ways: shrink it
>>>>>>> fully to the minimum size, and shrink it incrementally (like now) but
>>>>>>> faster. I'm working on that now.
>>>>>>>
>>>>>>> Regards,
>>>>>>> Thomas
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Tue, May 6, 2014 at 11:39 AM, Steve McLeod <[email protected]>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Hi Thomas,
>>>>>>>>
>>>>>>>> I've sent you a private email with a link to the new database file,
>>>>>>>> made with H2 1.4.178
>>>>>>>>
>>>>>>>> Regards,
>>>>>>>>
>>>>>>>> Steve
>>>>>>>>
>>>>>>>>
>>>>>>>> On Monday, 5 May 2014 07:46:16 UTC+2, Thomas Mueller wrote:
>>>>>>>>
>>>>>>>>> Hi,
>>>>>>>>>
>>>>>>>>> The database file should shrink if you run "shutdown defrag".
>>>>>>>>>
>>>>>>>>> The current compact algorithm is quite inefficient, that means the
>>>>>>>>> databases file is quite big on average. The highest priority is still
>>>>>>>>> to
>>>>>>>>> ensure it always works correctly, and when that's done I will work on
>>>>>>>>> more
>>>>>>>>> efficiently re-using disk space and specially compact the file faster
>>>>>>>>> when
>>>>>>>>> closing the database.
>>>>>>>>>
>>>>>>>>> Could you send me the new database file? It would be nice to have
>>>>>>>>> a real-world database file to test this. The last file you sent
>>>>>>>>> helped a
>>>>>>>>> lot, thanks to it I found some problems that completely prevented the
>>>>>>>>> file
>>>>>>>>> to shrink.
>>>>>>>>>
>>>>>>>>> Regards,
>>>>>>>>> Thomas
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Sunday, May 4, 2014, Steve McLeod <[email protected]> wrote:
>>>>>>>>>
>>>>>>>>>> Hi Thomas,
>>>>>>>>>>
>>>>>>>>>> I tested the same large data import with H2 1.4.178, and there is
>>>>>>>>>> no improvement over H2 1.4.177.
>>>>>>>>>>
>>>>>>>>>> Here are the file sizes, in both cases after the app has stopped:
>>>>>>>>>>
>>>>>>>>>> H2 1.3.176: pokercopilot.h2.db 301,669,352 bytes
>>>>>>>>>> H2 1.4.178: pokercopilot.mv.db 1,023,037,440 bytes
>>>>>>>>>>
>>>>>>>>>> Let me know what I can do to help.
>>>>>>>>>>
>>>>>>>>>> Regards,
>>>>>>>>>>
>>>>>>>>>> Steve
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On Saturday, 19 April 2014 11:44:05 UTC+2, Steve McLeod wrote:
>>>>>>>>>>
>>>>>>>>>> Hi Thomas,
>>>>>>>>>>
>>>>>>>>>> Great! Glad I could help make your superb product even better.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On Friday, 18 April 2014 21:38:27 UTC+2, Thomas Mueller wrote:
>>>>>>>>>>
>>>>>>>>>> Hi,
>>>>>>>>>>
>>>>>>>>>> Thanks a lot for the database! I know what the problem is now,
>>>>>>>>>> but I couldn't fix it yet. The database file (pokercopilot2.mv.db)
>>>>>>>>>> has
>>>>>>>>>> about 181 MB of "live" data, the rest (about 78%) is not used. The
>>>>>>>>>> mechanism to get rid of the unused space is not working as it should
>>>>>>>>>> for
>>>>>>>>>> this case (I think the problem is that b-tree nodes are not
>>>>>>>>>> processed
>>>>>>>>>> correctly). This will be fixed in the next release.
>>>>>>>>>>
>>>>>>>>>> Regards,
>>>>>>>>>> Thomas
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On Fri, Apr 18, 2014 at 5:29 PM, Steve McLeod <
>>>>>>>>>> [email protected]> wrote:
>>>>>>>>>>
>>>>>>>>>> Hi Thomas,
>>>>>>>>>>
>>>>>>>>>> I've sent a link to file privately to your email address.
>>>>>>>>>>
>>>>>>>>>> Regards,
>>>>>>>>>>
>>>>>>>>>> Steve
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote:
>>>>>>>>>>
>>>>>>>>>> Hi,
>>>>>>>>>>
>>>>>>>>>> Hm, that didn't help much. Could you send me the (compressed)
>>>>>>>>>> database files please? If it's too big, what is the compressed size
>>>>>>>>>> of the
>>>>>>>>>> files?
>>>>>>>>>>
>>>>>>>>>> Regards,
>>>>>>>>>> Thomas
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod <
>>>>>>>>>> [email protected]> wrote:
>>>>>>>>>>
>>>>>>>>>> Hi Thomas,
>>>>>>>>>>
>>>>>>>>>> Thanks for the suggestion. I tried adding ";retention_time=1000"
>>>>>>>>>> to the URL, and this resulted in a small improvement.
>>>>>>>>>>
>>>>>>>>>> pokercopilot.h2.db 302,018,560 bytes
>>>>>>>>>> pokercopilot.mv.db 999,120,896 bytes
>>>>>>>>>> pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes
>>>>>>>>>>
>>>>>>>>>> These numbers all reflect a loading of data in a newly created
>>>>>>>>>> database that consisted of roughly 2,400,000 INSERTS and UPDATES
>>>>>>>>>> with plenty of SELECTS and almost no DELETES. After the loading was
>>>>>>>>>> complete, I let the application keep running with the database open
>>>>>>>>>> for a
>>>>>>>>>> few minutes, then close the application and therefore the database.
>>>>>>>>>>
>>>>>>>>>> Here is the full JDBC url I'm using:
>>>>>>>>>> jdbc:h2:/Users/steve/Library/Application
>>>>>>>>>> Support/com.barbarysoftware.pokercopilot/database/pokercopil
>>>>>>>>>> ot;DATABASE_EVENT_LISTENER='com.barbarysoftware.pokercopilot
>>>>>>>>>> .database.DatabaseListener';COMPRESS_LOB=DEFLATE;CACHE_SIZE=
>>>>>>>>>> 65536;RETENTION_TIME=1000
>>>>>>>>>>
>>>>>>>>>> Let me know if there is anything else I can do to help diagnose
>>>>>>>>>> this.
>>>>>>>>>>
>>>>>>>>>> Regards,
>>>>>>>>>>
>>>>>>>>>> Steve
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Muel
>>>>>>>>>>
>>>>>>>>>> --
>>>>>>>>>> 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 [email protected].
>>>>>>>>>> To post to this group, send email to [email protected]
>>>>>>>>>> .
>>>>>>>>>> Visit this group at http://groups.google.com/group/h2-database.
>>>>>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>>>>>
>>>>>>>>> --
>>>>>>>> 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 [email protected].
>>>>>>>> To post to this group, send email to [email protected].
>>>>>>>> Visit this group at http://groups.google.com/group/h2-database.
>>>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>>>
>>>>>>>
>>>>>>> --
>>>>> 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 [email protected].
>>>>> To post to this group, send email to [email protected].
>>>>> Visit this group at http://groups.google.com/group/h2-database.
>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>
>>>>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
