H2 in production

[LeandroBR]

Hi.
I think h2 is a great project.
Because of that, i want to know if is recomended to use H2 Database in
production in a desktop java aplication for a simple customer
registration and storage control.

Thanks.

[Bob McGee]

Hello,

The core H2 database is stable, and already in use in a number of
production environments.

There are three things to remember when using H2 in a production
environment:

One, avoid use of the more experimental features: new page store,
jaqu, ODBC, MVCC, clustering, and the multithreading inside the
engine. These are newer features and still under development, so they
may have bugs. All are disabled by default.

Two, some features and options sacrifice safety / reliability for raw
speed, and should be used very carefully. These include the MEMORY
table type, memory databases, and running operations with logs and
undo logs off. While these features are stable, you should be very
careful about their use and test your system thoroughly before
deploying with them. Memory use for the memory tables & DBs are
particularly hard to estimate in advance, because they depend on the
JVM and settings used.

Three, all DDL statements commit the current transaction unless the
Grammar says otherwise. This wasn't specified clearly in the
documentation but will be in the future.

Regards,
Bob McGee

[Victor]

I can add fulltext search to this "avoid list". Am I not right?

[Bob McGee]

On Aug 5, 4:02 am, Victor <victor.pyan...@gmail.com> wrote:
> I can add fulltext search to this "avoid list". Am I not right?

That's a trickier one. Full-text search isn't listed as an
experimental or beta feature; however, it also had a couple bugs fixed
in the last few releases, indicating it hasn't been as fully tested.

I think fulltext search is probably okay for production use, since a
surviving bug in the the implementation is less likely to cause
catastrophic problems or data loss. It should be tested a bit before
deploying, but if it works without problems it is probably not going
to break something. If you have problems, it is pretty easy to
disable full-text without damaging anything else. You can then post
an issue report or start a new thread in this group.

The Lucene implementation is probably the more stable of the two,
since it relies on an external library that has received extensive
testing and use.

Cheers,
Bob McGee

[Ewald]

Hi.

I use H2 in two production-level applications, both scenarios where I
have replaced MySQL with H2. The one application has been running H2
for almost two years now, it's a multi-user Java application, using H2
in server mode. The speed is fantastic, it's nice and small to
distribute and of course, it's Java-based.

To date, the only issues I've had was with the default cache size
being too small, that escaped my attention for a while, but once I
sorted that out the database was fantastic. These applications are
being used on Windows, Ubuntu Linux and FreeBSD, all without a glitch.

As long as you stay away from the experimental bits, H2 is fantastic.
As a safety-measure, I use the BACKUP scripts, and also the CSVWRITER
to make sure I have copies of my data, just in the event that
something goes wrong. To date, I have not had any issues related to
H2.

Go ahead, use H2 and be blown away.

Regards
Ewald

[vipul]

Hi Bob,

What kind of applications should NOT use H2? If I have a system that
is running on SQL Server and the max size of log file is 3GB, would
you recommend to use H2 to increase performance. How does the number
of tables affect the decision.

My prototype is ready, but i want to make a logical conclusion before
I move H2 to production.

Regards,
Vipul

On Aug 5, 1:58 am, Bob McGee <buckyba...@gmail.com> wrote:
> Hello,
>
> The coreH2database is stable, and already in use in a number ofproductionenvironments.

>
> There are three things to remember when usingH2in aproduction
> environment:
>
> One, avoid use of the more experimental features: new page store,
> jaqu, ODBC, MVCC, clustering, and the multithreading inside the
> engine.  These are newer features and still under development, so they
> may have bugs.  All are disabled by default.
>
> Two, some features and options sacrifice safety / reliability for raw
> speed, and should be used very carefully.  These include the MEMORY
> table type, memory databases, and running operations with logs and
> undo logs off.  While these features are stable, you should be very
> careful about their use and test your system thoroughly before
> deploying with them.  Memory use for the memory tables & DBs are
> particularly hard to estimate in advance, because they depend on the
> JVM and settings used.
>
> Three, all DDL statements commit the current transaction unless the
> Grammar says otherwise.  This wasn't specified clearly in the
> documentation but will be in the future.
>
> Regards,
> Bob McGee
> On Aug 4, 1:23 pm, LeandroBR <leandrorc...@gmail.com> wrote:
>
>
>
> > Hi.

> > I thinkh2is a great project.

> > Because of that, i want to know if is recomended to useH2Database in

> >productionin a desktop java aplication for a simple customer

[abc6587]

I am particularly concerned with clustering. How soon will it be
production-ready? In the mean time, has anyone developed a
work-around? Say, a proxy jdbc driver? I don't care about high
availability, only about consistency of the database.

On a separate note, I'd like to have some idea of just how often a
database gets corrupt, and how soon before you see any symptoms? Is
there a sanity check process to run that can give a definite "yes" or
"no"?

[Sam Van Oort]

Vipul,
This is a complicated question, and I can only give you a general
answer. H2 is not usable for data warehousing, distributed databases,
and in general very large databases. Millions of rows or 10+ GB
databases may cause performance problems. If your app makes heavy use
of triggers or stored procedures, the java triggers & stored
procedures are clumsy to use. I don't think large numbers of tables
pose a problem (assuming we're not talking in the hundreds or
thousands), but large numbers of indices per table IS a problem for
performance.

I think it's only fair to say where H2 shines as well: it's faster,
more flexible, and easier to set up than MySQL, SQL Server, or
PostgreSQL for most applications. It is ideal for embedded
applications, local or personal databases, small to medium sized
business, and dynamic websites. The java stored procedures make it
easy to extend it with new features -- if you're feeling clever you
can use H2 for just about anything. There's something to be said for
tossing together a full database in 15 minutes hour or less. I may be
biased, since I'm willing to contribute my personal time to the
project, but I think H2 is just better-designed than a lot of the
competition.

abc6587: H2 uses checksums to verify data integrity, and is as close
to ACID transaction safety as most databases (see documentation:
http://www.h2database.com/html/advanced.html#acid )
As long as you avoid the experimental features and do not disable
integrity checks, you should NEVER see corruption of the database
itself. Users still report index corruption from time to time, but
this is easily solved by rebuilding the index, and doesn't impact
stored data.

Regards,
Sam Van Oort
(Formerly listed as Bob Mcgee for privacy reasons)

[vipul]

Hi Sam,

My app doesn't run 24 X 7, but whatever time it does run, performance
and data integrity is extremely important and there should be no loss
of data. As such I use the database only as a data store. I write all
logic in my java code. I absolutely agree that H2 suits my purpose
well. However, there are things that a production ready db should
provide like failover clustering and loadbalancing. How can I leverage
these 'must have' capabilities?

Also, May I safely assume that using connection pooling would not have
any impact on db performance. I dont know why, but I have this weird
thought that the db would lock the entire file on insert/update
operations and hence connection pooling would be useless.

[Thomas Mueller]

Hi,

> However, there are things that a production ready db should
> provide like failover clustering and loadbalancing. How can I leverage
> these 'must have' capabilities?

H2 already support a simple clustering mode, however other features
had higher priority for me. I agree the clustering needs to be
improved (specially automated recovery after a crash).

> Also, May I safely assume that using connection pooling would not have
> any impact on db performance. I dont know why, but I have this weird
> thought that the db would lock the entire file on insert/update
> operations and hence connection pooling would be useless.

For H2, connection pooling should not have an impact on performance.
Maybe you mean multi-threaded kernel that is not enabled by default?

Regards,
Thomas