Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: SQLite Transaction Benchmarking Tool

github.com

Post reply on HN
Print view View original

11–20 of 23 posts

Re: Show HN: SQLite Transaction Benchmarking Tool

#11

Post by Moehassan »

leononame wrote: post #10
rmbyrro wrote: post #9

Earlier quoted context omitted.

If you have a server with 100 cores to serve 100 connections simultaneously - and really need this setup -, you should probably be using Postgres or smth else.

It's a made up example to clarify whether I understand potential congestion scenarios and limitations correctly, not my actual situation. If I had a server with 100 cores to serve 100 connections, but each query took only 5ms, SQLite might be totally viable. There's no blanket solution. Edit: More importantly, SQLite async limitations come into play when I have only 12 cores but 100 incoming connections, and on top o…

You can make SQLite scale way beyond the limitations of WAL mode or even Begin Concurrent mode, all while doing synchronous writes

https://oldmoe.blog/2024/07/08/the-write-stuff-concurrent-wr...

Re: Show HN: SQLite Transaction Benchmarking Tool

#12

Post by matharmin »

leononame wrote: post #2

Thanks for the interesting article. Lots of things seem to happen in SQLite land at the moment and I appreciate that the SQLite team documents their quirks so openly, it gives great confidence. Since I don't know where else to ask, maybe this is a good place: How do async wrappers around SQLite (e.g. for node or python) work? SQLite only uses synchronous I/O if I'm not mistaken. Is it just a pretend async function wi…

You get concurrency in SQLite by using multiple connections - and typically a dedicated thread per connection.

When using async wrappers, a good solution is connection pooling like you mentioned - exactly the same concept as used by client->server database drivers. So you can have 5 or 10 read connections serving those 100 connections, with a statement/transaction queue to manage spikes in load. It's probably not worth having more connections than CPUs, but it depends a little on whether your queries are limited by I/O or CPU, and whether you have other delays in your transactions (each transaction requires exclusive use of one connection while it's running).

SQLite maintains an in-memory cache of recently-accessed pages of data. However, this gets cleared on all other connections whenever you write to the database, so is not that efficient when you have high write loads. But the OS filesystem cache will still make a massive difference here - in many cases your connections will just read from the filesystem cache, which is much faster than the underlying storage.

Open connections don't block checkpointing in SQLite. The main case I'm aware of that does block it, is always having one or more active transactions. I believe that's quite rare in practice unless you have really high and continuous load, but if you do then the WAL2 branch may be for you.

I feel connection pooling is much more rare in SQLite libraries than it should be. I'm maintaining one implementation (sqlite_async for Dart), but feel like this should be the standard for all languages with async/await support.

Re: Show HN: SQLite Transaction Benchmarking Tool

#13

Post by simonw »

leononame wrote: post #2

Thanks for the interesting article. Lots of things seem to happen in SQLite land at the moment and I appreciate that the SQLite team documents their quirks so openly, it gives great confidence. Since I don't know where else to ask, maybe this is a good place: How do async wrappers around SQLite (e.g. for node or python) work? SQLite only uses synchronous I/O if I'm not mistaken. Is it just a pretend async function wi…

I wrote an async wrapper around SQLite in Python - I'm using a thread pool: https://github.com/simonw/datasette/blob/main/datasette/data...

I have multiple threads for reads and a single dedicated thread for writes, which I send operations to via a queue. That way I avoid ever having two writes against the same connection at the same time.

Re: Show HN: SQLite Transaction Benchmarking Tool

#14

Post by leononame »

Moehassan wrote: post #11
leononame wrote: post #10

Earlier quoted context omitted.

It's a made up example to clarify whether I understand potential congestion scenarios and limitations correctly, not my actual situation. If I had a server with 100 cores to serve 100 connections, but each query took only 5ms, SQLite might be totally viable. There's no blanket solution. Edit: More importantly, SQLite async limitations come into play when I have only 12 cores but 100 incoming connections, and on top o…

You can make SQLite scale way beyond the limitations of WAL mode or even Begin Concurrent mode, all while doing synchronous writes https://oldmoe.blog/2024/07/08/the-write-stuff-concurrent-wr...

If synchronous IO is blocking your CPU bound application code, this won't help you. My made up example was not about concurrent writes, and the concurrent reads I mentioned were not my main point. For all I care, you could have 100 different databases or even normal files in this scenario and you read them.

I was wondering how the async wrappers around SQLite work when SQLite itself only has synchronous IO. At least for the Rust example by Op, the async part is only used when awaiting a queue, but the IO itself still has the potential of blocking all your application code while idling.

Re: Show HN: SQLite Transaction Benchmarking Tool

#15

Post by pdimitar »

matharmin wrote: post #12
leononame wrote: post #2

Thanks for the interesting article. Lots of things seem to happen in SQLite land at the moment and I appreciate that the SQLite team documents their quirks so openly, it gives great confidence. Since I don't know where else to ask, maybe this is a good place: How do async wrappers around SQLite (e.g. for node or python) work? SQLite only uses synchronous I/O if I'm not mistaken. Is it just a pretend async function wi…

You get concurrency in SQLite by using multiple connections - and typically a dedicated thread per connection. When using async wrappers, a good solution is connection pooling like you mentioned - exactly the same concept as used by client->server database drivers. So you can have 5 or 10 read connections serving those 100 connections, with a statement/transaction queue to manage spikes in load. It's probably not wor…

> I feel connection pooling is much more rare in SQLite libraries than it should be. I'm maintaining one implementation (sqlite_async for Dart), but feel like this should be the standard for all languages with async/await support.

I completely agree. But I simply have no reference / good-practice implementations to take inspiration from. I'd be more than willing to have an Elixir FFI bridge to a Rust library (and write both in the process) that actually make full use of parallelism to fully utilize SQLite's strengths but again, I got nowhere to steal from. :) Or I am not aware where to look.

Re: Show HN: SQLite Transaction Benchmarking Tool

#16

Post by seddonm1 »

pdimitar wrote: post #8
seddonm1 wrote: post #7

Earlier quoted context omitted.

Whilst I love the idea of SQLX compile-time checked queries it is not always practical to need a database connection to compile the code in my experience. If it works for you then thats great but we had a few tricky edge cases when dealing with migrations etc. Also, and more fundamentally, your application state is the most valuable thing you have. Do whatever you feel makes you most comfortable to make sure that sta…

Thank you, good perspective. Weren't the compile-time connections to DB optional btw? They could be turned off I think (last I checked, which was last year admittedly). My question was more about the fact that sqlx is integrated with tokio out of the box and does not need an extra crate like rusqlite does. But I am guessing you don't mind that.

SQLX has an offline mode where it saves the metadata of the SQL database structure but then you run into risk of that being out of sync with the database?

Yeah I just drop this one file [0] into my Tokio projects and I have a SQLite with single writer/multi reader pool done in a few seconds.

[0]: https://github.com/seddonm1/s3ite/blob/0.5.0/src/database.rs

Re: Show HN: SQLite Transaction Benchmarking Tool

#17

Post by rmbyrro »

leononame wrote: post #10
rmbyrro wrote: post #9

Earlier quoted context omitted.

If you have a server with 100 cores to serve 100 connections simultaneously - and really need this setup -, you should probably be using Postgres or smth else.

It's a made up example to clarify whether I understand potential congestion scenarios and limitations correctly, not my actual situation. If I had a server with 100 cores to serve 100 connections, but each query took only 5ms, SQLite might be totally viable. There's no blanket solution. Edit: More importantly, SQLite async limitations come into play when I have only 12 cores but 100 incoming connections, and on top o…

> 12 cores but 100 incoming connections

Especially when using a modern storage medium, which most servers nowadays use, I doubt that filesystem I/O will be a bottleneck for the vast majority of use cases.

I/O is extremely fast and will be negligible compared to other stuff going on to serve those requests, even running queries themselves.

The CPU work done by SQLite will vastly outshine the time it takes to read/write to disk.

It might be a bottleneck to reading if you have a very large database file, though.

Re: Show HN: SQLite Transaction Benchmarking Tool

#18

Post by pdimitar »

seddonm1 wrote: post #16
pdimitar wrote: post #8

Earlier quoted context omitted.

Thank you, good perspective. Weren't the compile-time connections to DB optional btw? They could be turned off I think (last I checked, which was last year admittedly). My question was more about the fact that sqlx is integrated with tokio out of the box and does not need an extra crate like rusqlite does. But I am guessing you don't mind that.

SQLX has an offline mode where it saves the metadata of the SQL database structure but then you run into risk of that being out of sync with the database? Yeah I just drop this one file [0] into my Tokio projects and I have a SQLite with single writer/multi reader pool done in a few seconds. [0]: https://github.com/seddonm1/s3ite/blob/0.5.0/src/database.rs

Thanks again!

I'll be resuming my effort to build an Elixir Rust SQLite bridge in the next several months. Hope you won't mind some questions.

Re: Show HN: SQLite Transaction Benchmarking Tool

#19

Post by jitl »

pdimitar wrote: post #15
matharmin wrote: post #12

Earlier quoted context omitted.

You get concurrency in SQLite by using multiple connections - and typically a dedicated thread per connection. When using async wrappers, a good solution is connection pooling like you mentioned - exactly the same concept as used by client->server database drivers. So you can have 5 or 10 read connections serving those 100 connections, with a statement/transaction queue to manage spikes in load. It's probably not wor…

> I feel connection pooling is much more rare in SQLite libraries than it should be. I'm maintaining one implementation (sqlite_async for Dart), but feel like this should be the standard for all languages with async/await support. I completely agree. But I simply have no reference / good-practice implementations to take inspiration from. I'd be more than willing to have an Elixir FFI bridge to a Rust library (and wri…

Libsql fork has good rust node async, you could look at them for inspiration. Maintained by Turso.

Re: Show HN: SQLite Transaction Benchmarking Tool

#20

Post by Moehassan »

leononame wrote: post #14
Moehassan wrote: post #11

Earlier quoted context omitted.

You can make SQLite scale way beyond the limitations of WAL mode or even Begin Concurrent mode, all while doing synchronous writes https://oldmoe.blog/2024/07/08/the-write-stuff-concurrent-wr...

If synchronous IO is blocking your CPU bound application code, this won't help you. My made up example was not about concurrent writes, and the concurrent reads I mentioned were not my main point. For all I care, you could have 100 different databases or even normal files in this scenario and you read them. I was wondering how the async wrappers around SQLite work when SQLite itself only has synchronous IO. At least…

How did you come to that conclusion? No, the synchronous IO is not blocking the application because the committer that actually does the writing to disk lives in an external process.

This implementayion turns synchronous IO to 100% async while still maintaining the chatty transaction api and the illusion of serail execution on the client side

Post reply on HN