Issue with Java Heap Space and H2 Database

[Mahima Chaubey]

Hello , 

We are encountering frequent Java heap space issues approximately every 1 1/2 hours after restarting the service. By the end of the day, our H2 database accumulates between 50 to 60 lakh (5 to 6 million) rows. However, the database starts becoming unresponsive when it reaches only 15 to 20 lakh (1.5 to 2 million) rows.

Service Functionality Overview: Our service fetches data from a third-party API and stores it in the H2 database throughout the day. This process is repeated frequently. Ideally, our setup should handle this workload seamlessly. However, after fetching data around 4 to 5 times (approximately 15 lakh records), we start encountering Java heap space errors. Even simple operations like querying the count(*) become impossible.

We have tried increasing the server's CPU cores and memory allocation, but the issue persists.

Request for Assistance: We suspect there might be a memory leak issue with H2 database causing these problems. Could you please advise on:

• Steps to diagnose and mitigate potential memory leaks in H2 database?
• Best practices or configurations to optimise H2 database performance with large datasets?

Any guidance or support you could provide would be greatly appreciated. Thank you for your attention to this matter.

Best regards,
Mahima

..

[Andrei Tokar]

Hi Mahima,

You did not even mention, if you are usin in-memory or persistent database, your connection URL should be the first thing to post. Database version would be helpful too.

If you suspect memory leak - H2 is just another java library, so follow standard best practices to detect JVM memory leaks (profiler, etc). If you increase java heap size and it did not change a thing - that sounds suspicious, at least time to reach unresponsive state should increase. When you say "optimize for large datasets", what does it mean exactly? 1.5 - 2 million rows does not sound like a lot, unless your table is very wide or there are blobs involved. What else, besides H2 exists in the same process?
I doubt, that without answers to those questions anyone would be able to give you any kind of a meaningful advice.