FWD » Core Development » Database

With #3961, we disabled MVCC and moved to page-store. But with page-store, the h2.sortNullsHigh is not working - this needs to be fixed at H2 level, or fix #3961 and enable back MVCC mode.

From an email discussion:

On 11/19/19 10:53 PM, Eric Faulhaber wrote:
Constantin,

Sorry I lost track of this conversation.

We disable MV_STORE because it is noticeably slower than the page store engine (at least it was last time I compared it, just a few versions ago). IIRC, we disabled MVCC because it supposedly was incompatible with the MULTI_THREADED option. I don't recall where I saw this.

To my understanding, MVCC is the default (and only) mode with MV_STORE, so MVCC=TRUE is redundant if MV_STORE=TRUE. Also, I think MULTI_THREADED=TRUE is obsolete with the MV store engine. I think most H2 development is now focused on the MV store engine, so the page store engine probably will be deprecated at some point. Thus, we most likely will need to move to MV store before long, even if it is slower.

In the mean time, as long as we are using the page store engine and MULTI_THREADED, if sortNullsHigh is not working with these options, I think we need to fix it.

Thanks,
Eric

On 11/16/19 7:28 PM, Constantin Asofiei wrote:

Eric,

I found another problem... h2.sortNullsHigh is not working in H2, if the 'mv_store=false;mvcc=false;' is used. I think we've disabled MVCC because of some other related issues, but I don't recall why.

So, at this point, I'll change the default settings for H2 db (_temp and dirty) to use MVCC again (in my local FWD), so I can advance further with this scenario.

But if MVCC really can't be used with FWD, we need to patch H2, so that sortNullsHigh is working...

Thanks,

Constantin

On 11/16/19 12:21 AM, Constantin Asofiei wrote:

Eric,

IIRC, we were setting this in the H2Helper.prepareDatabase method initially, but someone (maybe you?) found that this was too late in H2's initialization, maybe after I added the change to server startup to query index metadata very early. Honestly, I lost track what happened with this setting after that.

Yes, from the history, I'm the one who removed this, back in 2010; there is this comment in P2JH2Dialect.beforeDriverLoad:

The "h2.sortNullsHigh" setting was removed, as it was determined that by forcing the H2 to sort nulls high, it causes a regression in a test where 4GL seems to sort the unknown value at the end of the result set, when an descending sort clause was used (instead of sorting it at the begining of the result set).

TODO: more testing needs to be done to determine exactly how 4GL sorts the unknown value in the temporary database (and permanent DB too). At the time of this comment, H2 has a bug which doesn't enforce the "sort nulls high" setting in cases when the query's result order is determined by the index.

I need to do some digging in Redmine, maybe I can find why this failed...

Thanks,

Constantin

I have never seen the missing lob entry error. I wonder if my patch to H2 missed some LOB behavior because we weren't supporting LOBs in those days. This may require an edit to H2. I haven't looked at the LOB code inside H2.

Sorry, wish my answers were more helpful.

Thanks,
Eric

On 11/16/19 12:15 AM, Eric Faulhaber wrote:

Constantin,

Yes, I added the code myself to H2 (and it was accepted by the project) to optionally sort nulls high or low (i.e., the sortNullsHigh system property).

IIRC, we were setting this in the H2Helper.prepareDatabase method initially, but someone (maybe you?) found that this was too late in H2's initialization, maybe after I added the change to server startup to query index metadata very early. Honestly, I lost track what happened with this setting after that.

I have never seen the missing lob entry error. I wonder if my patch to H2 missed some LOB behavior because we weren't supporting LOBs in those days. This may require an edit to H2. I haven't looked at the LOB code inside H2.

Sorry, wish my answers were more helpful.

Thanks,
Eric

For our development/testing use of H2, I think it is ok to switch to the MV_STORE storage engine when using H2 as the primary database (i.e., not for the internal uses: temp-tables, dirty share, metadata). Don't specify the MVCC option in the JDBC URL.

It will be slower (at least it was last time I tested it), but it should be stable. At this time, MV_STORE is the primary storage engine receiving development attention in the H2 project.

Switching to MV_STORE may avoid the #3961 issue, as well as bypass this sortNullsHigh issue. Note that sortNullsHigh will still be a problem for the internal uses, and it will have to be fixed if we don't switch those cases to MV_STORE as well. I'm not ready to do that until performance of MV_STORE is on par with the page store engine.

Note that you can't connect to a database created with the page store engine using the MV_STORE engine. My understanding is that doing so may corrupt the database. An import from scratch with the MV_STORE engine is necessary.

Note also that H2 is still not officially supported as a production, primary database. It is only approved for development and testing use.

Eric Faulhaber wrote:

For our development/testing use of H2, I think it is ok to switch to the MV_STORE storage engine when using H2 as the primary database (i.e., not for the internal uses: temp-tables, dirty share, metadata). Don't specify the MVCC option in the JDBC URL.
...
Note that sortNullsHigh will still be a problem for the internal uses, and it will have to be fixed if we don't switch those cases to MV_STORE as well. I'm not ready to do that until performance of MV_STORE is on par with the page store engine.

The problem is with the _temp database, not primary database. If page store engine must be used for FWD internal DBs (temp, dirty, meta), then we need to fix H2 and add sortNullsHigh support for it.

H2 1.4.200 doesn't have support for sortNullsHigh.

Constantin Asofiei wrote:

H2 1.4.200 doesn't have support for sortNullsHigh.

Scratch that, sortNullsHigh in page-store seems to be working (at least for the scenario I'm testing with).

Are you saying it isn't working in the version of H2 we're using now, but it is working in 1.4.200?

Eric Faulhaber wrote:

Are you saying it isn't working in the version of H2 we're using now, but it is working in 1.4.200?

Yes, it looks so.

As a side note, only mv_store=false works in 1.4.200, they removed the mvcc URL option.