In-Memory H2 deleted rows not garbage collected.

[Arjun Sahoo]

Hi,

 I am using Spring boot In-Memory H2DB. I am storing latest 100k records in the database, so keep on deleting old records and inserting new records. Application runs fine for 4-5 hours, after that it throws OOM Exception. The memory consumption keeps on increasing even after deleting old records. I took the thread dump and analysed in MAT, it shows one memory leak.

One instance of org.h2.mvstore.tx.TransactionStore$TxMapBuilder$TMVMap loaded by org.springframework.boot.loader.LaunchedURLClassLoader @ 0x6c0737918 occupies 2,08,58,61,856 (77.95%) bytes. The memory is accumulated in one instance of org.h2.mvstore.Page$PageReference[], loaded by org.springframework.boot.loader.LaunchedURLClassLoader @ 0x6c0737918, which occupies 2,08,58,61,384 (77.95%) bytes.

When I restart the application with same number of records in H2db, it takes only 30% of memory.

H2db Version: 2.1.212

Spring boot: 2.7.1

Spring data JPA:2.7.1

spring.datasource.url=jdbc:h2:mem:db;DB_CLOSE_ON_EXIT=FALSE;DATABASE_TO_LOWER=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE;

[Noel Grandin]

check that all your transactions are being closed. Holding a transaction open means that the storage cannot be freed.

 

[Arjun Sahoo]

I am using spring data jpa to delete the records.

@Modifying
@Transactional
@Query(value = "delete from table where start_time_ms <= :startTimeMs", nativeQuery = true)
Integer deleteInBulkByStartTime(@Param("startTimeMs") Long startTimeMs) throws SQLException;

 Spring takes care of  managing the transaction.

[Noel Grandin]

I don't have any obvious answers.

You can try 2 things. 

First, try building H2 from git master and seeing if the changes since the last official release help.

If that doesn't help, we're going to need a standalone test case (no Spring, etc) to reproduce the problem and then we might be able to fix it.

[Andreas Reichel]

Noel,

question for better understanding please: Would a MEM database not grow exactly as a File backed database keeps growing forever?

Or in other words, is there any difference between a MEMORY database and File backed at TEMP FS?

I ask because all my file backed H2 databases keep growing and never release all filespace until I export to file, create new and compress. So I would expect the same (mis-) behavior for any memory DB.

Setting up a reliable test should be easy: Create a DB, populate with an indexed table and data and run queries continuously for a longer time. Check if size peaks or grows forever.

Cheers

Andreas

[Noel Grandin]

(1) That is not expected behaviour, my H2 databases grow to a point and then stabilise (and I run my H2 databases pretty hard)

(2) in-memory is slightly different, we have a couple of places where we do things different because we know it's a memory database

[Arjun Sahoo]

Look at the above charts, how memory usage is increasing over the time, even if database has less records. In the beginning Db has 100k records and the memory consumption is around 50%. As data gets ingested and deleted , over the time the memory usage increases even if more rows gets deleted. Before throwing OOM error, the database has 30k rows and the memory usage is 96%.

The table has 30 fields, one of them is a  clob field which contains 100k-300k long characters.

Reproducing the test case is easy, keep on ingesting and deleting records, you will see the memory usage increasing over time. If if query the table simultaneously , you will reach the trend faster.

[Andreas Reichel]

Greetings!

On Sun, 2023-05-21 at 23:42 -0700, Arjun Sahoo wrote:

Reproducing the test case is easy, keep on ingesting and deleting records, you will see the memory usage increasing over time. If if query the table simultaneously , you will reach the trend faster.

You will need to write a self containing unit test simulating this, Only this way people will help ypu.

Best regards

Andreas

[Andrei Tokar]

And to confirm that problem is still there, please run it against at least latest released version (2.1.214), or even better against current git build.

I have that feeling that you just run queries with LOB in the result which causes extensive LOB cloning. Try to add ;LOB_TIMEOUT=1000 (1 sec instead of default 5 min)

.

[Arjun Sahoo]

The problem still exists in 2.1.214 and with LOB_TIMEOUT=1000. The issue could not be reproduced with the  current git build, but we can't use any build other than official release. May I know when the next official release is?.

--
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/84e85db2-fc01-4e92-a4d2-6e162ec3a30bn%40googlegroups.com.

--

With Regards,
             Arjun Sahoo