Multiple pagestore h2 instances in a single VM and memory footprint

[Gerlits András]

Hi all,

I have a rather unique setup in my software where I'm running a separate h2 instance for each of my threads. I'm currently running 8 of them, 2 for each vcpu core. Each of these threads has exclusive access to these databases, so they are nice and fast. Because there is no parallel access, and because I'm very rarely updating anything in these databases, MVCC is not helpful and was measured to be about half as performant as pagestore.

So far so good. My problems started when I tried scaling these databases to multiple gigabytes each. As soon as I've done that, it started corrupting, unless I used "nioMapped". And even then, when I open any one of these databases, they are always read into memory fully, even if I put "file" in the URL. So, not just in my software, but if I open a split database of 2 gigs in a SQL console application like squirrel, it grows the amount of used memory by 2 gigabytes or throws an out of memory error.

Is there anything obvious I'm doing wrong? I understand that this is not something most people do, but I read through what I could before posting here and couldn't find anything that says anything in doing is wrong.

Any help would be very much appreciated.

Thanks,

Andras Gerlits

[Andreas Reichel]

Dear Gerlit,

unfortunately I am not a H2 developper but just an user like you.

However, my company runs several large H2 databases and we operated 15+ GB instances in the past. From this experience, my own observations are:

1) MVCC has a problem when writing large commits into indexed tables ("write amplification"). Drop the indexes before writing and you will be fine.

2) At least the latest GIT Snapshot of 2.0.201+ seems to be stable and I have not been able to corrupt a H2 DB since April this year (related to the developing H2 code, not related to the H2 user time). Although I also experience such corruptions with 1.4.200/199. But I was never able to prove it or to provide reliable test cases, so it is more like an anecdote.

Please indicate at least, what exact H2 version you are using and also the exact connection string.

3) I have never used"nioMapped", just plain Single File Access or Server Mode.

4) I can not confirm your "reads into memory" observation. I was able to open very large DBs with reasonable small Heaps, but of course only has long as the Commits are small.

5) not sure, but I believe you still can use PageStore but have to define it in the connection string and it will be incompatible with MVCC. I might be wrong on this, have not much interest in it.

Best regards

Andreas

--
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/CAHSv1fC02HtWNUT7zauag%2BJ0mF%2B4XDygO4AGODdTGD_w-LC%3Drg%40mail.gmail.com.

[Noel Grandin]

It is kind of hard to tell what is going on here, because you're trying different things and causing yourself different problems in the process.

If you are opening a lot of H2 databases, you may need to reduce the cache size, since each open database will have its own cache.

On Wed, 23 Dec 2020 at 23:47, Gerlits András <andras....@gmail.com> wrote:

unless I used "nioMapped". And even then, when I open any one of these databases, they are always read into memory fully, even if I put "file" in the URL. So, not just in my software, but if I open a split database of 2 gigs in a SQL console application like squirrel, it grows the amount of used memory by 2 gigabytes or throws an out of memory error.

"nioMapped" means using mmap() to map the file space into the memory space, so it doesn't actually "grow" the memory usage, but it does use up virtual memory space. You should ideally be monitoring the working set size, not the vmsize.

You might be seeing an out of memory error because you are trying to mmap() a large database into a 32-bit process, and running out of virtual memory. Try running a 64-bit JRE.