5 ms·

I'm not sure if you're referencing the client libs or the server-side v8 integration. Either way, both are async. The client is async because there's network in

I'm not sure if you're referencing the client libs or the server-side v8 integration. Either way, both are async. The client is async because there's network in between. And the server-side v8 integration is async to schedule execution on a dedicated SQLite event loop. What you're saying makes a lot of sense. SQLite is sync and if you're program is alone accessing SQLite doing a single task, going sync is the way. If you're doing a lot of parallel work, both your JS event loop interleaving many tasks and several event loops accessing SQLite in parallel you have to make trade-offs. Specifically, `conn.query` may block for a long time w/o doing any work. Depending on your use-case it may or may not be ok to block the event-loop that entire period. TrailBase's setup is optimized to maximize throughput under highly concurrent loads, rather than minimizing latency in single-threaded workloads. That's not to say, TrailBase isn't quick. It's pretty low-latency even under load. However, if that's all you're after you're probably better off with better-sqlite3 or dropping down to C :). Does that make sense?

laurencerowe 2y ago
Im referring to the server-side v8 integration. > What you're saying makes a lot of sense. SQLite is sync and if you're program is alone accessing SQLite doing a single task, going sync is the way. If you're doing a lot of parallel work, both your JS event loop interleaving many tasks and several event loops accessing SQLite in parallel you have to make trade-offs. Specifically, `conn.query` may block for a long time w/o doing any work. Depending on your use-case it may or may not be ok to block the event-loop that entire period. In WAL mode SQLite is very good at supporting parallel reads from multiple threads. It should only block for a long time when writing (since writes require an exclusive lock.) It sounds like your v8 worker threads are mixing read and write work so you are running the query in another sqlite thread pool to prevent writes from blocking reads. > TrailBase's setup is optimized to maximize throughput under highly concurrent loads, rather than minimizing latency in single-threaded workloads. That's not to say, TrailBase isn't quick. It's pretty low-latency even under load. However, if that's all you're after you're probably better off with better-sqlite3 or dropping down to C :). Given the additional costs of cross-thread communication I would be surprised if this approach maximizes throughput under highly concurrent loads compared to segregating write requests into a dedicated thread and running read queries synchronously from within their threadpool with a single task per thread.
- trailbase 2y ago
  > In WAL mode SQLite is very good at supporting parallel reads from multiple threads. It should only block for a long time when writing (since writes require an exclusive lock.) Agreed. > It sounds like your v8 worker threads are mixing read and write work so you are running the query in another sqlite thread pool to preven> In WAL mode SQLite is very good at supporting parallel reads from multiple threads. It should only block for a long time when writing (since writes require an exclusive lock.) Agreed. > It sounds like your v8 worker threads are mixing read and write work so you are running the query in another sqlite thread pool to prevent writes from blocking reads. The v8 isolates run whatever you as a TrailBase user feed them. I would certainly expect writes to be a common occurrence. > Given the additional costs of cross-thread communication I would be surprised if this approach maximizes throughput under highly concurrent loads compared to segregating write requests into a dedicated thread and running read queries synchronously from within their threadpool with a single task per thread. Ultimately, it will depend a lot on the ratios. If you have mostly reads and the occasional write you're probably right. I did spend a bit of time exploring different execution models: https://github.com/ignatz/libsql_bench https://github.com/ignatz/libsql_bench in case you're interested. There's also some prior works from the folks GIL'ed languages (especially ruby) around how to wrangle write congestion for multi-process workloads. Sadly for them, they don't have inter-thread comms in their arsenal :) One big unknown for me is, how you'd clearly separate reads from writes. As far as I can think, you'd have to rely on users to pick the right sync or async funnel. Which may be ok at least for simple queries. FWIW, the thing or elephant that bothered me more than inter-thread comms is the opportunity cost of not running reads in parallel. Then at the same time, the current setup does seem to manage to saturate the machines I've run on. Very high core-count machines would probably be a different story. It will certainly also depend on how much actual other work the server has to do, i.e. is it just a glorified SQLite accessor? I certainly would love to further optimize that aspect. You seem very well informed so I'd love to hear your thoughts. Hit me up, if you'd like to chat more.t writes from blocking reads. The v8 isolates run whatever you as a TrailBase user feed them. I would certainly expect writes to be a common occurrence. > Given the additional costs of cross-thread communication I would be surprised if this approach maximizes throughput under highly concurrent loads compared to segregating write requests into a dedicated thread and running read queries synchronously from within their threadpool with a single task per thread. Ultimately, it will depend a lot on the ratios. If you have mostly reads and the occasional write you're probably right. I did spend a bit of time exploring different execution models: https://github.com/ignatz/libsql_bench https://github.com/ignatz/libsql_bench in case you're interested. There's also some prior works from the folks GIL'ed languages (especially ruby) around how to wrangle write congestion for multi-process workloads. Sadly for them, they don't have inter-thread comms in their arsenal :) One big unknown for me is, how you'd clearly separate reads from writes. As far as I can think, you'd have to rely on users to pick the right sync or async funnel. Which may be ok at least for simple queries. FWIW, the thing or elephant that bothered me more than inter-thread comms is the opportunity cost of not running reads in parallel. Then at the same time, the current setup does seem to manage to saturate the machines I've run on. Very high core-count machines may be a different story. It will certainly also depend on how much actual other work the server has to do, i.e. is it just a glorified SQLite accessor? I certainly would love to further optimize that aspect. You seem very well informed so I'd love to hear your thoughts. Hit me up, if you're willing to chat more.

laurencerowe 2y ago

Im referring to the server-side v8 integration. > What you're saying makes a lot of sense. SQLite is sync and if you're program is alone accessing SQLite doing a single task, going sync is the way. If you're doing a lot of parallel work, both your JS event loop interleaving many tasks and several event loops accessing SQLite in parallel you have to make trade-offs. Specifically, `conn.query` may block for a long time w/o doing any work. Depending on your use-case it may or may not be ok to block the event-loop that entire period. In WAL mode SQLite is very good at supporting parallel reads from multiple threads. It should only block for a long time when writing (since writes require an exclusive lock.) It sounds like your v8 worker threads are mixing read and write work so you are running the query in another sqlite thread pool to prevent writes from blocking reads. > TrailBase's setup is optimized to maximize throughput under highly concurrent loads, rather than minimizing latency in single-threaded workloads. That's not to say, TrailBase isn't quick. It's pretty low-latency even under load. However, if that's all you're after you're probably better off with better-sqlite3 or dropping down to C :). Given the additional costs of cross-thread communication I would be surprised if this approach maximizes throughput under highly concurrent loads compared to segregating write requests into a dedicated thread and running read queries synchronously from within their threadpool with a single task per thread.

trailbase 2y ago
> In WAL mode SQLite is very good at supporting parallel reads from multiple threads. It should only block for a long time when writing (since writes require an exclusive lock.) Agreed. > It sounds like your v8 worker threads are mixing read and write work so you are running the query in another sqlite thread pool to preven> In WAL mode SQLite is very good at supporting parallel reads from multiple threads. It should only block for a long time when writing (since writes require an exclusive lock.) Agreed. > It sounds like your v8 worker threads are mixing read and write work so you are running the query in another sqlite thread pool to prevent writes from blocking reads. The v8 isolates run whatever you as a TrailBase user feed them. I would certainly expect writes to be a common occurrence. > Given the additional costs of cross-thread communication I would be surprised if this approach maximizes throughput under highly concurrent loads compared to segregating write requests into a dedicated thread and running read queries synchronously from within their threadpool with a single task per thread. Ultimately, it will depend a lot on the ratios. If you have mostly reads and the occasional write you're probably right. I did spend a bit of time exploring different execution models: https://github.com/ignatz/libsql_bench https://github.com/ignatz/libsql_bench in case you're interested. There's also some prior works from the folks GIL'ed languages (especially ruby) around how to wrangle write congestion for multi-process workloads. Sadly for them, they don't have inter-thread comms in their arsenal :) One big unknown for me is, how you'd clearly separate reads from writes. As far as I can think, you'd have to rely on users to pick the right sync or async funnel. Which may be ok at least for simple queries. FWIW, the thing or elephant that bothered me more than inter-thread comms is the opportunity cost of not running reads in parallel. Then at the same time, the current setup does seem to manage to saturate the machines I've run on. Very high core-count machines would probably be a different story. It will certainly also depend on how much actual other work the server has to do, i.e. is it just a glorified SQLite accessor? I certainly would love to further optimize that aspect. You seem very well informed so I'd love to hear your thoughts. Hit me up, if you'd like to chat more.t writes from blocking reads. The v8 isolates run whatever you as a TrailBase user feed them. I would certainly expect writes to be a common occurrence. > Given the additional costs of cross-thread communication I would be surprised if this approach maximizes throughput under highly concurrent loads compared to segregating write requests into a dedicated thread and running read queries synchronously from within their threadpool with a single task per thread. Ultimately, it will depend a lot on the ratios. If you have mostly reads and the occasional write you're probably right. I did spend a bit of time exploring different execution models: https://github.com/ignatz/libsql_bench https://github.com/ignatz/libsql_bench in case you're interested. There's also some prior works from the folks GIL'ed languages (especially ruby) around how to wrangle write congestion for multi-process workloads. Sadly for them, they don't have inter-thread comms in their arsenal :) One big unknown for me is, how you'd clearly separate reads from writes. As far as I can think, you'd have to rely on users to pick the right sync or async funnel. Which may be ok at least for simple queries. FWIW, the thing or elephant that bothered me more than inter-thread comms is the opportunity cost of not running reads in parallel. Then at the same time, the current setup does seem to manage to saturate the machines I've run on. Very high core-count machines may be a different story. It will certainly also depend on how much actual other work the server has to do, i.e. is it just a glorified SQLite accessor? I certainly would love to further optimize that aspect. You seem very well informed so I'd love to hear your thoughts. Hit me up, if you're willing to chat more.