Would more threads help performance?

[zhenpingcui]

Hi, we’re looking into solutions that improve operations on multiple databases.

There are some cases that, the app opens / reads / writes multiple databases in a short period of time. For example, during the loading phase, the app loads data from multiple sources. In current android implementation, all method calls go to the same thread. Would you be willing to share more context about the design idea behind that?

We did some experiment that has a small thread pool in the plugin. Method calls to different databases could be run in parallel. And method calls to the same database instance are only run in one thread at any given moment and they can still be run in first in first out manner. The experiment shows a positive result that one thread can be run when others are in "uninterruptible Sleep (IO)" state.

Should we consider introducing more threads? Thanks!

[alextekartik]

Thanks for the performance testing that I have not done much especially in a multiple databases context. Having more threads is a very good idea if it improves performance and I think this could be implemented in sqflite (native, more threads on Android, iOS/MacOS), sqflite_common_ffi (ffi, more isolate), sqflite_common_ffi_web, (web, more workers). I can think of the following scenarios:

• a pool of databases per thread (currently basically we have one thread for all databases, we could for example allow 2 or more databases on a given thread (I prefer keeping one database on one given thread).
• a pool of threads.
• one database = one thread
• a mix of both, limiting the number of threads

Well basically the developer using sqlite has to know what he is doing, hard to be a generic solution at this point without some testing first and understand the limits if any. If indeed it does not cost more to have 4 threads for 4 databases, we could consider having better default values.

One thread is basically the cheapest and it works well in a common app scenario: one database. We have to keep this option.

This could be a global parameter on the database factory (thread pool count) or an additional parameter in SqfliteOpenDatabaseOptions (use a dedicated thread).

Let me know your thoughts, sorry if i'm late to reply, I might not tackle this before a few weeks.

[zhenpingcui]

Shall we start with sqflite Android implementation? I can make a change proposal where we can have deeper discussion.

The proposal is something that you mentioned as 'a mix of both, limiting the number of threads' and 'a global parameter on the database factory (thread pool count)'.

By default, it will not change today's one-thread behavior. The optimization will only take effect when the thread count is larger than one.

[alextekartik]

Shall we start with sqflite Android implementation?

Yes android is the easiest to start with.

I can make a change proposal where we can have deeper discussion.

Great!