Tuning to databases "in-memory mode"

[Innova4j]

Hi,

Currently I'm attempting to use H2 ("in-memory mode") in a high
availability system (8M records).
I'm planning to do some work of tuning on the database (example
indexes creation), but I'm not sure if the recommendations of
optimization/performance are valid for databases "in-memory mode".

Any advice for tuning a database "in-memory mode" is welcome.

Thanks.

[Thomas Mueller]

Hi,

See http://www.h2database.com/html/performance.html#database_performance_tuning
Most of the tips also apply to in-memory databases (there are
exceptions of course: Virus Scanners, Cache Size and Type).

Regards,
Thomas

[Bob McGee]

> Any advice for tuning a database "in-memory mode" is welcome.

These tips are not in the documentation yet, but will be at some
point.

For performance and compact storage, prefer the Double and Real types
rather than Decimal when not dealing with currency.

Watch your memory use -- it is entirely too easy to get an
OutOfMemoryError when working with the memory modes if the table gets
too big. There are posts in the groups on various ways to avoid
this. In general, you should limit the number of rows and size of per-
row data. To prevent memory problems, Varchar/char, binary, and LOB
types should have hard limits on precision.

My previous post on estimating memory use is not fully accurate -- I
have better information from source diving now, but hesitate to post
since the page store will change this.

Regards,
Bob McGee

[Thomas Mueller]

Hi,

I forgot about hash indexes. Those are only supported for in-memory
databases, and may improve performance for insert, update, delete, and
select. See:

http://www.h2database.com/html/grammar.html#create_index
CREATE UNIQUE HASH INDEX ...
http://www.h2database.com/html/grammar.html#create_table
create table test(id int primary key hash, name varchar)

I will add this to the documentation:

"
== In-Memory (Hash) Indexes ==

Using in-memory indexes, specially in-memory hash indexes, can speed
up queries and data manipulation.

In-memory indexes are automatically used for in-memory databases, but
can also be created for persistent databases using CREATE MEMORY
TABLE. In many cases, the rows itself will also be kept in-memory.
Please note this may cause memory problems for large tables.

In-memory hash indexes are backed by a hash table and are usually
faster than regular (tree based) indexes. However, hash indexes only
support direct lookups (WHERE ID = ?) and not range scans (WHERE ID >
? and so on). To use hash indexes, use HASH as in: CREATE UNIQUE HASH
INDEX and CREATE TABLE ...(ID INT PRIMARY KEY HASH,...).
"

Regards,
Thomas

[Sergi Vladykin]

Hola,
As I can see hash indexes can be used only for unique keys.
What about non-unique implementation? I mean holding
for each hash collection of rows. Will it give performance gains
for some scenarios? Any thoughts?

regards,
S.Vladykin

[Thomas Mueller]

Hi,

> As I can see hash indexes can be used only for unique keys.
> What about non-unique implementation? I mean holding
> for each hash collection of rows. Will it give performance gains
> for some scenarios?

An in-memory hash index for non-unique keys is possible. In theory it
should be a bit faster than the current AVL tree for larger in-memory
tables. While I don't plan to implement such an index myself, you are
welcome to implement it if you want.

Regards,
Thomas