FWD » Core Development » Database

This option was discussed in a standup meeting today as an alternative to using the H2 database in memory for "live" metadata tables (e.g., _connect, _trans, etc.).

When support for certain metadata tables which must expose operational state to applications was first introduced, H2 was chosen for apparent performance advantages. Previous experience with a migration of temp-tables from the persistent database to an in-memory H2 database showed marked performance improvements, due to the elimination of network traffic and file system I/O.

However, the temp-table implementation requires no locking, since the tables (and, in fact, the databases) are private to a session. The metadata database OTOH is shared across sessions, and the locking required is not performant. This is not a problem specific to the H2 pagestore engine; we've not seen sufficient improvement with the MVStore engine.

We've implemented synchronization further back from the database, in FWD, to minimize the locking needed within H2, and that has shown a promising improvement. However, this makes stateless clustering more complicated and potentially makes performance of a stateless clustering implementation worse.

The idea here is to prototype a solution for these live metadata tables using the persistent database (e.g., PostgreSQL, MariaDB, etc.). We introduce network latency, but these databases likely have better locking/synchronization performance than H2. It remains to be seen whether the effect on performance on balance is better or worse.

Using a persistent database for this purpose may also make the stateless clustering solution simpler, depending how it is implemented. Currently, we store state in memory for most metadata tables and try to minimize flushing. This state would need to be synchronized across nodes in the cluster. Is it feasible to flush changes directly to the persistent database, so that this synchronization can be avoided?

This approach may be impractical for certain update-heavy metadata (e.g., the _lock table). It may still be necessary to use the deferred flush model in these cases.

The immediate goal here is to work out a feasible design which eases the clustering implementation, then to create a working prototype, so we can start measuring/comparing performance and scalability.

Currently we support the following VSTs (alphabetically ordered):

Eric Faulhaber wrote:

Using a persistent database for this purpose may also make the stateless clustering solution simpler, depending how it is implemented. Currently, we store state in memory for most metadata tables and try to minimize flushing. This state would need to be synchronized across nodes in the cluster. Is it feasible to flush changes directly to the persistent database, so that this synchronization can be avoided?

This approach may be impractical for certain update-heavy metadata (e.g., the _lock table). It may still be necessary to use the deferred flush model in these cases.

The immediate goal here is to work out a feasible design which eases the clustering implementation, then to create a working prototype, so we can start measuring/comparing performance and scalability.

Maybe again it is my idea is again an over engineering, but I think it is worth to be noted.

The proposed design combines local thresholded batching with a lightweight publish/subscribe coordination so each server instance writes most metadata updates to the shared Postgres/MariaDB store proactively and reads see a consistent, up‑to‑date view with minimal latency.

When any server instance needs to read a VST, it issues a short‑lived flush request on the pub/sub channel to all existing server instances in a cluster. Nodes that currently hold pending local batches respond by flushing those batches immediately and publishing a flush done message. The requesting node waits only for responses from nodes that are actually dirty, then reads the consolidated state from the persistent meta DB.

In order to avoid huge flushing latency at each read of VST, we could do the flushing proactivly. Each server instance keeps VST insert events in memory and flushes them to the persistent meta tables when a certain trashhold has been reached. Also this flushed could be executed outside of application business logic thread. Because most server instances will already be clean thanks to thresholded flushing, the coordination step is usually a fast acknowledgement rather than a heavy write operation, keeping read latency low.

Of course this approach would apply only to write heavy meta tables.

will we have a server hierarchy (tree, star)?
all nodes equals and a 1-to-1 communication between all servers?
how do we handle backup servers? (online but inactive servers, ready to take over in case an active one fails, to implement fault-tolerance)

See #4369 for the clustering design. The current implementation separates the cluster design from the synchronization implementation. We will be considering 2 configurations, peer-to-peer and client/server. We don't have a final answer yet. Regardless, the core idea is that all FWD servers in the cluster are active and any that fail should not cause the others to fail.

This is not a problem specific to the H2 pagestore engine; we've not seen sufficient improvement with the MVStore engine.

I would like to argue on this point. There is an algorithm for ensuring FIFO for locks built on top of java locks (which are not FIFO). Removing this mechanism will grealty improve the scalability at the expense of FIFO policy. Maybe is it worth consulting the PG source-code of AI to ellaborate on this topic:

• is FIFO indeed a standard for DB locking? For Java synchronized apparently it is not.
• is there another algorithm we can use in Java for simulating FIFO better than the H2 guys did?

If this point is irrelevant in regard to stateless clustering, I am ok to drop it. But it is important to have it in consideration.

Consistency (C from CAP)¶

I am once again bringing up the consistency problem here. Meta DB access isn't meant to be consistent from the business logic POV, but available. Lets imagine we move the _Lock the PG. Due to high traffic, a query over _Lock may take 2 seconds (low availability we are concerned of!). By the time we get the _Lock content, the actual contents of this meta might have changed (new records were added, the _Lock you retrieved might no longer exist or was updated).

So my point is: we have an availability issue, having lock timeouts and high times to access _meta because we try to ensure consistency (H2 or PG locks). But in 4GL, this doesn't seem to be true. In 4GL, the data access to _meta seems to be highly available without being able to lock (so low consistency). I am not sure how _meta is implemented, but I doubt it runs on the same engine as user defined DBs.

Moving the existing H2 to PG will carry over the theoretic limitation. It may be easier to implement in regard to stateless clustering at the expense of even lower availability.

I agree that LockManager that we use internally should be consistent and shared across servers in the cluster, but I would argue this is needed for _Lock. The only constaints it has is to have unique id and somemandatory columns. This can be ensured by FWD without much fuss.

The scenario I have in mind when writting this:

• a session is doing a find first over _Lock and gets that a certain record is exclusively locked by X
• the same session does find next over _Lock and gets that the same record is exclusively locked by Y

Technically, this doesn't look consistent, but as long as the FOR EACH query is iterating records one-by-one without being able to lock, it is possible!
So if one session will see the exclusive lock on Y later or sooner, it won't make much difference. The only caveat here is PRESELECT that would technically provide a snapshot of a consistent state. Thus, I would think that having a "stop-the-world" algorithm would make sense only for PRESELECT queries over _meta, which from my experience, are not the usual case. Yet, from my POV, this is the case we are striving to always honor at the expense of scalability.

Another example:

• a session is querying the _Lock with FIND and stays in a waiting loop till FIND on _Lock fails.
• after, it will start doing some work pretending there is no _Lock anymore. But this is incorrect, because between the FIND that did not find a _Lock and the actual business logic there may have been a window in which another session got a _Lock.

So my suggestion was in line maybe with Eduard of "thresholded batching". Each server in the cluster will handle their own H2 _meta table. We assess the FIFO and raise the scalability. The changes on it will be also put on a broadcast channel. Other servers will periodically get updates on _meta and update their own _meta. These may be stale, but this is exactly my POV. The only consistency part should be ensured for the same session. But this will always be true, because we can leave the H2 to be consistent within a single server, but non-consistent across cluster - yet highly available.

If we are to get aggressive about it, we can even have one private _meta for each session and synchronize the _meta between sessions from time to time. In other words, each _meta change will also be enqueued in a global queue. This queue is async processed and will write seldomly to other _meta. From a session POV, his _meta will have only two connections - its own and the global queue doing updates periodically.