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

LiteFS

fly.io

Post reply on HN
Print view View original

111–120 of 158 posts

Re: LiteFS

#111

Post by simonw »

benbjohnson wrote: post #102
hinkley wrote: post #76

Earlier quoted context omitted.

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…

LiteFS 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.

Is that transaction ID made available to SQLite clients? As a custom SQL function or a value in a readable table somewhere?

Re: LiteFS

#112

Post by simonw »

hinkley wrote: post #88
simonw wrote: post #82

Earlier quoted context omitted.

Another 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.

If 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?

That'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.

Re: LiteFS

#113

Post by benbjohnson »

simonw wrote: post #111
benbjohnson wrote: post #102

Earlier quoted context omitted.

LiteFS 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.

Is that transaction ID made available to SQLite clients? As a custom SQL function or a value in a readable table somewhere?

It’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.

Re: LiteFS

#114

Post by hinkley »

simonw wrote: post #112
hinkley wrote: post #88

Earlier quoted context omitted.

If 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?

That'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.

The 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.

Re: LiteFS

#115

Post by fcoury »

cbsmith wrote: post #105
simonw wrote: post #93

Earlier quoted context omitted.

The performance boost that matters most here is when your application reads from the database. Your application code is reading directly from disk there, through a very thin FUSE layer that does nothing at all with reads (it only monitors writes). So your read queries should mostly be measured in microseconds.

> So your read queries should mostly be measured in microseconds. You should check out the read latency for read-only requests over unix domain sockets with PostgreSQL. You tend to measure it in microseconds, and depending on circumstances it can be single-digit microseconds. Regardless of whether your FUSE logic does nothing at all, It sure seems like there's intrinsic overhead to the FUSE model that is very similar…

Wow, thank you so much for explaining the overhead around FUSE usage, that’s really eye opening and makes having some performance benchmarks down the line even more interesting.

Re: LiteFS

#116

Post by benbjohnson »

rsync wrote: post #106
benbjohnson wrote: post #104

Earlier quoted context omitted.

Yes, it should fit with WunderBase to provide HA. I'm adding S3 support which will work the same (in principle) as Litestream. I'm hoping to have that in about a month or so.

Can you comment on how you added SFTP support to litestream so that it would work with rsync.net[1][2] ? How does that compare/contrast with what my grandparent is alluding to ? [1] https://github.com/benbjohnson/litestream/issues/140 [2] https://www.rsync.net/resources/notes/2021-q3-rsync.net_tech...

Litestream does log shipping so it just takes a chunk of the write-ahead log (WAL) and copies it out a destination. That can be S3, GCP, SFTP, etc.

LiteFS will do the same although I'm only targeting S3 initially. That seemed to be the bulk of what people used.

Re: LiteFS

#117

Post by metafex »

tptacek wrote: post #18
pphysch wrote: post #16

> Developing against a relational database requires devs to watch out for "N+1" query patterns, where a query leads to a loop that leads to more queries. N+1 queries against Postgres and MySQL can be lethal to performance. Not so much for SQLite. This is misleading AFAICT. The article(s) is actually comparing remote RDBMS to local RDBMS, not Postgres to SQLite. Postgres can also be served over a UNIX socket, removing…

Anyone want to chime in with the largest app they've deployed where prod Postgres was reachable over a Unix domain socket?

Had an instance with almost 2TB on disk in 2015, it's probably a lot more by now, if it's still running as-is. Was for analytics/data-warehousing. Though the big table was partitioned so queries were always nice and snappy. The machine had 128gb ram back then, but probably never needed that much. The applications working with the data ran on that same server, so processing latency was usually <10ms even for larger queries.

Re: LiteFS

#118

Post by zie »

nicoburns wrote: post #94
zie wrote: post #84

Earlier quoted context omitted.

No, the big difference is write speed. SQLite is limited in the amount of writes it can do at the same time(typically 1). This is generally called "concurrent writes". Last I checked SQLite can't really get past 1 write at the same time, but it can emulate concurrent writes in WAL mode such that it isn't normally a problem, for most applications. Postgres has no such limit(though there can be limits to updating a par…

> it can emulate concurrent writes in WAL mode Seems a bit unfair to call WAL mode emulation of "true" concurrent writes as I'm pretty sure a write-ahead-log (WAL) is exactly how other databases implement multiple concurrent writes. It's just always-on rather than being opt-in.

Well, but that's not really what it's doing. WAL1 mode in SQLite is different than in your typical RDBMS. WAL mode in SQLite is just getting the write path outside of the read path, but there is still only 1 write allowed at any given time. The "fake" concurrency is done by letting write transactions queue up and wait for the lock.

See [here](https://sqlite.org/wal.html) under "2.2. Concurrency" where it says:

> "However, since there is only one WAL file, there can only be one writer at a time."

SQLite is awesome, I'm a huge fan, but if you need to do lots and lots of writes, then SQLite is not your friend. Luckily that's a very rare application. There is a reason you never see SQLite being the end point for logs and other write-heavy applications.

Re: LiteFS

#119

Post by fny »

This reads like a professor who's so steeped in research that he's forgotten how to communicate to his students!

What exactly are we talking about here? A WebSQL thats actually synced to a proper RDBMS? Synced across devices? I'm not clear about an end to end use case.

Edit: Honestly, this line from the LiteFS docs[0] needs to be added to the top of the article:

> LiteFS is a distributed file system that transparently replicates SQLite databases. This lets you run your application like it's running against a local on-disk SQLite database but behind the scenes the database is replicated to all the nodes in your cluster. This lets you run your database right next to your application on the edge.

I had no idea what was being talked about otherwise.

[0]: https://fly.io/docs/litefs/

Re: LiteFS

#120

Post by Spivak »

benbjohnson wrote: post #99
ignoramous wrote: post #63

Earlier quoted context omitted.

Thanks. > LiteFS still maintains serializable isolation within a transaction, although, it has looser guarantees across nodes than something like rqlite. Picking up a term from the consistency map here [0], what guarantees LiteFS makes across nodes? [0] https://jepsen.io/consistency

Good question. Right now, LiteFS operates with async replication so its possible to have a transaction written to the primary get lost if the primary fails before it's replicated out. For that short window, you could read a transaction that no longer exists. From that standpoint, I believe it would technically be Read Uncommitted. However, during normal operation it'll function more like Snapshot Isolation. LiteFS do…

I think you’re being too harsh on yourself, isolation levels don’t typically account for replication lag and network partitioning into account. For example on MySQL you can have async replication and serializable isolation. Replicas in this mode might never receive updates.
Post reply on HN