Advice for the use of isolates

[frankvollebregt]

Hello. Our app has been working quite well, but a few of our customers are unfortunately experiencing some pretty poor performance. Without boring you with any of the details, our Drift database has around 70 tables. Many parts of the UI are built using StreamBuilders, which works great.

There is a second isolate running in the background, which is responsible for synchronizing data with the server (specifically uploading, the UI does not have to change as a result of these uploads).

Currently, I am creating the Drift database in the background, registering its port in the IsolateNameServer:

  static Future<QueryExecutor> _getBackgroundExecutor(File file) async {
    // Either get the existing connect port, or create a new connection
    final existingPort = IsolateNameServer.lookupPortByName(databaseIsolate);

    if (existingPort != null) {
      // We can connect to the existing database isolate!
      return DriftIsolate.fromConnectPort(existingPort).connect();
    } else {
      // We need to spawn a new isolate and register it
      final isolate = DriftIsolate.inCurrent(() => NativeDatabase(file));

      IsolateNameServer.registerPortWithName(
        isolate.connectPort,
        databaseIsolate,
      );

      return isolate.connect();
    }
  }

Which works great in terms of stability, but seems to be lacking in performance. In practice, my background isolate gets a message sent to it whenever there is data to upload, so it's not a requirement to share the connection.

Practically, when I directly use NativeDatabase(file) the process running a number of queries that would (on a pretty slow Android 8.1.0 device with a pretty mediocre SoC) take about 13 seconds suddenly completes in about 2 seconds. The main disadvantage is that this does seem to block the UI of the app while the process is running.

Question

My question (inquiry?) then, is as follows: what way would you recommend me to set up the database connections in this case? Should my background isolate just use an entirely separate connection to the database file? Or is it sensible to spawn this third isolate for common use? Could the use of a MultiExecutor help at all in this case?

So what I have:

• A main isolate running the app, which needs the database for its stream builders to read, and to write data too
• A background isolate that, independently of the main isolate, needs to read from and write to the database in the process of uploading data to the server

Sorry if it's a bit vague, but I'm curious to know what the recommended way to set up something like this is.

[simolus3]

When using drift isolates, there is a slowdown due to the asynchronous communication channels used to issue database requests. So operations take longer in total, but the actual time spent synchronously processing them on the isolate accessing the database is lower. That makes them a good choice for UIs which are typically asynchronous to begin with, and also more susceptible to lag caused by blocking IO.

As you've seen, this model is not a good fit for work already happening on a background isolate. We're not blocking the UI here, so we can afford synchronous database IO and avoid the communication overhead. Opening a second database connection is fine, but opens you up to concurrency issues (not threatening correctness, but possibly causing "database is locked" errors).

The easiest part is if the background isolate is not doing any writes at all. In that case, just enable WAL mode so that writes made in the UI don't block background reads and you can safely open a second connection.
Handling concurrent writes from the UI and the background isolate is tricky - WAL alone doesn't help. You can instruct sqlite3 to perform busy retries instead of reporting a locked error directly, but managing long-running transactions can be a pain. Since drift isolates are already handing out semaphores asynchronously, they can be used to manage this problem. In that setup you'd have:

1. One drift isolate.
2. Your UI database connecting to the drift isolate.
3. Your background isolate with a MultiExecutor using a second instance of the database connection for reads and the drift isolate for writes.

Be aware that transactions in a MultiExecutor are then being managed through the drift isolate. So a long-running transaction by your background isolate would block reads from the UI. But apart from that, this setup should give you a better read performance in the background isolate.

[frankvollebregt]

Thank you for the response! The main issue currently is the scenario where:

• The user taps a button
• A CircularProgressIndicator appears
• The processing happens (currently on the Drift Isolate, but called from the main isolate), taking well over 10 seconds on the older device
• Once done, the user is sent to the next screen

But I suppose there is no way to do the processing a) almost as quickly as a foreground process but also b) without causing the UI to hang completely?

I also suspect the queries are taking longer because while processing, the stream builders (that aren't even visible at the time, another route is pushed to the navigator) are rebuilding repeatedly. Unfortunately, the queries depend on each other in some cases, so I can't wrap everything inside a single transaction.

If you have any other pointers to offer me I'd love to hear them, otherwise thank you for the great support as always 😄

[simolus3]

But I suppose there is no way to do the processing a) almost as quickly as a foreground process but also b) without causing the UI to hang completely?

It's not easy to say for sure. If there are lots of small queries, you could try running them on the main isolate and see how bad it gets - depending on the underlying data sets there might not be that much IO happening for them. You probably still want the isolate for writes, but then you give both the background and UI isolate a local database connection for reads.

the stream builders (that aren't even visible at the time, another route is pushed to the navigator) are rebuilding repeatedly

Streams created by drift respect being paused and will temporarily stop listening for changes in that case. StreamBuilder unfortunately never calls StreamSubscription.pause, but those happen to cause significant load, it might make sense to manually pause the subscriptions on previous pages.

[frankvollebregt]

After struggling for a bit trying to set up the stream builders/subscriptions to pause and resume (without too much progress), I was discussing with a colleague and remembered about nested transactions. In theory, this would resolve the issue by only emitting a single event for the stream builders, but I guess the database would get locked, and the background isolate would no longer be able to do anything with the database while this process is running.

The process can also be 'interrupted' to ask for some user input, and keeping a transaction alive while this is happening sounds like a terrible idea.

What I could thus do instead, is start a transaction when the background processing starts, and end it when the user starts to interact, and start a new one when background processing continues. This should make the loading times between interactions significantly shorter in theory.

However, ideally I'd want to freely decide where to BEGIN and END/COMMIT the transaction. I suppose I can try with some customStatements, but I feel like this might interfere with the whole nested transactions setup Drift uses. Or will Drift automatically realise it's in a transaction and apply any nested transactions in my code using SAVEPOINT/RELEASE?

[frankvollebregt]

I have just gotten it working by pausing the stream builders, which is much less prone to errors and works great now. I'll update this thread with a code snippet or gist this afternoon, for anyone else trying to achieve this :)

[frankvollebregt]

I set up a gist here, with some code for a route aware StreamBuilder that automatically pauses and resumes its stream as a different route is pushed/popped above it :)

https://gist.github.com/frankvollebregt/b1f4ce1b076858319b2f92004451ecbc