7 ms·
WAL Mode in LiteFS
- PreInternet01 4y agoEven if you're not using LiteFS yet, the following bit of SQLite advice makes this article absolutely worth the 11 minutes it allegedly takes to read: > Use WAL mode (For bonus points, also bump cache_size up to 20000 or so and set synchronous=NORMAL)
- bob1029 4y agoJust to give you an idea of how much difference this makes, I ran the following benchmark as of a few minutes ago: 3 new SQLite databases each with 1 text column, inserting 1000 rows of 1 char per. Testing using .NET6 and latest copy of System.Data.SQLite. Default / No Pragmas: 8806ms WAL Mode Only: 2819ms WAL Mode + Synchronous Normal: 44ms
- benbjohnson 4y agoThe SYNCHRONOUS pragma is great but I'll mention that there is a durability trade-off. In "NORMAL" mode, there is not an fsync() after every transaction so you could lose recent transactions if you unexpectedly shutdown. The WAL is append-only so you don't risk data corruption (which is great).
- bob1029 4y agoWe took the tradeoff in our product due to the strong performance upside. Our reasoning goes something like - whatever happened approximately at the edge of the power-loss abyss is considered part of the explosion. We have never held out hope that we'd be able to get information to disk up until the last microsecond. Letting WAL roll us back a few transactions is not a huge deal for our customers. Even if this breaks logical consistency with regard to external systems. We have many degrees of "redo" and the back office is always going to be a thing in our line of business. We file this under the "edge cases not economically worth targeting" bucket and take our 2 ~free orders of magnitude.
- vlovich123 4y agoAre you sure that those failures modes are the only ones guaranteed? I'd be worried about durability failures that happen due to non-obvious interactions (e.g. committing transaction A, losing transaction B, committing transaction C) or the database getting left in a state where it can't even start.
- bob1029 4y agoWAL enforces serialization semantics at crash recovery time. The only real variable is how many WAL frames made it to disk before the rug pull.
- TillE 4y ago> unexpectedly shutdown Right, a power cut or kernel panic. It's certainly something to be aware of, but there are probably much more likely ways to lose some data (programming errors, etc).
- tptacek 4y agoOr a point-in-time snapshot of the block device being made.
- bob1029 4y agoThis is actually how we recommend our customers perform backups of our product. Crash-consistent snapshots of the entire VM's disk every so often. 100% of our installs are in virtualized/cloud environments, so this is a very convenient and natural way to go about things. Some loss around the edges (and between snapshots) is understood and accepted by all. We've made it clear to our customers that by making this trade-off, we can vastly simplify operational costs around the system (i.e. we only need 1 self-contained VM per environment).
- tptacek 4y agoOh, for sure. I'm just saying that in cloud environments the "power cut off" scenario is more common than it looks. :)
- aidenn0 4y agoThe WAL is append-only so you don't risk data corruption (which is great). I'm not sure this is true with all filesystems. I think there are some filesystems in which a crash during append can end up with the file enlarged, but the data not written (IIRC I saw something like this with XFS when I was working on a kernel module that kept crashing the kernel).
- benbjohnson 4y ago> I think there are some filesystems in which a crash during append can end up with the file enlarged The SQLite WAL file itself has a running checksum that starts from the beginning so if you had an enlarged file or even a block of zero'd out bytes in the middle, SQLite would still recover gracefully. It recovers up to the last valid WAL frame that contains a set "commit" flag.
- aidenn0 4y agoI'm honestly not surprised that SQLite handles this situation (I seriously doubt there's any file-system oddity I've run into that Hipp hasn't). But just being "append only" is insufficient.
- cldellow 4y agoWAL and synchronous=NORMAL is huge. I love databases that choose safe defaults. That said, many real-life applications of databases don't need to block for confirmation that the transaction has been durably committed -- ETL jobs, integration tests, most logging, etc. When you discover that you can get a 20-50x speedup for basically free, it's like Christmas.
- deleted 4y ago[deleted]
- deleted 4y ago[deleted]
- zamalek 4y agoWhat are the practical limits (not theoretical limits) for LiteFS? Are we talking 100's of GB, or something smaller?
- benbjohnson 4y agoWe're targeting databases of 1 to 10 GB right now and those seem to work pretty well overall. I'm sure we'll expand that target over time as LiteFS matures though. Most of the requests I've seen have been to support a lot of smaller databases (e.g. hundreds or thousands of 1GB databases) rather than one huge 100GB database. You can do interesting things like sharding. Or if you're a SaaS companies, you could do one SQLite database per customer. That has some nice isolation properties and it improves SQLite's single writer restriction as your writes are spread across multiple databases.
- zamalek 4y agoAwesome, thanks! These ballpark figures would be a great improvement to your documentation.
- pgm8705 4y agoI'm curious how one goes about handling the need to query data across customers with the one-database-per-customer pattern.
- ithrow 4y agowith a script
- benbjohnson 4y agoIt depends on what you're trying to do. If you're trying to query revenue then that's usually stored in a third-party system like Stripe. If you're tracking metrics then those can be aggregated into a time series database like Prometheus and viewed through Grafana. It's definitely a trade-off. Isolation is pretty compelling for a lot of mid-market and enterprise customers but it does come at a cost of making it a pain to query across customers. But then again, that's kinda the point. :)
- mbStavola 4y agoHaving an interest in Sqlite and LiteFS, but not having explored either very much, I wonder what the DB administration aspect looks like? Say I want to connect a SQL client to my production DB do I expose a port on my production app that speaks the protocol? Or maybe I have a separate container that is basically just the Sqlite replica that I can connect to? Or maybe there is some other approach that I'm just not seeing? Fly, Sqlite, and LiteFS is definitely an interesting combination but I'm still sort of mentally stuck in the managed (and standalone) DB model.
- benbjohnson 4y agoCurrently, you need to SSH in and use the sqlite3 CLI on the server. There has been some work in this area but it's all still rough around the edges. I wrote a server called Postlite[1] that exposes remote SQLite databases over the Postgres wire protocol but it's very alpha. :) I'd love to see more work in this area. Ricardo Ander-Egg wrote a remote management tool called litexplore[2] that connects over SSH to the SQLite CLI behind the scenes. I haven't used it but I think there's a lot of potential with that approach. [1]: https://github.com/benbjohnson/postlite https://github.com/benbjohnson/postlite [2]: https://github.com/litements/litexplore https://github.com/litements/litexplore
- polyrand 4y agoHi, author of litexplore here! I agree remote management is the "least convenient" aspect of SQLite. If you have control over the remote VM where the DB is hosted, you can also run something like ClouDBeaver[0], Datasette[1] or sqlite-web[2] as a Docker container, then connect to it from your laptop. The reason I wrote litexplore was that those approaches require exposing extra ports, managing authentication, etc. With litexplore, you just need the sqlite3 CLI (compiled with the JSON1 extension) installed in the VM and a way to access it over SSH. litexplore is still in early stages and I have some updates planned in the following 2 months, but the basics are working. [0]: https://github.com/dbeaver/cloudbeaver https://github.com/dbeaver/cloudbeaver [1]: https://datasette.io/ https://datasette.io/ [2]: https://github.com/coleifer/sqlite-web https://github.com/coleifer/sqlite-web
- 4y ago
- spiffytech 4y agoIf a LiteFS node is way behind on replication, can it let the app know when it's caught up? What I'd like to do: have one Fly instance running and another in cold standby. On deploy, the cold instance boots, fails the health check until replication catches up, then takes over while the first instance goes cold.
- benbjohnson 4y ago> If a LiteFS node is way behind on replication, can it let the app know when it's caught up? LiteFS exposes the replication position via a file[1] suffixed with "-pos" for each database. That has a monotonically increasing TXID that you could compare to the primary. We track latency via a Prometheus metric but we don't currently expose that easily to the application. I added an issue for that[2]. > have one Fly instance running and another in cold standby. On deploy, the cold instance boots, fails the health check until replication catches up, then takes over while the first instance goes cold. When LiteFS starts up, it waits for the node to either connect to the primary and catch up or become the primary itself before it starts the user's application (if running in a supervisor mode). That would probably get you most of the way there to what you're trying to do. [1]: https://fly.io/docs/litefs/position/ https://fly.io/docs/litefs/position/ [2]: https://github.com/superfly/litefs/issues/253 https://github.com/superfly/litefs/issues/253
- simonw 4y agoDoes this mean I can run both LiteFS and Litestream at the same time, for a streamed backup to S3 in addition to replication for read-only replicas?
- benbjohnson 4y agoNot yet. Litestream doesn't support moving the primary node around. However, we'll be adding support for writing to a streaming back up directly in LiteFS soon. You might be able to get away with using a static primary node and running Litestream on there but I haven't tried that yet.
- BFLpL0QNek 4y agoI was looking at LiteFS docs yesterday as it happens. Does it support snapshots? From what I could work out, when you start a non primary instance it catches up from the primary? So if your replica is a fresh instance it replicates the entire db from the primary? I was wondering if it’s possible to snapshot somehow, so if I have a fresh server that comes online it grabs the last known snapshot from somewhere like s3 then the delta changeset from the primary is much smaller (less load?)
- benbjohnson 4y ago> Does it support snapshots? We'll be adding an "export" feature soon to do snapshots. We'll also be adding support for streaming backups similar to what Litestream does (although it'll be cluster aware). > From what I could work out, when you start a non primary instance it catches up from the primary? So if your replica is a fresh instance it replicates the entire db from the primary? Yes, it'll grab a snapshot from the primary if it's a fresh instance or if it's been too long since it was connected to the primary (and the next transaction files are not available). > I was wondering if it’s possible to snapshot somehow, so if I have a fresh server that comes online it grabs the last known snapshot from somewhere like s3 then the delta changeset from the primary is much smaller (less load?) You could but I'm not sure there's a large benefit over simply copying from the primary. I suppose if you have a very large database and there's only a small number of changes then pulling from S3 first could be beneficial. The transaction files are also compactable so we'll be able to reduce the total amount transferred once we integrate that into LiteFS.
- xrd 4y agoIs anyone using something like pocketbase with LiteFS or Litestream? I love pocketbase as a management tool (and the go interface is really nice when you need a bit of extra customization).
- xrd 4y agoFor posterity: https://github.com/TylerSustare/pocketbase-framework-litestream https://github.com/TylerSustare/pocketbase-framework-litestr...