Benchmark for h2, hypersonic, derby

1,778 views
Skip to first unread message

Brish

unread,
Sep 26, 2007, 1:20:29 AM9/26/07
to H2 Database
>From the website:
"It will not be easy for the developers of Derby to improve the
performance to a reasonable level."

Sounds like a challenge! ;-)

I don't develop derby but I thought it might be entertaining to see if
I could improve the performance of derby with the h2 benchmarks.

I only focused on the embedded databases.

Results from website
h2: 16312
derby: 131704

The first thing I did was run the benchmarks with the latest version
of h2, and derby. My results were a bit different than the posted
results.

Results summary
h2: 17844
derby: 85531

I looked at some of the code to see what was going on and I noticed
that for a lot of the benchmarks that autocommit mode was on. I made a
slight modification so that openConnection automatically turns
autocommit mode off. I also made it so that closeConnection does a
commit.

Autocommit = false summary
h2: 17063
derby: 66077

The next thing I noticed was the h2 wasn't using durable operations.
There is a special mode in derby to allow a weaker durability setting
(it still writes more frequently than h2). I just added this before
any of the benchmarks where ran:

System.setProperty("derby.system.durability", "test");

Durability off summary
h2: 18796
derby: 45499

Results for all the embedded databases (more recent versions of all
databases with my minor changes to the benchmarks):
h2: 17437
hypersonic: 37469
derby: 44704

If I knew more about derby I'm sure I could get it running faster.

Brish

Thomas Mueller

unread,
Sep 26, 2007, 1:39:45 PM9/26/07
to h2-da...@googlegroups.com
Hi,

Thanks for your mail!

> Sounds like a challenge! ;-)

Sure!

> I don't develop derby but I thought it might be entertaining to see if
> I could improve the performance of derby with the h2 benchmarks.

That's good! So far nobody from Derby took the time to see why Derby is slow.

> I only focused on the embedded databases.

I think that's the main usage of H2 and Derby.

> The first thing I did was run the benchmarks with the latest version
> of h2, and derby. My results were a bit different than the posted
> results.

Yes, I also found out the newest version of Derby is faster. The new
results will be posted in the next release.

> Results summary: h2: 17844, derby: 85531

On my machine, I got: H2: 14687, Derby: 87111; see also:
http://h2database.googlecode.com/svn/trunk/h2/src/docsrc/html/performance.html

> a lot of the benchmarks that autocommit mode was on.

Yes, that's what I expect most people use.

> I made a slight modification so that openConnection automatically turns
> autocommit mode off. I also made it so that closeConnection does a
> commit.
> Autocommit = false summary; h2: 17063; derby: 66077

While I don't think that this is a valid use case for a database, I
will add this to the Derby documentation:

"A few problems have been identified: Leaving autocommit on is a
problem for Derby. If it is switched off during the whole test, the
results are about 20% better for Derby."

> The next thing I noticed was the h2 wasn't using durable operations.

Yes, Derby does not use durable operations as well. Derby uses
RandomAccessFile(.., "rws" or "rwd"), this is not durable. It does not
call FileDescriptor.sync() or FileChannel.force() for each commit.

See also: http://www.h2database.com/html/advanced.html#durability_problems

> There is a special mode in derby to allow a weaker durability setting
> (it still writes more frequently than h2)

> System.setProperty("derby.system.durability", "test");

I'm not sure if it really writes more frequently, how did you test
that? I know about this mode. As far as I have read the docs, it says
'don't use except for testing, otherwise you got a corrupted database
very quickly'. So this seems too dangerous. However if Derby would
implement 'soft commits' like MySQL, PostgreSQL and H2, I would use
that.

> If I knew more about derby I'm sure I could get it running faster.

I'm sure it can be made faster, but in my view it is up to the Derby
develpers to change the default settings to make it faster.

In any case, I think it would be good if Derby would write an open
source benchmark where they are faster than another database, if this
is possible. They do have a benchmark where they compare embedded
Derby against client/server MySQL (as far as I know), and made
presentations at ApacheCon and JavaOne. I asked for the source code
but they said it is confidential.

Thomas

Brish

unread,
Oct 1, 2007, 9:33:25 PM10/1/07
to H2 Database
On Sep 26, 11:39 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

... snip ...

> > I don't develop derby but I thought it might be entertaining to see if
> > I could improve the performance of derby with the h2 benchmarks.
>
> That's good! So far nobody from Derby took the time to see why Derby is slow.

But they sped it up with the current release?! It appears they are
looking at performance.

... snip ...

> > I made a slight modification so that openConnection automatically turns
> > autocommit mode off. I also made it so that closeConnection does a
> > commit.
> > Autocommit = false summary; h2: 17063; derby: 66077
>
> While I don't think that this is a valid use case for a database, I
> will add this to the Derby documentation:

Having autocommit on isn't the normal case.

> > The next thing I noticed was the h2 wasn't using durable operations.
>
> Yes, Derby does not use durable operations as well. Derby uses
> RandomAccessFile(.., "rws" or "rwd"), this is not durable. It does not
> call FileDescriptor.sync() or FileChannel.force() for each commit.
>
> See also:http://www.h2database.com/html/advanced.html#durability_problems

It does use force. See:

http://svn.apache.org/repos/asf/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/io/DirRandomAccessFile4.java

> > There is a special mode in derby to allow a weaker durability setting
> > (it still writes more frequently than h2)
> > System.setProperty("derby.system.durability", "test");
>
> I'm not sure if it really writes more frequently, how did you test
> that? I know about this mode. As far as I have read the docs, it says
> 'don't use except for testing, otherwise you got a corrupted database
> very quickly'. So this seems too dangerous. However if Derby would
> implement 'soft commits' like MySQL, PostgreSQL and H2, I would use
> that.

Derby does IO for commits. H2 delays IO for commits for one second.
This gives h2 really impressive looking numbers because it can do many
commits with a single write but at the cost of durability.

According to the documentation this is what the setting does:

derby.system.durability

Function
This property changes the default durability of Derby to improve
performance at the expense of consistency and durability of the
database. The only valid supported case insensitive value is test. If
this property is set to any value other than test, this property
setting is ignored. When derby.system.durability is set to test, the
store system will not force I/O synchronization calls for:

* the log file at each commit.
* the log file before a data page is forced to disk.
* page allocation when a file is grown.
* for data writes during checkpoints.

Derby still does IO operations but it won't force syncs so there are
chances that it can't recover.

Derby in this mode is still more reliable than h2.

How can I consider the H2 benchmark valid if you disable durability on
every database that supports it except Derby?

> > If I knew more about derby I'm sure I could get it running faster.
>
> I'm sure it can be made faster, but in my view it is up to the Derby
> develpers to change the default settings to make it faster.

They are. See the latest version performance improvements.

> In any case, I think it would be good if Derby would write an open
> source benchmark where they are faster than another database, if this
> is possible. They do have a benchmark where they compare embedded
> Derby against client/server MySQL (as far as I know), and made
> presentations at ApacheCon and JavaOne. I asked for the source code
> but they said it is confidential.

Why not just have a standard open source benchmark for databases
period?

Like this one:
http://polepos.sourceforge.net/

I noticed that as the database size grows the difference in
performance between h2, and derby shrinks. This probably has something
to do with all the data for the benchmarks fits into the h2 cache but
not in the derby cache. As the size is increased both h2, and derby
are forced to read the data from the disk so they are on more even
ground.

After looking at how both databases work I think it all boils down to
one thing. Disk IO is more expensive than ram access.

H2 sacrifices durability for performance. The closest thing Derby
supports for non-durability is not calling sync all the time. I
checked the latest code and it appears there is some work on how
information is stored. They might be working on being able to turn
some of the durability options for performance. It will be interesting
to see how well H2 does against Derby when both of them delay writes.

fyi I tried to increase the data sizes for the benchmark so that both
Derby, and H2 were actually reading from the disk and sometimes the
benchmark didn't work. Other times h2 crashed. You may want to look
into this.

Brish

Brish

unread,
Oct 8, 2007, 6:06:11 PM10/8/07
to H2 Database
No reply?

I continued to try to update the h2 benchmark so it doesn't cripple
the other database's results. All the previous changes I mentioned
above are still applied.

I reduced the cache size on h2 to 4 megs so that it has the same size
as derby so that it's a fair test (it was 16 megs giving it 4x the
cache that derby has).

I decided to focus on fixing one benchmark first (BenchSimple).

I added batch inserts/updates/deletes so that network databases aren't
crippled anymore. I also added batch selects.

I encountered huge performance problems with h2. When I did the query
(random) test with batch selects the performance was really REALLY
bad. This change has no impact on derby embedded, and significantly
sped up derby server, postgres.

// In H2 there is a serious performance problem executing this
query.
// I'm not sure what is wrong with h2 but it should be fixed
because
// this is a common way of speeding up some types of data
loading.
db.start(this, "Query (random) - added fetch batching");
prep = db.prepare("SELECT * FROM TEST WHERE ID in
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

int currentParameter = 1;

for (int i = 0; i < records; i++) {
prep.setInt(currentParameter++, random.nextInt(records));

if (currentParameter > 10)
{
currentParameter = 1;
db.queryReadResult(prep);
}
}

prep.close();
db.end();

Embedded results for just this test:
H2: 103891
Derby: 1703

Server results for just this test:
H2: 111921
Derby: 7407
postgres: 4875

H2 has huge performance problems with batching if connecting to a
network server. On the network test H2 was easily beaten by Derby, and
postgres after batching was added. Batching sped up all the other
databases I tested.

Embedded results for BenchSimple:
h2: 109172
derby: 10547

Server results for BenchSimple:
h2: 237859
derby: 23016
postgres: 17046

I noticed that you didn't apply my previous changes to the benchmark
before updating your benchmark results on your main site. Does this
mean you are not serious about having a fair benchmark?!

Brish

Brish

unread,
Oct 9, 2007, 2:45:39 AM10/9/07
to H2 Database
BenchSimple continued ...

I disabled batch insert/updates/deletes, and batch fetching for the
embedded tests because H2 performance was so bad with it.

I removed the crippling hsqldb config you added.

Your config for hsqldb:
jdbc:hsqldb:data/
test;hsqldb.default_table_type=cached;sql.enforce_size=true;sa, sa

h2: 4188
derby: 11125
hsqldb: 8578

Removed your custom config for hsqldb:
jdbc:hsqldb:data/test;sa, sa

h2: 4234
derby: 10654
hsqldb: 1875

Brish

Message has been deleted

Brish

unread,
Oct 9, 2007, 11:05:39 PM10/9/07
to H2 Database
Do you really think this is a valid test:

prep = prepare("SELECT COUNT(DISTINCT S_I_ID) "
+ "FROM ORDER_LINE, STOCK WHERE OL_W_ID=? AND "
+ "OL_D_ID=? AND OL_O_ID<? AND "
+ "OL_O_ID>=? AND S_W_ID=? AND "
+ "S_I_ID=OL_I_ID AND S_QUANTITY<?");
prep.setInt(1, warehouseId);
prep.setInt(2, dId);
prep.setInt(3, oId);
prep.setInt(4, oId - 20);
prep.setInt(5, warehouseId);
prep.setInt(6, threshold);
// TODO this is where HSQLDB is very slow
rs = db.query(prep);
rs.next();
rs.getInt(1); // stockCount
rs.close();

?!

Brish

Thomas Mueller

unread,
Oct 10, 2007, 12:22:05 AM10/10/07
to h2-da...@googlegroups.com
Hi,

Sorry for the delay.

> > nobody from Derby took the time to see why Derby is slow.
> But they sped it up with the current release?! It appears they are
> looking at performance.

Sorry, I should have written: why Derby is slow in the H2 benchmark.

> Having autocommit on isn't the normal case.

It depends on the use case of course. Of course you could argue people
who are interested in performance will disable it. Autocommit is
enabled by default after opening a connection.

> It does use force. See:

Yes, I saw that, and also thought it is used by default. However, then
I have added a System.out.println(...) just before the call and run my
test. There was nothing on system out. The method is not actually
called by the engine in the normal case.

Maybe I am wrong and they have changed it (but that would be strange
as it slows things down a lot), if you have time could you test it
with the current release of Derby?

> Derby does IO for commits. H2 delays IO for commits for one second.
> This gives h2 really impressive looking numbers because it can do many
> commits with a single write but at the cost of durability.

Compared to Derby, yes. For MySQL, PostgreSQL, and HSQLDB, the
configuration is changed so they also delay the commit by one second.
If Derby supports a delayed commit I would do that as well.

> derby.system.durability = test


> Derby in this mode is still more reliable than h2.

Why do you think so?

> How can I consider the H2 benchmark valid if you disable durability on
> every database that supports it except Derby?

Because Derby says it's dangerous:
WARNING: Write cache reduces probability of successful recovery after
power failure
If it's not dangerous any longer, I will use it.

> > develpers to change the default settings to make it faster.
> They are. See the latest version performance improvements.

That's good! Of course performance is not the only 'selling point' for
a database.

> Why not just have a standard open source benchmark for databases
> period?
> Like this one:
> http://polepos.sourceforge.net/

See http://www.h2database.com/html/performance.html for the results
(however I didn't run this test for a longer time, probably I should
remove the numbers). The algorithms are all quite simple. It was
developed / sponsored by db4o. The default settings are problematic
(for example, databases are not always closed).

> I noticed that as the database size grows the difference in
> performance between h2, and derby shrinks.

That's possible. H2 is not yet optimized for larger databases.

> This probably has something
> to do with all the data for the benchmarks fits into the h2 cache but
> not in the derby cache.

I'm not sure. For BenchC Derby does use much less memory. I will need
to change the cache settings. The last time I did that, it didn't make
much difference, but potentially it does of course.

> It will be interesting
> to see how well H2 does against Derby when both of them delay writes.

Sure!

> fyi I tried to increase the data sizes for the benchmark so that both
> Derby, and H2 were actually reading from the disk and sometimes the
> benchmark didn't work. Other times h2 crashed. You may want to look
> into this.

What were the settings? Of course I need to find out why H2 crashed!
What kind of exception did you get? Lock timeout?

Thanks for you feedback!
Thomas

Chris Schanck

unread,
Oct 11, 2007, 2:16:24 PM10/11/07
to h2-da...@googlegroups.com
Just to jump in on an interesting discussion...

On 10/10/07, Thomas Mueller <thomas.to...@gmail.com> wrote:
>
> Hi,
>
> Sorry for the delay.
>
> > > nobody from Derby took the time to see why Derby is slow.
> > But they sped it up with the current release?! It appears they are
> > looking at performance.
>
> Sorry, I should have written: why Derby is slow in the H2 benchmark.
>
> > Having autocommit on isn't the normal case.
>
> It depends on the use case of course. Of course you could argue people
> who are interested in performance will disable it. Autocommit is
> enabled by default after opening a connection.
>

We use both Derby and H2 with autocomitt off exclusively; our use case
is a bit non standard but still.

> > It does use force. See:
>
> Yes, I saw that, and also thought it is used by default. However, then
> I have added a System.out.println(...) just before the call and run my
> test. There was nothing on system out. The method is not actually
> called by the engine in the normal case.
>
> Maybe I am wrong and they have changed it (but that would be strange
> as it slows things down a lot), if you have time could you test it
> with the current release of Derby?
>
> > Derby does IO for commits. H2 delays IO for commits for one second.
> > This gives h2 really impressive looking numbers because it can do many
> > commits with a single write but at the cost of durability.
>
> Compared to Derby, yes. For MySQL, PostgreSQL, and HSQLDB, the
> configuration is changed so they also delay the commit by one second.
> If Derby supports a delayed commit I would do that as well.
>
> > derby.system.durability = test
> > Derby in this mode is still more reliable than h2.
>
> Why do you think so?
>
> > How can I consider the H2 benchmark valid if you disable durability on
> > every database that supports it except Derby?
>
> Because Derby says it's dangerous:
> WARNING: Write cache reduces probability of successful recovery after
> power failure
> If it's not dangerous any longer, I will use it.
>

This tracks with what I understand about how Derby works. It seems to
me that with H2 you might lose some data fromt he last set of
operations, but the database would be recoverable to a known point;
with Derby, if you turn off durability, you run the risk of a
non-recoverable database, which is an order of magnitude more
disastrous.

> > > develpers to change the default settings to make it faster.
> > They are. See the latest version performance improvements.
>
> That's good! Of course performance is not the only 'selling point' for
> a database.
>
> > Why not just have a standard open source benchmark for databases
> > period?
> > Like this one:
> > http://polepos.sourceforge.net/
>
> See http://www.h2database.com/html/performance.html for the results
> (however I didn't run this test for a longer time, probably I should
> remove the numbers). The algorithms are all quite simple. It was
> developed / sponsored by db4o. The default settings are problematic
> (for example, databases are not always closed).
>
> > I noticed that as the database size grows the difference in
> > performance between h2, and derby shrinks.
>
> That's possible. H2 is not yet optimized for larger databases.
>
> > This probably has something
> > to do with all the data for the benchmarks fits into the h2 cache but
> > not in the derby cache.
>

In my situation, I expose H2 to somewhat large databases (500-800
tables, a total of 10-30 million rows). In this instance I use a cache
of at least 64M, sometimes 128M. Even though H2 is handling
significantly more data than fits in the cache, its performance
remains massively above Derby's, both query and ingest. No matter how
high I set the cache in Derby I can't quite get it to play catchup.

> I'm not sure. For BenchC Derby does use much less memory. I will need
> to change the cache settings. The last time I did that, it didn't make
> much difference, but potentially it does of course.
>
> > It will be interesting
> > to see how well H2 does against Derby when both of them delay writes.
>

> [... deletia ...]

I've no doubt Derby will improve.

Chris

Thomas Mueller

unread,
Oct 14, 2007, 6:55:51 AM10/14/07
to h2-da...@googlegroups.com
Hi,

> I reduced the cache size on h2 to 4 megs so that it has the same size
> as derby so that it's a fair test (it was 16 megs giving it 4x the
> cache that derby has).

What settings did you use?

> with batch selects the performance was really REALLY
> bad.

> prep = db.prepare("SELECT * FROM TEST WHERE ID in
> (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

The problem is that H2 doesn't always use an index in this case.

> Does this
> mean you are not serious about having a fair benchmark?!

I am serious about a fair benchmark, but I agree the IN(...) needs to
be fixed in H2. However I don't believe that using IN(...) like that
is the most important use case. Usually IN is used to limit the result
in some way, for example: WHERE BUG_ID=? AND BUG_STATE IN(OPEN,
REOPENED, ASSIGNED). You are using it as the primary key lookup, WHERE
BUG_ID IN(?, ?, ...). The TPC tests don't use IN(...) at all as far as
I know, at least not in this way.

Derby does a better job than H2 at using indexes when possible. There
are still quite many case where H2 doesn't use an index, or only uses
indexes in some cases. For example, H2 doesn't use an index when using
GROUP BY: SELECT TYPE, COUNT(*) FROM TEST GROUP BY TYPE will not use
an index on TYPE. It will take some time until all such cases are
using an index. However, H2 is already much better at optimizing
queries than HSQLDB.

Thomas

Andrea Aime

unread,
Oct 14, 2007, 8:09:09 AM10/14/07
to h2-da...@googlegroups.com
Thomas Mueller ha scritto:

> I am serious about a fair benchmark, but I agree the IN(...) needs to
> be fixed in H2. However I don't believe that using IN(...) like that
> is the most important use case.
Given a wide enough use case set, you'll find that at least for some
applications a filter
based exclusively on in(...) is an important use case.
For example, in GeoTools we generate a in(...) quite often when dealing
with the OGC
FidFilter, which translated in sql sounds like primaryKey in (v1, v2,
..., vn).
Think of a situation where you select some geographic data either by
pin-pointing your cursor, or
by dragging a selection rectangle, the only stable way to refer that
selection back
in the db is to use the set of primary keys.

Cheers
Andrea

Thomas Mueller

unread,
Oct 18, 2007, 12:31:06 AM10/18/07
to h2-da...@googlegroups.com
Hi,

> I removed the crippling hsqldb config you added.
>
> Your config for hsqldb:
> jdbc:hsqldb:data/
> test;hsqldb.default_table_type=cached;sql.enforce_size=true;sa, sa

If you do that HSQLDB uses in-memory tables. H2 supports this mode as
well, using
jdbc:h2:test;DEFAULT_TABLE_TYPE=MEMORY
There is still a difference between H2 memory tables and HSQLDB memory
tables (for persistent databases): H2 supports larger in-memory tables
as the table data is not always kept in-memory (I know this is
confusing); only the index data is kept in-memory.

H2 doesn't support ignoring the VARCHAR size.

Thomas

Thomas Mueller

unread,
Oct 18, 2007, 12:31:23 AM10/18/07
to h2-da...@googlegroups.com
> Do you really think this is a valid test:
prep = prepare("SELECT COUNT(DISTINCT S_I_ID) "
+ "FROM ORDER_LINE, STOCK WHERE OL_W_ID=? AND "
+ "OL_D_ID=? AND OL_O_ID<? AND "
+ "OL_O_ID>=? AND S_W_ID=? AND "
+ "S_I_ID=OL_I_ID AND S_QUANTITY<?");

Is it not? See the TPC-C test at http://www.tpc.org/tpcc/spec/tpcc_current.pdf

Thomas

Thomas Mueller

unread,
Oct 18, 2007, 12:31:37 AM10/18/07
to h2-da...@googlegroups.com
Hi,

> at least for some applications a filter
> based exclusively on in(...) is an important use case.

Sure. I understand it is important. For H2, there is a workaround:

SELECT * FROM TABLE(X AS INT = ?) I, TEST T WHERE I.X = T.ID

You can then set the parameter ? like this:
PreparedStatement.setObject(1, new Long[]{new Long(1), ...});

Therefore, you can use it with PreparedStatements. It will use an
index on TEST.ID. Also, you can use multiple columns. See also
org.h2.tools.MultiDimension (about this, and about multi-dimensional
range queries).

The current idea is to automatically convert IN(...) queries to such
queries. But it will take some more time.

Thomas

Brish

unread,
Oct 25, 2007, 2:27:43 AM10/25/07
to H2 Database
On Oct 9, 10:22 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

> It depends on the use case of course. Of course you could argue people
> who are interested in performance will disable it. Autocommit is
> enabled by default after opening a connection.

Who are the people who aren't interested in performance?!

> > Derby does IO for commits. H2 delays IO for commits for one second.
> > This gives h2 really impressive looking numbers because it can do many
> > commits with a single write but at the cost of durability.
>
> Compared to Derby, yes. For MySQL, PostgreSQL, and HSQLDB, the
> configuration is changed so they also delay the commit by one second.
> If Derby supports a delayed commit I would do that as well.

fyi There is new feature in the beta of posgresql 8.3 so that delayed
writes can't result in unrecoverable errors.

MySQL isn't reliable in any mode (it's getting better with a special
ansi mode but it's still not reliable).

It would be interesting to see the performance numbers for all the
databases when durability is turned on.

fyi For most the data I work with reliability is more important than
speed.

> > derby.system.durability = test
> > Derby in this mode is still more reliable than h2.
>
> Why do you think so?

I tried to get derby to corrupt the database by forcing the java vm to
shutdown in the middle of transactions. I even did forced shutdowns
during recovery and it still had no problems (it turns out even
recovery in derby uses a WAL so failed recoveries can be recovered
from!!!).

When I tried optimize the benchmarks I got a few crashes in h2 that
caused data problems. Maybe I'm just good at finding bugs! :-)

> > How can I consider the H2 benchmark valid if you disable durability on
> > every database that supports it except Derby?
>
> Because Derby says it's dangerous:
> WARNING: Write cache reduces probability of successful recovery after
> power failure
> If it's not dangerous any longer, I will use it.

If having issues recovering means it is excluded from being in the
benchmark then you can't include MySQL (it isn't reliable in any
mode), you can't do delayed writes for postgres (it can corrupt data),
you can't include hypersonic, and you can't include h2 in the
benchmark.

So either fix the postgres configuration, and remove all the other
databases except derby ... or turn off durability for derby so that
it's a fair test.

> > fyi I tried to increase the data sizes for the benchmark so that both
> > Derby, and H2 were actually reading from the disk and sometimes the
> > benchmark didn't work. Other times h2 crashed. You may want to look
> > into this.
>
> What were the settings? Of course I need to find out why H2 crashed!
> What kind of exception did you get? Lock timeout?

There is a number in a properties file that controls the size of the
data to benchmark. It was 400 by default. I changed it to 10000. The
benchmark failed.

Brish

Brish

unread,
Oct 25, 2007, 2:40:58 AM10/25/07
to H2 Database
> > Because Derby says it's dangerous:
> > WARNING: Write cache reduces probability of successful recovery after
> > power failure
> > If it's not dangerous any longer, I will use it.
>
> This tracks with what I understand about how Derby works. It seems to
> me that with H2 you might lose some data fromt he last set of
> operations, but the database would be recoverable to a known point;
> with Derby, if you turn off durability, you run the risk of a
> non-recoverable database, which is an order of magnitude more
> disastrous.

H2 delays writes to the log so if the java vm crashes it can result in
unrecoverable errors because there is no log to recover from.

In derby in "test" mode the write is still done but it doesn't call
sync. This means even if the vm crashes the OS can still write the
data. The test mode will only fail if the OS crashes, or there is a
power failure.

What this means is the derby in the test mode is more reliable than h2
in any mode.

> In my situation, I expose H2 to somewhat large databases (500-800
> tables, a total of 10-30 million rows). In this instance I use a cache
> of at least 64M, sometimes 128M. Even though H2 is handling
> significantly more data than fits in the cache, its performance
> remains massively above Derby's, both query and ingest. No matter how
> high I set the cache in Derby I can't quite get it to play catchup.

I found the following:
- for simple operations h2 is faster
- for reports derby is faster
- h2 is really slow on a few operations (batching with a network
server, large transactions, in(...) processing with a wide range of
values etc)

Brish

Brish

unread,
Oct 25, 2007, 2:47:17 AM10/25/07
to H2 Database
On Oct 14, 4:55 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> > I reduced the cache size on h2 to 4 megs so that it has the same size
> > as derby so that it's a fair test (it was 16 megs giving it 4x the
> > cache that derby has).
>
> What settings did you use?

The cache size in h2 was set to 4 megs.

> > Does this
> > mean you are not serious about having a fair benchmark?!
>
> I am serious about a fair benchmark, but I agree the IN(...) needs to
> be fixed in H2. However I don't believe that using IN(...) like that
> is the most important use case. Usually IN is used to limit the result
> in some way, for example: WHERE BUG_ID=? AND BUG_STATE IN(OPEN,
> REOPENED, ASSIGNED). You are using it as the primary key lookup, WHERE
> BUG_ID IN(?, ?, ...). The TPC tests don't use IN(...) at all as far as
> I know, at least not in this way.

I use the in (...) thing all the time because it speeds up network
database queries a lot for graphs of objects. Also, Hibernate does
this as well.

fyi The in (...) significantly sped up h2 for the sequential test when
h2 was ran in server mode. It only had a negative effect on h2 for the
random test (all the other database were sped up in the random test
using it).

> Derby does a better job than H2 at using indexes when possible. There
> are still quite many case where H2 doesn't use an index, or only uses
> indexes in some cases. For example, H2 doesn't use an index when using
> GROUP BY: SELECT TYPE, COUNT(*) FROM TEST GROUP BY TYPE will not use
> an index on TYPE. It will take some time until all such cases are
> using an index. However, H2 is already much better at optimizing
> queries than HSQLDB.

I checked out the current code for hsqldb because the website said it
has a new query optimizer. hsqldb beats h2 on the h2 benchmark using
the current version.

Brish

Brish

unread,
Oct 25, 2007, 2:48:21 AM10/25/07
to H2 Database
On Oct 17, 10:31 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

You didn't answer the question. Do you think it's a valid test?

The query is just odd.

Brish

Brish

unread,
Oct 25, 2007, 2:52:13 AM10/25/07
to H2 Database
On Oct 17, 10:31 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

fyi The explain said it was using the index for the in(...) in my
test.

Brish

Thomas Mueller

unread,
Nov 1, 2007, 2:20:01 PM11/1/07
to h2-da...@googlegroups.com
Hi,

> fyi There is new feature in the beta of posgresql 8.3 so that delayed
> writes can't result in unrecoverable errors.

Yes, that's what H2 uses as well.

> MySQL isn't reliable in any mode (it's getting better with a special
> ansi mode but it's still not reliable).

That's what Derby claims. Do you have a link for the MySQL problem?

> It would be interesting to see the performance numbers for all the
> databases when durability is turned on.

The performance would be very bad for all of them: about 20
transactions per second. Except for OS X where fsync doesn't actually
do a lot (that's what I have read somewhere). And it wouldn't be
durable, see http://www.h2database.com/html/advanced.html#durability_problems

> fyi For most the data I work with reliability is more important than
> speed.

Yes. But reliability doesn't mean force commits; delayed commits as in
H2 or PostgreSQL (8.3 only? I didn't know that) are reliable as well.
They are just not durable (see the link above)

> When I tried optimize the benchmarks I got a few crashes in h2 that
> caused data problems.

Could you post the exception, and the steps required to reproduce it?

> MySQL (it isn't reliable in any mode),

> postgres (it can corrupt data),

It would be great if you could tell me from where you have this information.

Thomas

Thomas Mueller

unread,
Nov 2, 2007, 2:35:10 PM11/2/07
to h2-da...@googlegroups.com
> H2 delays writes to the log so if the java vm crashes it can result in
> unrecoverable errors because there is no log to recover from.

It would be great if you could describe how you came to this
conclusion, or if you could provide a test case.

> In derby in "test" mode the write is still done but it doesn't call
> sync.

Again, Derby never calls sync. Even when not using "test". Please test
it if you don't believe it.

> What this means is the derby in the test mode is more reliable than h2
> in any mode.

It would be great if you could describe how you came to this conclusion.

> I found the following:
> - for simple operations h2 is faster
> - for reports derby is faster
> - h2 is really slow on a few operations (batching with a network
> server, large transactions, in(...) processing with a wide range of
> values etc)

Yes, H2 is slower for some operations. For reports that use very
complex queries, and when using IN(...) H2 doesn't always optimize as
good as Derby yet (H2 doesn't always use an index where it could).

Thomas

Thomas Mueller

unread,
Nov 6, 2007, 3:15:09 PM11/6/07
to h2-da...@googlegroups.com
Hi,

Sorry for the delay.

> I checked out the current code for hsqldb because the website said it


> has a new query optimizer. hsqldb beats h2 on the h2 benchmark using
> the current version.

That's cool! As soon as it is released I will run the benchmark again.

> > > Do you really think this is a valid test:
> > prep = prepare("SELECT COUNT(DISTINCT S_I_ID) "
> > + "FROM ORDER_LINE, STOCK WHERE OL_W_ID=? AND "
> > + "OL_D_ID=? AND OL_O_ID<? AND "
> > + "OL_O_ID>=? AND S_W_ID=? AND "
> > + "S_I_ID=OL_I_ID AND S_QUANTITY<?");
> > Is it not? See the TPC-C test athttp://www.tpc.org/tpcc/spec/tpcc_current.pdf

> You didn't answer the question. Do you think it's a valid test?

Yes, I think it is a valid test.

> The query is just odd.

I suggest your read the documentation at
http://www.tpc.org/tpcc/spec/tpcc_current.pdf to understand the query.
It tests are a bit complex, but I think the people who designed it
tried to make it meaningful. I saw many queries in existing
applications that are very strange.

Thomas

Brish

unread,
Dec 22, 2007, 4:28:13 PM12/22/07
to H2 Database

On Nov 1, 11:20 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
> Hi,
>
> > fyi There is new feature in the beta of posgresql 8.3 so that delayed
> > writes can't result in unrecoverable errors.
>
> Yes, that's what H2 uses as well.

No it doesn't. It uses the older style delay write that 8.2 supported
that could corrupt data.

> > MySQL isn't reliable in any mode (it's getting better with a special
> > ansi mode but it's still not reliable).
>
> That's what Derby claims. Do you have a link for the MySQL problem?

That isn't what derby claims. That is what I have experienced.

I still have been unable to get derby to lose data even when I forced
that java vm to exit. Losing data is supported in MySQL for backwards
compatibility.

Google "MySQL gotchas" so see how MySQL corrupts data by design.

> > It would be interesting to see the performance numbers for all the
> > databases when durability is turned on.
>
> The performance would be very bad for all of them: about 20
> transactions per second. Except for OS X where fsync doesn't actually
> do a lot (that's what I have read somewhere). And it wouldn't be
> durable, seehttp://www.h2database.com/html/advanced.html#durability_problems

I read your link. It comes across as you don't understand durability.

From apples docs

Fsync() causes all modified data and attributes of fildes to be
moved to
a permanent storage device. This normally results in all in-core
modi-fied modified
fied copies of buffers for the associated file to be written to a
disk.

Note that while fsync() will flush all data from the host to the
drive
(i.e. the "permanent storage device"), the drive itself may not
physi-cally physically
cally write the data to the platters for quite some time and it
may be
written in an out-of-order sequence.

Specifically, if the drive loses power or the OS crashes, the
application
may find that only some or none of their data was written. The
disk
drive may also re-order the data so that later writes may be
present,
while earlier writes are not.

This is not a theoretical edge case. This scenario is easily
reproduced
with real world workloads and drive power failures.

For applications that require tighter guarantees about the
integrity of
their data, Mac OS X provides the F_FULLFSYNC fcntl. The
F_FULLFSYNC
fcntl asks the drive to flush all buffered data to permanent
storage.
Applications, such as databases, that require a strict ordering
of writes
should use F_FULLFSYNC to ensure that their data is written in
the order
they expect. Please see fcntl(2) for more detail.

http://developer.apple.com/documentation/Darwin/Reference/ManPages/man2/fsync.2.html

That's actually a pretty good description about why fsync is required
and why non-fsyncing writes can corrupt data.

> > fyi For most the data I work with reliability is more important than
> > speed.
>
> Yes. But reliability doesn't mean force commits; delayed commits as in
> H2 or PostgreSQL (8.3 only? I didn't know that) are reliable as well.
> They are just not durable (see the link above)

No Postgres 8.2 would delay the writes in a way that is very similar
to h2 but it can lose data.

The problem comes from ordering issues during writes can result in
unrecoverable errors.

Think of it this way. If you update a WAL (write ahead log used for
recovery) and update the table the table could get updated first. Then
a crash will mean the WAL isn't written so there isn't anything to
recover from.

> > When I tried optimize the benchmarks I got a few crashes in h2 that
> > caused data problems.
>
> Could you post the exception, and the steps required to reproduce it?

I already posted what I changed.

> > MySQL (it isn't reliable in any mode),
> > postgres (it can corrupt data),
>
> It would be great if you could tell me from where you have this information.

The internet, and from my own personal experience with MySQL losing
data.

Just google it. There is tons of information about MySQL losing data.

Brish

Brish

unread,
Dec 22, 2007, 4:46:58 PM12/22/07
to H2 Database
On Nov 2, 11:35 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
> > H2 delays writes to the log so if the java vm crashes it can result in
> > unrecoverable errors because there is no log to recover from.
>
> It would be great if you could describe how you came to this
> conclusion, or if you could provide a test case.
>

Run the benchmark and force the java vm to exit (as in kill the
processing while the benchmark is running so that the shutdown hook
can't be executed).

> > In derby in "test" mode the write is still done but it doesn't call
> > sync.
>
> Again, Derby never calls sync. Even when not using "test". Please test
> it if you don't believe it.

Tested, yes it does call sync.

> > What this means is the derby in the test mode is more reliable than h2
> > in any mode.
>
> It would be great if you could describe how you came to this conclusion.
>

I have already explained it in numerous posts. You can also read other
users posts where there are data corruption as well.

> > I found the following:
> > - for simple operations h2 is faster
> > - for reports derby is faster
> > - h2 is really slow on a few operations (batching with a network
> > server, large transactions, in(...) processing with a wide range of
> > values etc)
>
> Yes, H2 is slower for some operations. For reports that use very
> complex queries, and when using IN(...) H2 doesn't always optimize as
> good as Derby yet (H2 doesn't always use an index where it could).

I also find that the performance difference goes away when the data
size is large.

I wrote some code that creates a database of all the metadata for
files on my hard drive so that I could do indexed searches.

Insert performance:
derby : 2450.9 records per second
h2 : 2942.641 records per second
postgres: 2087.487 records per second

Final times after index is created:
derby: 57.531 seconds
h2: 64.328 seconds
postgres: 64.108 seconds

h2 is faster at inserting the records than derby but the time to index
is greater. Also h2 loses to derby for even simple queries when the
data size grows beyond the size of the cache.

I think it has to do with the derby database being smaller so there is
less IO which is the majority of the time.

Brish

Brish

unread,
Dec 22, 2007, 4:48:05 PM12/22/07
to H2 Database
On Nov 6, 1:15 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
> > You didn't answer the question. Do you think it's a valid test?
>
> Yes, I think it is a valid test.
>
> > The query is just odd.
>
> I suggest your read the documentation athttp://www.tpc.org/tpcc/spec/tpcc_current.pdfto understand the query.
> It tests are a bit complex, but I think the people who designed it
> tried to make it meaningful. I saw many queries in existing
> applications that are very strange.

Well, I still think the query is strange! ;-)

Brish

gingda

unread,
Dec 26, 2007, 2:45:35 PM12/26/07
to H2 Database
Just speaking as someone who has used several of the databases that
Thomas features in the benchmark results on his website, several
commercial databases, and at least one other java database not
featured on Thomas benchmarks, our experience has been that the
relative, real world performance of H2 has proven to be consistent
with the expectations we had from looking at the H2 benchmarks.

Additionally, something that really sold us on H2 was the fact that
Thomas had obviously put effort into trying to present an objective
comparison of the performance of several different databases.

Basically, we have used a lot of databases and our experience is that
H2 lives up to its claims.

On Dec 22, 3:48 pm, Brish <bris...@gmail.com> wrote:
> On Nov 6, 1:15 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
> wrote:
>
> > > You didn't answer the question. Do you think it's a valid test?
>
> > Yes, I think it is a valid test.
>
> > > The query is just odd.
>
> > I suggest your read the documentation athttp://www.tpc.org/tpcc/spec/tpcc_current.pdftounderstand the query.
Reply all
Reply to author
Forward
0 new messages