FWD » Core Development » Database

From Artur in #9802-210:

As already discussed, one of the main reasons for the differences in performance we see in FWD compared to 4GL is the following pattern:

for each <table>:
    execute queries against other tables than <table>
    execute some business logic, usually create records for a temp table
    if a certain <limit> is reached, exit the for each
end.

A solution to this problem and a potential performance boosting technique is result set streaming.

Result set streaming refers to the process of retrieving query results from a PostgreSQL server incrementally (in a stream), rather than all at once.
This would naturally solve the cased where only a certain number of records are needed.
Another added benefit is that the business logic inside the for each can be executed as soon as the first batch of results arrive.

As a POC, this example iterates over 200 records from the oeeh table, using a query we are familiar with:

import java.sql.*;

public class Test {

    private static final String URL = "jdbc:postgresql://localhost:5434/CUSTDBNAME";
    private static final String USER = "fwd_admin";
    private static final String PASSWORD = "admin";

    public static void executeQuery(String url, String user, String password, String sqlQuery) {

        try {
            int limit = 200;
            int fetched = 0;
            try (Connection conn = DriverManager.getConnection(url, user, password)){
                conn.setAutoCommit(false);
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                stmt.setFetchSize(50);
                long start = System.currentTimeMillis();
                ResultSet rs = stmt.executeQuery();
                while (rs.next()) {
                    if (fetched >= limit){
                        rs.close();
                        break;
                    }
                    fetched++;
                }
                System.out.println(System.currentTimeMillis() - start);
            }
        } catch (Exception e) {
        }
    }

    public static void main(String[] args) {

        String query = "select *\n" +
                "from\n" +
...customer_details_redacted...
                         +
                " limit ('1000'::int4)";
        executeQuery(URL, USER, PASSWORD, query);

    }
}

If conn.setAutoCommit(false); and stmt.setFetchSize(50); is removed, we get the usual retrieval of the results.
I disabled SEQ_SCAN so the query is fast, with slower queries the benefit will be much larger.
With result set streaming the time it took to iterate over the desired amount of records is 50ms, doing it the usual way takes 100ms.
Result streaming does not introduce a significant performance drop, because after all, only one query is being executed, indeed there are more round trips to the database, but they are quite quick compared to a query execution.

Going a step further, all our queries are blocking operations, I wonder if we can use asynchronous programming in combination with result streaming to execute the business logic while the records are being retrieved in another thread, there are a few options for this, the most common ones are CompletableFuture and producer-consumer pattern.

I suspect there are aspect that I missed, I would love some input on the idea before moving further with the implementation.

This seems quite promising, I especially like the idea of adding threading in to keep the data transfer happening in the background. I think we should investigate this.

One approach for implementation would be to measure conditions at runtime and shift into this streaming/async mode when we detect that the conditions are right.

I think this implies tracking and storing the time of execution for queries and iterative blocks that have queries (also other metadata, nr of queries in a block for example), then on subsequent runs, based on the history, deciding whether to go async or not. I don't think we could predict the latency or the overall time in advance.

• The blocking nature of ResultSet.next ensures that the body of the ForEach recieves at least FetchSize records.
• The for each body (business logic) does not need all the records from result set to be executed, at least one at a time is enough.
• Using a single parallel thread for the body ensures the correct and consistent order of execution.
• If the query and business logic have realistic latency/execution times, async is a great way to reduce the combined time.

Below are the results for retrieving 2000 records and executing some logic over each row, each processing takes exactly 1ms.
I introduced various artificial DB server side latency to simulate a production env.

          query time    overall time    improvement for async
Async     7ms           2853ms          3% (error margin)
Sync      7ms           2947ms          ---

          query time    overall time    improvement for async
Async     350ms         2822ms          23%
Sync      350ms         3690ms          ---

          query time    overall time    improvement for async
Async     1000ms        2859ms          30%
Sync      1000ms        4105ms          ---  

          query time    overall time    improvement for async
Async     2200ms        2926ms          43%
Sync      2200ms        5206ms          ---

And again, if the latency is high enough, async provides a way of considerably improving performance. With async, the total execution time of a scenario = slowest component of the for each (the query or the body).
I attached the source for this test.

I started working on introducing async in forEachWorker, currently there are issues with our security contexts that do not agree with multi-threading, but I believe it can be done.

On another note, I found a way to introduce artificial latency on a network adapter.
The delay is per tcp packet, not sql query.

sudo tc qdisc add dev lo root handle 1: prio
sudo tc qdisc add dev lo parent 1:3 handle 30: netem delay 10ms
sudo tc filter add dev lo protocol ip parent 1:0 prio 3 u32 match ip dport 5434 0xffff flowid 1:3

// Reset
sudo tc qdisc del dev lo root

This introduces a 10ms delay for all packets going from the pg cluster using port 5434 to the localhost.
The method also works for remote network adapters and docker virtual networks. Replace lo with the desired adapter.

Artur Școlnic wrote:

I started working on introducing async in forEachWorker, currently there are issues with our security contexts that do not agree with multi-threading, but I believe it can be done.

In SecurityContextStack

   /** Per-thread reference to the security context. */
   private static ThreadLocal<SecurityContextStack> contextPointer = new ThreadLocal<>();

I don't think we should look at pushing 4GL logic into other threads. Our core design will not allow this without a substantial rewrite. The original 4GL envirobment is single threaded and we rely upon this everywhere. We don't synchronize anything that is context-local, because we assume it is essentially running on the same "main" thread. That code can call anything, accessing persistent handles, object instances, shared resources etc... that cannot be safely accessed from another thread. In addition to this, any of that code can invoke runtime functionality (UI, filesystem access, memptrs, native API calls...) that calls down to the client and that must be done on that same "main" thread as well.

It is feasible to handle a limited bit of result set processing on other thread. I don't think it is feasible to do this for arbitrary 4GL code without a very deep rewrite.

Well, this changes things. If the for each body cannot be executed in a new thread, I will go the other way and try to execute the query in another thread.

The java test cases show that the benefit of running the query in a separate thread is similar with running the business logic in a separate thread. I started working on the implementation in forEachWorker, from my initial experience I can say that we will need to copy some of the context from the main thread to the new thread, also thread safety will need to be implemented in places that a P2jQuery touches.

At the meeting, Alexandru raised a valid concern regarding the async execution for the foreach query. In my initial implementation, I am using a separate thread to fetch the results, while the for each body is executed all while using a single database connection. This poses a problem because the for each body has queries of it's own that will use the same connection. We can approach this issue two ways:
1. synchronize the database access and use the same connection. The persistent DB io will not be async, but the business logic will still be executed in parallel. AFAIK concurrent db access is not implemented in pgsql/mariadb, so this approach fails.
2. use a connection pool and use a different connection for each new thread.

Alexandru correct me if I am wrong, but you mentioned that using different connections means loosing transactional support for the loop query. If that cannot be avoided, this leaves us no viable options.

Using different connections is a big NO from my POV. If you have X nested FOR EACH, then you may end up with a session blocking 10 connections to the database. The connection pool is not that big to assist with that. Such connections are precious resources. Nevertheless, the transaction is no longer available to the other connection.

synchronize the database access and use the same connection. The persistent DB io will not be async, but the business logic will still be executed in parallel. AFAIK concurrent db access is not implemented in pgsql/mariadb, so this approach fails.

This is still a possible solution, but may not provide that big expectation as set in #10169-1. There may be other IO inside the FOR EACH loop that will be processed async (like _temp queries), so technically it will still process things in parallel. I don't think we should abandon this idea completely, but rather set the expectations right. The only concern is that this will require more thinking and design time. With this approach, I also worry that the main thread will wait the separate thread to fetch records that won't be ever used. In the example from #10169, you may not need records from 1000th onward. But with an implementation of async fetching, you may end up gathering record 100.000th and your main thread will wait for that - yuck!

Alexandru Lungu wrote:

But with an implementation of async fetching, you may end up gathering record 100.000th and your main thread will wait for that - yuck!

I don't think the query will fetch the records continuously, the current implementation is:

 Future<Boolean> nextFuture = fetchExecutor.submit(() -> futureQueryHelper(query, stack));
 while (true)
 ...
          boolean hasNext = nextFuture.get(); // wait for result
          if (!hasNext)
          {
              ...
          }
          nextFuture = fetchExecutor.submit(() -> futureQueryHelper(query, stack)); // execute the 'next', retrieves one record
          ...
          if (processForBody(wa, block, to, expr, on, defOn))
          {
              break loop;
          }

I am more concerned about multiple results sets being open with a single connection, I am not sure if that is possible.

We might want some kind of "read ahead" limit to avoid doing too much on the secondary thread.

I am not sure how we could end up retrieving more records than indented. We still have the progressive results, the emitted query does not change, the only thing that changes is that, starting from the second record, retrieving the next record happens in a different thread.

I was thinking that you retrieve all batches that are going to be retrieved by ProgressiveResults, not only the next. Your approach is right then. It will prefetch only the next batch. This is nice.

I am more concerned about multiple results sets being open with a single connection, I am not sure if that is possible.

The question should have included the statement as well. One connection can generate multiple prepared statement for the same SQL, if all are going to be opened simultaneously. If you open - close - open - close - open -close the statement, c3p0 will eventually provide you the same statement all over again.

The result-set however is bound to the lifecycle of the statement. So if you close the statement, the result-set will be also closed.

After a quick investigation, I found out that most JDBC drivers support multiple opened result sets on a single connection as long as they come from different statements, what they do not support, is asynchronous operations on a same connection, we will need to synchronize the access to the result sets so that there is no overlap. Also, trying to execute a query on the same statement will immediately close the previously opened result set.

Driver          Multiple Open ResultSets (via different Statements)
PostgreSQL      ✅ Yes
MySQL (8.x)     ✅ Yes (use useCursorFetch=true for large data)
Oracle          ✅ Yes
SQL Server      ✅ Yes
SQLite          ⚠️ Limited, depends on implementation
MariaDB         ✅ Yes

we will need to synchronize the access to the result sets so that there is no overlap.

I don't quite understand where is this required. I can't tell how a single result-set is used by multiple threads at the same time. I can tell that a connection can be used by main and separate thread at once:

for each tt: // thread 1 postpones next batches to thread 2 which uses statement 1 built on connection 1 -> the results are returned back to thread 1 once fetched
find first tt2. // thread 1 uses statement 2 also built on connection 1, so it needs synchronization on the connection resource
end.

How is the result-set provided by for each tt going to be used concurrently? Once it is retrieved by thread 2, it will be provided back entirely to thread 1 - so there is no concurrent access.

Artur Școlnic wrote:

we will need to synchronize the access to the result sets so that there is no overlap.

I meant the access to any result set by a thread should not overlap with the access of another result set, not that the same result set will be accessed by different threads.

I meant the access to any result set by a thread should not overlap with the access of another result set

I don't quite understand this statement. How is the access of a result-set going to overlap with the access of another result-set if we are talking about a single thread?

FYI, the following example executes two different statements in the DB and fetches 2 different result-sets. One can do operations on the result-sets independently and intertwined.

open query q for each tt.
open query q2 for each tt2.
get next q. // row 1
get next q2. // row 1
get next q. // row 2

I see. I wonder if you can't simply pass the result-set to the main thread and use it from there rather than using it from the secondary thread. I imagine the secondary thread should only delay the execution of the statement, but the results can simply be brought back to the main thread and let it use it. This way, you don't have to deal with result-sets being used by different threads and synchronize.

My goal is to delegate as much work as possible to the secondary thread, ideally the whole query.next, this includes the result set access and row fetching, I believe synchronizing result set accesses within a single connection is achievable.

Well, it may be a bit more complex to implement but a solution would be to use a pool of worker threads. Consider the connection to a database as a context (possible with some additional information necessary for a statement to be executed). They do not need a thread associated all the time. But when FWD decided the code from a client requires a background execution, the context is set to one of the threads from the pool and executed. The worker thread is released right after the result is updated. The chances are that the next chunk of data will be be fetched by another thread. If all threads in the pool are temporarily busy, the query is executed on client's thread, as it does now.

Ovidiu Maxiniuc wrote:

But when FWD decided the code from a client requires a background execution, the context is set to one of the threads from the pool and executed.

The code from the client cannot run in another thread other than the main one.

Maybe I am not understanding something, but i am not sure why we need a pool of threads, currently I am creating a new single thread at the beginning of a for each (using Executors), assign the context to the new (secondary) thread, execute the fetching there. But the issues still remains with the result set access collision with the queries in the for each body.

Well, it may be a bit more complex to implement but a solution would be to use a pool of worker threads. Consider the connection to a database as a context (possible with some additional information necessary for a statement to be executed). They do not need a thread associated all the time. But when FWD decided the code from a client requires a background execution, the context is set to one of the threads from the pool and executed. The worker thread is released right after the result is updated. The chances are that the next chunk of data will be be fetched by another thread. If all threads in the pool are temporarily busy, the query is executed on client's thread, as it does now.

This sounds nice.

Artur, the fundamental flow of generating one thread per for-each is that you may end up with thousand of threads in the end (i.e. 100 sessions * 10 for each blocks), that most probably will end up idle once finishing the query.next part.
Ovidiu suggest creating a thread pool of a limited number of such threads. The need to fetch something from the database can be considered as a job. A thread is being checked-out, attached to the context of the session, the job is delegated to it and after it is completed with a well defined output, it can be checked in back into the pool.

But the issues still remains with the result set access collision with the queries in the for each body.

Can we have a prototype first without the need to actually hydrate the records on the separate thread? We can view this as an improvement further down the line, but IMHO the plate is quite full already with the need of scheduling database access on separate contextual threads and see how that goes. It would be a nice "a" branch from my POV.

Ok, I revised the code, indeed I am creating one thread each time a for each is called and leaking threads, but just terminating them at the end of the for each should be enough. Although this approach could perform worse than Ovidiu's proposal.

• Two databases (e.g., PostgreSQL + MySQL)
• A database + a JMS queue
• Multiple JDBC connections

• use C3P0 for simple, local database connections where you don't need distributed transactions.
• use Atomikos (with XADataSources) only for connections participating in distributed XA transactions.

I suspect we will not be replacing our trusty C3P0 with a new dependency so, maybe we can implement the transactional support for multiple JDBC connection ourselves?..
This problem reminds me of dirty share issues and we managed to solve that.

Alternatively, we can keep using the same connection, but, enable the async for each only for the blocks that do not execute queries against a persistent database. I think we can determine at conversion time if a for each block is performing persistent DB operations and annotate it accordingly.

I also think we can determine if the queries in the for each alter the iterated table, if not, enable async, but create a different connection for the new thread.

I really don't want to purge some historical philosophy from FWD (JDBC + connection pooling + one connection per thread) favoring more complexity (that may take months to implement, let alone review, test and maintain). I am still not convinced that you are hitting a show-stopper within the current path of one single connection shared by multiple threads. I am afraid that we are aiming for a leap too big, trying to get both results streaming and async queries in a single effort. FYI #7991 (result streaming) already took almost 1 year to complete and did not make it properly in trunk. I suggest having more baby steps.

• that connection becomes effectively blocked? What is the actual statement that becomes blocking; is it rs.next()?
• ResultSet with server-side cursors? These are result-sets generated by the JDBC to "stream" results from the database and are usually triggered in FWD by FORWARD-ONLY, non-auto-commit mode and non-zero fetch-size? If so, I recall not using those for a FOR EACH. Even if they are used, they shouldn't be coupled with multi-threading anyway because are used within a ScrollingResults, not a ProgerssiveResults
• the second ResultSet is fully consumed or closed? What do you mean by fully-consumed? In SCROLLING SQL queries generated by JDBC you can go NEXT/PREV/FIRST/LAST. So I can't tell when is the moment of fully consumption.

Lastly, I think the limitation you are exposing is mostly because you are also deferring the business logic to the separate thread including hydration. IMHO, this makes the separate thread too specialized ... like a Swiss knife :) Gathering result-sets, iterating, hydrating, use session caching, etc. all withing a streaming results context. Eventually, I think you are hitting other areas in FWD that weren't meant to be synchronized, let alone stressing the JDBC resources which weren't meant for concurrent access in the first place.

Can you attempt to lower down the responsibilities of the second thread to just execute the statement without streaming results? The result-set is provided back to the main thread and that is all. We need to understand first and foremost if this is possible, before delegating more work to the other thread.

In a dream world, I would see a "LazyDatabaseAccessThread" that is pooled and simply runs stmt.execute. The statement can be gathered as a parameter. The thread should be super-simple and eventually provide the result-set. This allows any arbitrary code from FWD to defer its database access, not only a ProgressiveResults. With a nice API of a dispatcher, even Direct Java Access can do that.

Concretely, can you focus on having an example like #10169-5, but with only one connection? You can skip streaming for now. We did that in the past and we had to revert it from trunk due to performance drawbacks. It has a high chance of not being feasible. See #7991 - the historical effort of results streaming. It was a failure at first try. I would prefer having #7991-118 clarified before being so aggressive about forward only queries.

Lets focus solely on async queries and understand if these can happen even in non-streaming result-sets.

PS: according to https://www.postgresql.org/docs/7.4/jdbc-thread.html, in 7.4 any connection statement is blocking. This could have changed in the mean-time. I can't tell if c3p0 has any kind of limitation in this area.
Personally, I have a bad feeling that c3p0 and JDBC driver designers simply used syncrhonized on the connection access :/ But experiments and investigation will tell.

To answer Alexandru's questions, the JDBC driver does not allow for concurrent result set traversal within a single connection, furthermore, the connection seems to be synchronized and an additional executed statement blocks the previously opened result set until the new one is closed, this is why using a single connection by two threads will have absolutely no effect on the executed queries and results consuming, at least no positive effect. One connection + multiple threads using it = bat idea.

At the meeting, Constantin proposed using two connections, creating one new connection for the secondary thread that executes the for each query and fetches the results, only if the for each block is not in a transaction, which seems to solve the 'breaking' transaction issue. If in the block there are statements that alter the iterated query, existing measures will be used.

If I understand correctly, Alexandru proposes to narrow the affected scenarios to progressive results only, which will see the biggest improvement, because of the multiple queries being emitted by a single 4gl statement.

I agree with both ideas, but again, I don't see how using a single connection will result in any benefit for us regardless of what we do in the secondary thread.

To answer Alexandru's questions, the JDBC driver does not allow for concurrent result set traversal within a single connection, furthermore, the connection seems to be synchronized and an additional executed statement blocks the previously opened result set until the new one is closed, this is why using a single connection by two threads will have absolutely no effect on the executed queries and results consuming, at least no positive effect. One connection + multiple threads using it = bat idea.

This is comprehensive. Thank you for the update. I thought we can leverage some niche features in the JDBC to just not block the main thread while waiting for the results.

At the meeting, Constantin proposed using two connections, creating one new connection for the secondary thread that executes the for each query and fetches the results, only if the for each block is not in a transaction, which seems to solve the 'breaking' transaction issue. If in the block there are statements that alter the iterated query, existing measures will be used.

Hmm, this makes sense. Invalidation occurs if changes are made, so the thread efforts should just be disregarded.

Using more connections is reasonable, but you should take into account that these are usually pooled. So we would need to at least double the c3p0 pool size once this change gets further down the path.

I want to document on the task what exactly could the async 'for each' achieve and when it is especially beneficial.

1. Slow queries will not become faster in any way, the time it takes the DB server to plan execute and return the first row is the same, no matter what the client does.
2. Queries that return a large result set, will benefit because the fetching of the rows, starting from the second one, happens in background, while the for each body gets executed in the main thread.
3. The biggest uplift in performance, and the main reason this task was created, will be seen with ProgressiveResults. In theory, just the first batch of the 'progressive query' will be blocking, all the subsequent queries, execution and results fetching, should be in the background.

The benefits will not be visible in a system that hosts the database locally, a realistic latency has to be present.

I want this to also be documented in case the discussion about using one connection in a multithreaded environment arises ever again.

1. The PostgreSQL JDBC driver is not thread safe. The PostgreSQL server is not threaded. Each connection creates a new process on the server; as such any concurrent requests to the process would have to be serialized. The driver makes no guarantees that methods on connections are synchronized. It will be up to the caller to synchronize calls to the driver.

2. We require that all operations on all the java.sql objects be multi-thread safe and able to cope correctly with having several threads simultaneously calling the same object. Some drivers may allow more concurrent execution than others. Developers can assume fully concurrent execution; if the driver requires some form of synchronization, it will provide it. The only difference visible to the developer will be that applications will run with reduced concurrency.

• Driver behavior varies between databases; some may serialize internally, others may throw errors unpredictably.
• JDBC spec does not guarantee thread safety, so behavior is not portable.
• Best practice: Use a connection pool and give each thread its own Connection.