7 ms·

This is a good article that neatly covers a lot of the tips I've seen spread across various posts, talks, etc. Thanks! I'm curious what experience you or other

by LVB 4y ago

This is a good article that neatly covers a lot of the tips I've seen spread across various posts, talks, etc. Thanks! I'm curious what experience you or others have dealing specifically with the write concurrency elements of this setup, should you find it is actually an issue. I've occasionally seen mention of restructuring things to queue writes within the app (e.g. having a single writer goroutine fed with a channel), but I be interested in more details about when folks hit the point of needing to do that, and what they did (and did it help?)

simonw 4y ago
If you turn on WAL mode SQLite will queue the writes for you, and since most writes complete in under 1ms you'll likely find that this isn't really worth worrying about at all. I did some trivial benchmarking around this recently: https://simonwillison.net/2022/Oct/23/datasette-gunicorn/ https://simonwillison.net/2022/Oct/23/datasette-gunicorn/
- dinosaurdynasty 4y ago
  WAL mode does have some slight decrease in durability by default. If you pull the power immediately after a commit the commit may be reverted when you come back up. But yeah most of the time it isn't an issue.
  - markusw 4y ago
    This is news to me. What’s your source for that? AFAIK, SQLite is fully and completely ACID also in WAL mode.
    - dinosaurdynasty 4y ago
      https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_synchronous When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in journal_mode=DELETE on an older filesystem. WAL mode is safe from corruption with synchronous=NORMAL, and probably DELETE mode is safe too on modern filesystems. WAL mode is always consistent with synchronous=NORMAL, but WAL mode does lose durability. A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash. Transactions are durable across application crashes regardless of the synchronous setting or journal mode. The synchronous=NORMAL setting is a good choice for most applications running in WAL mode.
      - markusw 4y ago
        Interesting, thanks! I didn’t know this was a configurable tradeoff. Might update the article with a little bonus info.
    - deleted 4y ago
      [deleted]
mappu 4y ago
Go + SQLite user at $DAYJOB here. WAL is essential, and the next essential trick is to open a single db.Conn up-front and hide that behind a mutex for any transactions/queries that will write. All callers can pick this.db_rw or this.db_ro as required.
- markusw 4y ago
  What’s the advantage of the mutexed rw connection compared to the busy timeout and built-in locking?
  - mappu 4y ago
    The built-in one actually sleeps and polls to check if the write lock is free again. It's a disaster for high write throughput. https://sqlite.org/forum/info/00312b3d02bc0583 https://sqlite.org/forum/info/00312b3d02bc0583 Sqlite's internal one has to work this way to support their multi-process guarantees. In comparison, an in-process Go mutex will be readied immediately.
    - markusw 4y ago
      Ooooh, I didn’t know that detail. Thank you for sharing!
    - LVB 4y ago
      Very interesting. I'm now keen to write a very simple benchmark comparing the results of N goroutines leaning on the WAL lock vs in-app serialization.
      - philosopher1234 4y ago
        would love to see the results of that, if you decide to do this
        markusw 4y ago
        I did a small benchmark on some trivial code: https://gist.github.com/markuswustenberg/8be63c6e95dc5bb50fff6e14cc26f5eb https://gist.github.com/markuswustenberg/8be63c6e95dc5bb50ff... Results vary a bit on my machine, but it's pretty much the same (unless I'm doing something wrong, please point it out if so): $ make benchmark go test -bench=. goos: darwin goarch: arm64 pkg: sqlite BenchmarkWriteBlogPost/write_blog_post_without_WAL-8 6036 186719 ns/op BenchmarkWriteBlogPost/write_blog_post_with_WAL-8 102567 11169 ns/op BenchmarkWriteBlogPost/write_blog_post_with_WAL_and_Go_mutex-8 104228 11226 ns/op PASS ok sqlite 3.833s
markusw 4y ago
Thank you for the kind words! :) The built-in busy timeout basically takes care of write queueing, so a standard setup like this will take you very, very far.

by LVB 4y ago

simonw 4y ago

If you turn on WAL mode SQLite will queue the writes for you, and since most writes complete in under 1ms you'll likely find that this isn't really worth worrying about at all. I did some trivial benchmarking around this recently: https://simonwillison.net/2022/Oct/23/datasette-gunicorn/ https://simonwillison.net/2022/Oct/23/datasette-gunicorn/

dinosaurdynasty 4y ago
WAL mode does have some slight decrease in durability by default. If you pull the power immediately after a commit the commit may be reverted when you come back up. But yeah most of the time it isn't an issue.
- markusw 4y ago
  This is news to me. What’s your source for that? AFAIK, SQLite is fully and completely ACID also in WAL mode.
  - dinosaurdynasty 4y ago
    https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_synchronous When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in journal_mode=DELETE on an older filesystem. WAL mode is safe from corruption with synchronous=NORMAL, and probably DELETE mode is safe too on modern filesystems. WAL mode is always consistent with synchronous=NORMAL, but WAL mode does lose durability. A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash. Transactions are durable across application crashes regardless of the synchronous setting or journal mode. The synchronous=NORMAL setting is a good choice for most applications running in WAL mode.
    - markusw 4y ago
      Interesting, thanks! I didn’t know this was a configurable tradeoff. Might update the article with a little bonus info.
  - deleted 4y ago
    [deleted]

mappu 4y ago

Go + SQLite user at $DAYJOB here. WAL is essential, and the next essential trick is to open a single db.Conn up-front and hide that behind a mutex for any transactions/queries that will write. All callers can pick this.db_rw or this.db_ro as required.

markusw 4y ago
What’s the advantage of the mutexed rw connection compared to the busy timeout and built-in locking?
- mappu 4y ago
  The built-in one actually sleeps and polls to check if the write lock is free again. It's a disaster for high write throughput. https://sqlite.org/forum/info/00312b3d02bc0583 https://sqlite.org/forum/info/00312b3d02bc0583 Sqlite's internal one has to work this way to support their multi-process guarantees. In comparison, an in-process Go mutex will be readied immediately.
  - markusw 4y ago
    Ooooh, I didn’t know that detail. Thank you for sharing!
  - LVB 4y ago
    Very interesting. I'm now keen to write a very simple benchmark comparing the results of N goroutines leaning on the WAL lock vs in-app serialization.
    - philosopher1234 4y ago
      would love to see the results of that, if you decide to do this
      - markusw 4y ago
        I did a small benchmark on some trivial code: https://gist.github.com/markuswustenberg/8be63c6e95dc5bb50fff6e14cc26f5eb https://gist.github.com/markuswustenberg/8be63c6e95dc5bb50ff... Results vary a bit on my machine, but it's pretty much the same (unless I'm doing something wrong, please point it out if so): $ make benchmark go test -bench=. goos: darwin goarch: arm64 pkg: sqlite BenchmarkWriteBlogPost/write_blog_post_without_WAL-8 6036 186719 ns/op BenchmarkWriteBlogPost/write_blog_post_with_WAL-8 102567 11169 ns/op BenchmarkWriteBlogPost/write_blog_post_with_WAL_and_Go_mutex-8 104228 11226 ns/op PASS ok sqlite 3.833s

markusw 4y ago

Thank you for the kind words! :) The built-in busy timeout basically takes care of write queueing, so a standard setup like this will take you very, very far.