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.
