4 ms·
That's a fun one. A couple years ago someone posted a solution to that here. I'm not sure if it works for SQLite, but it worked for Postgres. The basics of it
by hinkley 4y ago
That's a fun one.
A couple years ago someone posted a solution to that here. I'm not sure if it works for SQLite, but it worked for Postgres. The basics of it were that each replica was aware of the latest transaction ID it had seen. On a normal read you'd deal with the usual set of eventually consistent issues. But on a read-after-write, you would select a replica that was ahead of the write transaction.
Ultimately that's a very odd flavor of sharding. What I don't recall is how they propagated that shard information efficiently, since with sharding and consistent hashing the whole idea is that the lookup algorithm is deterministic, and therefore can be run anywhere and get the same result. WAL lag information is dynamic, so... Raft?
- simonw 4y agoAnother solution that I've used successfully in the past for web apps is to set a 10s cookie every time a user performs a write, and then route their read requests to the lead server until that cookie expires. That way they're sure to see the impact of the write they just made.
- hinkley 4y agoIf write traffic is low enough I could see that working, but 10s is an awful long time. Anything less than a 1000:1 read:write ratio would start running into capacity problems wouldn't it?
- simonw 4y agoThat's never been a problem on any system I've worked on. If you assume a web application with logged in users who are occasionally updating their data, the percentage of users who will have performed a write within the last 10s is always going to be absolutely tiny.
- hinkley 4y agoThe absolute rate is the problem. You can't shed that load to other machines so the percentage of users or percentage of traffic doesn't matter. This is basically a variant of Little's Law, where server count has a ceiling of 1. 100 edits a minute from distinct sessions is 1000 sessions pinned at any moment. If they read anything in that interval it comes from the primary. The only question is what's the frequency and interval of reads after a write.
- infogulch 4y agoYeah a transaction ID solution would work pretty well. Every request would return the highest transaction ID seen by the replica; writes forwarded to the primary would also return the transaction ID after that write; any request with a transaction ID higher than the replica would be forwarded to the primary.
- hinkley 4y agoI think that may have been the case. Try the local replica, if it is too old, hit the primary, rather than shopping around for the next best replica.
- benbjohnson 4y agoLiteFS provides a transaction ID that applications can use to determine replication lag. If the replica is behind the TXID, it can either wait or it can forward to the primary to ensure consistency.
- simonw 4y agoIs that transaction ID made available to SQLite clients? As a custom SQL function or a value in a readable table somewhere?
- benbjohnson 4y agoIt’s available as a file handle. If you have a database file named “db” then you can read “db-pos” to read the replication position. The position is a tuple of the TXID and the database checksum.
- ngrilly 4y agoCan the app read it concurrently while it is updated by LiteFS?
- benbjohnson 4y agoYes, the app can read it whenever. There's a brief lock obtained on the current database position internally to read it out. The position only gets updated at the end of the transaction.
- yencabulator 4y agoLooks like LiteFS invalidates the cached file contents on every update of position. This means there is never stale data to be read. (The client needs to make sure to consume that in a single pread/read syscall, or it could observe a sheared state.) Disclaimer: I wrote the FUSE framework LiteFS uses, https://bazil.org/fuse https://bazil.org/fuse https://github.com/superfly/litefs/blob/52e269d4b04070690ce2757e568d320ae631d7fe/db.go#L121 https://github.com/superfly/litefs/blob/52e269d4b04070690ce2... https://github.com/superfly/litefs/blob/52e269d4b04070690ce2757e568d320ae631d7fe/fuse/file_system.go#L136 https://github.com/superfly/litefs/blob/52e269d4b04070690ce2... https://github.com/superfly/litefs/blob/a5cf33d1a3a91873d4ad291e6e63c94a8488084d/fuse/pos_node.go#L49 https://github.com/superfly/litefs/blob/a5cf33d1a3a91873d4ad...