FWD » Core Development » Database

• my times were generally 3-4x times greater than yours;
• I did not observe much improvement with the new jar. In fact the old library seems to be marginally faster. So I delete/copy the files again, just to be sure I did not messed the jars;
• the initial run showed the indexed case performing slower (+2%) than non-indexed with the new jar, while with the old one the indexed was 4% faster. In the second run both non-indexed updates performed better (?), by +4% and +2% respectively. That's strange! (Note: when doing the statistics I used only the client executions 2-5, ignoring the 1st, cold run).

Thanks, Ovidiu, I'll profile some more and see what happens.

I am doing the stress tests right now.

I've been running the H2 unit tests, too, and this showed some problems which I had to fix, but the unit tests with rev 7 of the fwd-h2 project still have some failures. I can't tell if these are from our changes or not.

When I introduced the 7th revision, some unit tests failed. In some cases, the tests expected a scan index to be used instead of a tree index. My changes improved the query plan to use tree index in some certain cases which proved faster. You can safely fix the unit tests. I guess I forgot to do the unit test changes in rev. 7.

Note that there is a recent addition to Hotel GUI regarding H2 performance testing. Pressing CTRL-ALT-H anywhere pops up a H2 profiling dialog. There are only some tests which compute their etime in a browse (for create, update, insert and delete). This was designed explicitly for profiling H2 version in different cases. However, I guess this UPDATE test is OK to be shared here as it requires a fine profiling (in terms of ms / in ChUI). Anyways, that tool can be upgraded to cover many more scenarios (like this one).

It actually have improved a lot, an odd issue which can be visible is that the indexed executions take up to twice as much time. However, as strange as this looks, it is actually normal: since there are no indices to update, the non-indexed variant is faster! It is the sacrifice which needs to be paid to get a better indexed access later. Taking into balance that updates usually happen rarely I think this is a good trade.

Of course, I re-did the tests with the old fwd-h2-1.4.200-6.jar and the result were pretty unchanged, average being ~200ms for both cases. Those results from my note #6928-4 above still stand.

These are my testing results with the new jar. The times are certainly better than in #6928-7 (3 times better for unindexed is really good).

Alexandru: I recall there were some performance testcases written for H2, can you point me where these are?

There is a thread about Performance Testing the H2 Database. The most accurate results are achieved using the testcases/uast/h2_profiling/start.p and testcases/uast/h2_profiling/legacy-tests/perf-run-all-silent.p tests (using etime). The first tests are the ones integrated in Hotel GUI.

I got deep in most of the changes in rev. 8 - they seem reasonable. As a side question, I see some changes from for-each structure into index based iteration: are these changes targeting performance?

Alexandru Lungu wrote:

As a side question, I see some changes from for-each structure into index based iteration: are these changes targeting performance?

Yes, the point is that for-each creates iterator-related objects which affect garbage collection.

Is there anything left to do, in order for FWD to use the new H2 jar by default? Is the FWD build updated? Is the jar uploaded to the artifacts site? If not, is there any reason it should not be?

H2's UPDATE command was profiled and improved to avoid object allocations and unnecessary iterations. This is in fwd-h2 rev 8, but fwd-h2 rev 7 shows a slowdown, so this can not be released until fwd-h2 rev 7 is fixed.

H2 rev. 7 is mostly targeting progressively fetched (using LIMIT) multi-table AdaptiveQuery. This is not yet part of FWD, so the optimization itself doesn't have the expected impact on its own. It looks like the overhead there is much more than the actual benefit. As we have an "experimental" H2 branch, I think we can integrate the UPDATE optimizations alone in our "official" FWD-H2 branch. Otherwise, we can move rev. 7 to an even more experimental branch, until we have a resolution for #6582.

Alexandru Lungu wrote:

H2 rev. 7 is mostly targeting progressively fetched (using LIMIT) multi-table AdaptiveQuery. This is not yet part of FWD, so the optimization itself doesn't have the expected impact on its own. It looks like the overhead there is much more than the actual benefit. As we have an "experimental" H2 branch, I think we can integrate the UPDATE optimizations alone in our "official" FWD-H2 branch. Otherwise, we can move rev. 7 to an even more experimental branch, until we have a resolution for #6582.

Agreed. As has been suggested elsewhere, it is time to get our fork of the H2 source code base under version control. I'll open a separate task for this.

While working on another task, I stumbled upon an issue. With fwd-h2 rev.7 it worked fine, but in rev.8 there seems to be a problem regarding rollback to savepoint.
This is the test I tried in a H2 console:

create table tt (f1 int)
insert into tt (f1) values (1)
savepoint sv
update tt set f1 = 2
select * from tt
rollback to savepoint sv
select * from tt

Radu, please debug this and fix it.

Rebased 6928a_h2 rev 8 from fwd-h2.trunk rev 8 - new rev 10.

Tested 6928a_h2/rev. 10 and found a regression: the old-row stored by deltaChangeCollector inside the H2 update statement was altered by our modifications. Therefore, SELECT * FROM OLD TABLE (UPDATE TEST SET B = 3 WHERE ID = 1); was returning updated rows, instead of the old ones. Fixed this is 6928a_h2/rev. 11.

As all regressions tests now pass, I am planning to merge 6928a_h2 into trunk if no further objections.

Alexandru Lungu wrote:

Tested 6928a_h2/rev. 10 and found a regression: the old-row stored by deltaChangeCollector inside the H2 update statement was altered by our modifications. Therefore, SELECT * FROM OLD TABLE (UPDATE TEST SET B = 3 WHERE ID = 1); was returning updated rows, instead of the old ones. Fixed this is 6928a_h2/rev. 11.

Was the branch bound? I don't see rev 11 for 6928a_h2.

Constantin Asofiei wrote:

Was the branch bound? I don't see rev 11 for 6928a_h2.

What a quick response! My bzr was still committing when I've written #6928-23 :) Now it is finished, please try updating again.

Thanks, the change is OK. It can be merged to trunk.