H2 Clustering with more than 2 nodes

[dch...@peaxy.net]

I'd be grateful for your insights into a design idea so that I don't head down a dead-end road.  Alternatives appreciated!

Our requirement is for a relatively small DB with a low rate of transactions.  I'd like to be able to have a db clone on each of 3 nodes.  Clients would be directed to one node via a virtual IP; the cluster would apply all operations at that IP and to two copies.  If either copy (not the 'master' at the virtual IP) fails the cluster continues to operate normally; after the node is recovered CreateCluster is re-run as usual, albeit with 3 nodes.  If the master (the node at the virtual IP) fails our code selects a new master from the 2 copies; that node takes over the virtual IP and the cluster begins operating as though a copy has failed.

As far as I can see this imposes only 2 requirements on H2 that are different than usual: 1) support for a client JDBC url naming a single tcp server while in cluster mode, and 2) support for 3 nodes in a CreateCluster -serverList.

Re (1): an attempt to connect to a cluster using a single node JDBC url (e.g. "jdbc:h2:tcp://172.16.35.53:9101/msp") receives a response: "Cannot connect to <dbname>.  Check your URL. Clustering error - database currently runs in cluster mode: server list <server:port>,<server>:<port>".  Is the server topology exposed so that client-side code can redirect a failed operation to the alternate server?  The design I am suggesting hides the topology - and requires that a single node URL be accepted by the server.

I've begun exploring this a bit (2) a bit.  I have a working 2 node cluster (nodes .55 and .53).  I run the server on a third node (.51):

java -cp /usr/lib/java/*:. org.h2.tools.Server -tcp -tcpPort 9101 -tcpAllowOthers -baseDir <base> & 

Then on my 'first' server (.55) I successfully run CreateCluster, which creates the db files on node .51:

java -cp /usr/lib/java/*:. org.h2.tools.CreateCluster \

    -urlSource jdbc:h2:tcp://172.16.35.55:9101/msp \

    -urlTarget jdbc:h2:tcp://172.16.35.51:9101/msp \

    -serverList 172.16.35.55:9101,172.16.35.51:9101,172.16.35.53:9101

Expecting the cluster to only use 2 nodes, I put the new .51 node second in the serverlist.  The H2 log on .51 shows an update to the CLUSTER server list and includes all three servers:

10-02 09:14:27 index: SYS_DATA remove ( /* key:77 */ 58, 0, 6, 'SET CLUSTER ''''')

10-02 09:14:27 pageStore: log - s: 1 table: 0 row: ( /* key:77 */ 58, 0, 6, 'SET CLUSTER ''''')

10-02 09:14:27 index: SYS_DATA add ( /* key:78 */ 58, 0, 6, 'SET CLUSTER ''172.16.35.55:9101,172.16.35.51:9101,172.16.35.53:9101''')

10-02 09:14:27 pageStore: log + s: 1 table: 0 row: ( /* key:78 */ 58, 0, 6, 'SET CLUSTER ''172.16.35.55:9101,172.16.35.51:9101,172.16.35.53:9101''')

10-02 09:14:27 pageStore: log commit s: 1

10-02 09:14:27 lock: 1 exclusive write lock unlock SYS

10-02 09:14:27 jdbc[20]: 

/*SQL t:1*/SET CLUSTER '172.16.35.55:9101,172.16.35.51:9101,172.16.35.53:9101';

Code in SessionRemote loops through the serverlist applying any SQL operation, so this looks very promising.

However, at the start of handling a SQL command, H2 removes that value and replaces it with a 2 node list, and the new node is not in that list:

10-02 09:17:06 jdbc[21]: 

/*SQL */SET CLUSTER TRUE;

10-02 09:17:06 lock: 1 exclusive write lock requesting for SYS

10-02 09:17:06 lock: 1 exclusive write lock added for SYS

10-02 09:17:06 index: SYS_DATA remove ( /* key:78 */ 58, 0, 6, 'SET CLUSTER ''172.16.35.55:9101,172.16.35.51:9101,172.16.35.53:9101''')

10-02 09:17:06 pageStore: log - s: 1 table: 0 row: ( /* key:78 */ 58, 0, 6, 'SET CLUSTER ''172.16.35.55:9101,172.16.35.51:9101,172.16.35.53:9101''')

10-02 09:17:06 index: SYS_DATA add ( /* key:79 */ 58, 0, 6, 'SET CLUSTER ''172.16.35.55:9101,172.16.35.53:9101''')

10-02 09:17:06 pageStore: log + s: 1 table: 0 row: ( /* key:79 */ 58, 0, 6, 'SET CLUSTER ''172.16.35.55:9101,172.16.35.53:9101''') 

I'll be looking into this.

I also see this interesting comment at line 397: 

      // TODO cluster: support more than 2 connections

What would need to be done?

Thanks!

[dch...@peaxy.net]

sweet - I am able to cluster 3 nodes, where "cluster" is defined as "writes to the first server in a serverlist are replicated on the other (two) nodes.   What I had reported in (2) was due to a misconfiguration - I still had only two nodes in a local configuration file read (and repeatedly applied) by mybatis SessionFactory.

Re (1): I found that on occasion I am able to connect to a cluster using a JDBC URL naming a single server.  Looking into this I found that the H2 value of CLUSTER is set prior to every write - and reset following a successful connect.  I can connect to a cluster naming a single server (the first one) prior to a write to the DB.  Following a write I can connect using a URL naming any 2 of the 3 servers.  Connecting resets the state so that I can again connect using a single server until the next write:

10-02 16:15:02 jdbc[14]: /*SQL */SET AUTO_RECONNECT TRUE;

10-02 16:15:02 jdbc[14]: /*SQL */SET CLUSTER TRUE;

10-02 16:15:02 jdbc[14]: /*SQL */SET TRACE_LEVEL_SYSTEM_OUT 3;

10-02 16:15:02 jdbc[14]: /*SQL */SET DB_CLOSE_DELAY -1;

10-02 16:15:04 index: SYS_DATA remove ( /* key:119 */ 58, 0, 6, 'SET CLUSTER ''172.16.35.55:9101,172.16.35.53:9101,172.16.35.51:9101''')

10-02 16:15:04 pageStore: log - s: 1 table: 0 row: ( /* key:119 */ 58, 0, 6, 'SET CLUSTER ''172.16.35.55:9101,172.16.35.53:9101,172.16.35.51:9101''')

10-02 16:15:04 index: SYS_DATA add ( /* key:120 */ 58, 0, 6, 'SET CLUSTER ''''')

10-02 16:15:04 pageStore: log + s: 1 table: 0 row: ( /* key:120 */ 58, 0, 6, 'SET CLUSTER ''''')

Though this is a cluster, SELECT VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME='CLUSTER'; returns '' from successful connect til first write.  Following a write it returns the expected list of servers.  I suspect this is not as intended - though it may prove useful in my efforts :) 

[Noel Grandin]

Hi

The current clustering stuff was a bit of a hack.
We'd be very happy to accept any patches to improve it.

You might want to join forces with the other people who are interesting
in clustering with H2
- see the discussion here:
https://groups.google.com/d/msg/h2-database/35UcVmKz784/xn8RGf6Uc08J

Regards, Noel.