Should I use Multitenancy or multiple sqld processes to optimize for concurrency?

[jakelowen]

Greetings - I Love this project.

I'm exploring the feasibility of transitioning to libsql for an application architecture that extensively relies on interacting with multiple databases concurrently. Our current setup involves managing a collection of discrete SQLite databases. When processing user actions, our system often performs simultaneous queries across these distinct databases, aggregating the results before presenting them to the user. Traditionally, this has been handled through multiple Node.js worker threads, each querying separate SQLite databases.

With the introduction of libsql and its network interface, I'm considering a shift towards a more streamlined approach. Specifically, I'm intrigued by the potential to utilize Promise.all() for executing concurrent queries, as opposed to managing multiple worker threads. This adjustment seems promising for simplifying our data retrieval logic significantly.

Given this context, I am weighing the benefits of adopting a multi-tenancy architecture within libsql against the alternative of deploying separate sqld instances for each database. My core inquiry revolves around performance implications:

• Would leveraging libsql's multi-tenancy capabilities offer comparable, if not superior, performance when executing concurrent queries across numerous databases (e.g., 100 databases), relative to managing an equivalent number of separate sqld instances each on their own port?
• Are there notable efficiency gains or trade-offs in network overhead, resource utilization, or query processing times between these two approaches?

Understanding the performance dynamics in these scenarios is crucial for my migration strategy, especially considering the potential scale of databases and the concurrency of operations involved.

I appreciate your insights and look forward to your recommendations.

[haaawk]

Hi @jakelowen
Let me try to address some of this.

It is usually recommended to use multi-tenancy instead of multi sqld processes. That's usually more economical. If you don't care about the cost then having each db in a separate sqld process might allow to reach higher throughput. On the other hand, sqld allows ATTACH so when all dbs are in a single sqld process you can potentially query them together with a single SQL.

Network is a complicated matter. The lowest overhead caused by the network is probably in single digit ms.

[hatemjaber]

I know this is from last year but from my limited experience.... the important thing is the service resources. If the server has enough memory and cpu it should be able to handle a large amount. I have tested on a hetzner vpc with 2 dedicate vcpu and 8 gb of ram and it runs like a champ. I did not test a high load of databases but I was doing a lot of testing with provisioning, forking, etc... and it was fine. The server was running other things like traefik, infisical, and other services, it was not dedicated to sqld alone. You can also pass individual jwt keys to each db instance if you want to do that as well. Unfortunately it was a lot of trial and error to figure it out because the documentation did not full address these topics. Anyhow, I think it would be overkill to have a single sqld instance per db, but that might not be the case in your situation.