Working H2 DB with low memory (File DB vs In Memory DB)

[vsaji]

Hi,

I am trying implement a solution using H2 DB where it is expected to
load more than 6 to 7 Million records into the DB.
I am able to load this volume using mem and file option ie.
"jdbc:h2:mem:<databaseName>" and "jdbc:h2:file:<databaseName>",
however, I see both these options consumes very high memory and no big
different.

I would like to achieve this loading process with less memory usage. I
assume file based storage should resolve my issue.

Can someone help me how to do this with enabling some options.

Sorry, if this is a duplicate post.

Cheers
Saji

[vsaji]

Hi Thomas,

Any luck on my question?
Appreciate you response.

Saji

[Noel Grandin]

Importing help is here:

http://www.h2database.com/html/performance.html#fast_import

[vsaji]

Hi Noel,

Thanks for your response.
I did try the options stated in the URL, however, if I trace the
memory consumption of the process, it is still taking more memory
(Close to 2GB for loading 1million records with 6 columns) . What I am
not able to understand is, why file mode <jdbc:h2:file> consuming such
high memory.

My program is simple and it populates record into H2 copying from a
Oracle.

Any inputs ?

Regards
Saji

[Noel Grandin]

how are you doing the copy?
Are you committing periodically, or only after the load is complete?
If you commit only once, you are going to use a lot of memory.
You need to be committing every 1000 rows or so.

[vsaji]

Hi Noel,

I tried your suggestion but not much luck.
Let me put my question this way. If I want to load 1million records
(with 10 cols) and avg record size 500 byte using file method. How
much memory is expected to be consumed.
I am trying to achieve using 1GB,is it possible ? if yes, how should
my jdbc url look like.

From this url, I found the following information. But it doesn't seems
to be working.
http://www.h2database.com/html/advanced.html?highlight=split&search=%3Asplit#limits_limitations

"Main memory requirements: The larger the database, the more main
memory is required. With the version 1.1 storage mechanism, the
minimum main memory required for a 12 GB database was around 240 MB.
With the current page store, the minimum main memory required is much
lower, around 1 MB for each 8 GB database file size"

Pleas help.

Saji

[Noel Grandin]

Java will typically consume as much memory as there is available on the system.
If you want it reduce it's memory usage, then you need to explicitly tell it to limit itself.

For example
java -XMx=250M
will tell java to limit itself to less then 250M.

[vsaji]

Hi Noel,

The issue I am trying to explain here is, even 1GB Max heap is failing
when I try to load 1Million Record.

So is this is an expected behavior with file mode ? Since the data is
getting written into the file, the loading process should not take
much memory.

Please clarify.

Cheers
Saji

[vsaji]

Hi Noel,

The issue I am trying to explain here is, the loading process is
failing even on 1GB heap size.

So is this an expected behavior with file mode, If the H2 is
configured for file mode, ideally the data is getting written into the
file, so the memory usage should be less.

What my programme does.
1) Fetching records from Oracle Data base
2) Inserting into H2 DB in batch mode, batch size is 500.
3) End of every batch I commit the transaction.

Please clarify,

Saji

[Noel Grandin]

That shouldn't be the case when in file mode, the loading process should not require much memory at all.
Do you have a simplified test case?

[Noel Grandin]

Are you sure the problem is H2?

Maybe try using the NetBeans memory profiler
Or the Eclipse Memory Profilter
(both free)

to find out where the memory is going.

-- Noel.

[Ryan How]

You could try running H2 in server mode, and then connect to that using
your import program. That way it shouldn't use any extra memory in your
"client" program. Then you can tell if it is H2 or something in the
import program that is causing the issue. It will be a bit slower, but
might be helpful for debugging.

Ryan