Hi all, I have big problems after migrating vom 1.4.199 to 2.2. 229-SNAPSHOT (and other 2.x.x versions). The snapshot build uses git code from 10.03.2024.
My database has around 50.000 tables storing several 100 millions of rows. Its a kind of time series database. Data is added continously (around 1500 rows per second) and data older than 30 days is removed once a day. Apart from the fact that 2.x requires 50%-150% more CPU load compared to 1.4 (using page store) I observed that the 2.x database grows continuously and with enormous rate when removing old data (from 20GB to 90GB in 10 minutes while removing data). My parameters are: WRITE_DELAY=10000;MAX_MEMORY_ROWS=1000000;MAX_OPERATION_MEMORY=50000000;ANALYZE_AUTO=0;RETENTION_TIME=0 As long as the automatic compaction did not show nice results I decided to switch off the automatic compaction, set MAX_COMPACT_TIME to 30000 and shutdown the database each 5 minutes using SHUTDOWN. I use SHUTDOWN instead of SHUTDOWN COMPACT to get control over the maximum time while the db is not available. I observed that all of my SHUTDOWNs do not run longer than 7 seconds. The only effect is that INFO.LEAF_RATIO drops from x to 0 and the INFO.CHUNK_COUNT increases by 1. The database file size never shrinks. After 8 hours I got a database > 220GB. After running SHUTDOWN its compact relevant parameters are: INFO.CHUNKS_FILL_RATE 7 INFO.CHUNKS_FILL_RATE_RW 7 INFO.CHUNK_COUNT 86163 INFO.FILE_SIZE 223603212288 INFO.FILL_RATE 100 INFO.LEAF_RATIO 0 INFO.PAGE_COUNT 387816653 INFO.PAGE_COUNT_LIVE 51646729 INFO.PAGE_SIZE 16384 I checked MAX_COMPACT_TIME and it is 30000. Using H2 1.4.199 with PageStore and feeded with exact the same data creates a 36GB database without any SHUTDOWN. Why does the compact algorithm has no effect to the database size? Why doesn't it fully use the MAX_COMPACT_TIME setting? Why dosen't it touch the CHUNKS_FILL_RATE? Can you give an hint how to restrict the database file size? 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/3f1e1cf0-22a6-46fb-82cd-1cbf729da3e3n%40googlegroups.com.
