Re: [h2] Automatic COMPACT does not seem to work

2022-06-13 Thread Ulrich 
The manual is not clear about this:
https://www.h2database.com/javadoc/org/h2/engine/DbSettings.html#autoCompactFillRate
 


Having a look into the source code I guess it should compact while the 
database is open.

I have a 2nd project where compacting works fine. But this project only has 
rare database changes.


and...@manticore-projects.com schrieb am Dienstag, 14. Juni 2022 um 
06:02:08 UTC+2:

> Greetings.
>
> My understanding is, there is no "auto compact" while the database is 
> running.
> Instead it will be compacted only during shutdown and only  within the 
> defined shutdown time period (longer will give more time to compact).
>
> And yes, H2 database consumes a lot of file space, especially when data 
> are constantly written and deleted. I made exactly the same observation.
>
> Best regards
> Andreas
>
> On Mon, 2022-06-13 at 02:48 -0700, Ulrich wrote:
>
> Hi all,
>
> I am running a H2 2.1.212 and I wonder why the automatic compact feature 
> does not shrink the database size.
>
> My application is continuously collecting data (around 100 inserts per 
> second). Most of the data is removed once a day. The data is kept in 4300 
> tables.
>
> The database file size is around 5 GB. When running a manual SHUTDOWN 
> COMPACT it is compacted to 40 MB. 
>
> I expected that the automatic compact algorithm of the MV_STORE is able to 
> keep the database smaller than it is. Are there any known circumstances why 
> the compact algorithm does not run or does not give good results?
>
> I already tried to use the latest git code but the results are the same.
>
> Here are some infos from the information_schema.settings table with the 
> relevant parameters:
>
> AUTO_COMPACT_FILL_RATE  90 
> info.FILL_RATE  19 
> info.CHUNKS_FILL_RATE  27 
> info.CHUNKS_FILL_RATE_RW  27 
> info.FILE_SIZE  4916121600  
> info.CHUNK_COUNT 490 
> info.PAGE_COUNT  1594967 
> info.PAGE_COUNT_LIVE 440730 
> info.PAGE_SIZE  4096 
> info.CACHE_MAX_SIZE 16 
> info.CACHE_SIZE 15 
> info.CACHE_HIT_RATIO 85 
> info.TOC_CACHE_HIT_RATIO 99 
> info.LEAF_RATIO 44
>
> Thanks!
> Ulrich
>
> -- 
> 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/ae5bf92d-f61a-4cac-981a-9e1b51d7d49fn%40googlegroups.com
>  
> 
> .
>
>
>

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/594cb22c-03d2-4b61-b6cd-20492f64fd4bn%40googlegroups.com.

Re: [h2] Automatic COMPACT does not seem to work

2022-06-13 Thread Andreas Reichel 
Greetings.

My understanding is, there is no "auto compact" while the database is
running.
Instead it will be compacted only during shutdown and only  within the
defined shutdown time period (longer will give more time to compact).

And yes, H2 database consumes a lot of file space, especially when data
are constantly written and deleted. I made exactly the same
observation.

Best regards
Andreas

On Mon, 2022-06-13 at 02:48 -0700, Ulrich wrote:
> Hi all,
> 
> I am running a H2 2.1.212 and I wonder why the automatic compact
> feature does not shrink the database size.
> 
> My application is continuously collecting data (around 100 inserts
> per second). Most of the data is removed once a day. The data is kept
> in 4300 tables.
> 
> The database file size is around 5 GB. When running a manual SHUTDOWN
> COMPACT it is compacted to 40 MB. 
> 
> I expected that the automatic compact algorithm of the MV_STORE is
> able to keep the database smaller than it is. Are there any known
> circumstances why the compact algorithm does not run or does not give
> good results?
> 
> I already tried to use the latest git code but the results are the
> same.
> 
> Here are some infos from the information_schema.settings table with
> the relevant parameters:
> 
> AUTO_COMPACT_FILL_RATE  90 
> info.FILL_RATE  19 
> info.CHUNKS_FILL_RATE  27 
> info.CHUNKS_FILL_RATE_RW  27 
> info.FILE_SIZE  4916121600  
> info.CHUNK_COUNT 490 
> info.PAGE_COUNT  1594967 
> info.PAGE_COUNT_LIVE 440730 
> info.PAGE_SIZE  4096 
> info.CACHE_MAX_SIZE 16 
> info.CACHE_SIZE 15 
> info.CACHE_HIT_RATIO 85 
> info.TOC_CACHE_HIT_RATIO 99 
> info.LEAF_RATIO 44
> 
> Thanks!
> Ulrich
> -- 
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/h2-database/ae5bf92d-f61a-4cac-981a-9e1b51d7d49fn%40googlegroups.com
> .

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/9c480453482e2f1b81ddf85bedb15af8d68333ea.camel%40manticore-projects.com.

[h2] Automatic COMPACT does not seem to work

2022-06-13 Thread Ulrich 
Hi all,

I am running a H2 2.1.212 and I wonder why the automatic compact feature 
does not shrink the database size.

My application is continuously collecting data (around 100 inserts per 
second). Most of the data is removed once a day. The data is kept in 4300 
tables.

The database file size is around 5 GB. When running a manual SHUTDOWN 
COMPACT it is compacted to 40 MB. 

I expected that the automatic compact algorithm of the MV_STORE is able to 
keep the database smaller than it is. Are there any known circumstances why 
the compact algorithm does not run or does not give good results?

I already tried to use the latest git code but the results are the same.

Here are some infos from the information_schema.settings table with the 
relevant parameters:

AUTO_COMPACT_FILL_RATE  90 
info.FILL_RATE  19 
info.CHUNKS_FILL_RATE  27 
info.CHUNKS_FILL_RATE_RW  27 
info.FILE_SIZE  4916121600  
info.CHUNK_COUNT 490 
info.PAGE_COUNT  1594967 
info.PAGE_COUNT_LIVE 440730 
info.PAGE_SIZE  4096 
info.CACHE_MAX_SIZE 16 
info.CACHE_SIZE 15 
info.CACHE_HIT_RATIO 85 
info.TOC_CACHE_HIT_RATIO 99 
info.LEAF_RATIO 44

Thanks!
Ulrich

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/ae5bf92d-f61a-4cac-981a-9e1b51d7d49fn%40googlegroups.com.