8 ms·

I just open-sourced a streaming replication tool for SQLite called Litestream that does physical replication (raw pages) instead of logical replication (SQL com

I just open-sourced a streaming replication tool for SQLite called Litestream that does physical replication (raw pages) instead of logical replication (SQL commands). Each approach has its pros and cons. Physical replication logs tend to be larger than logical logs but I agree that you avoid a lot of issues if you do physical replication. https://github.com/benbjohnson/litestream https://github.com/benbjohnson/litestream

mrkurt 6y ago
I've been following this, and am anxious for the direct-to-sqlite replication. One of rqlite's big limitations is that it resyncs the entire DB at startup time. Being able to start with a "snapshot" and then incrementally replicate changes would be a big help.
- benbjohnson 6y ago
  That can be painful for sure. Litestream will do a snapshot on startup if it detects that it can't pick up from where it left off in the WAL. That can happen if Litestream is shut down and another process perform a checkpoint. But generally a restart will just use the existing snapshot & continue with the WAL replication.
- CuriouslyC 6y ago
  This is tangential, but depending on your sql needs, CouchDB's replication story is amazing, and you can replicate to the browser using PouchDB. There is an optional SQL layer, but obviously the good replication story comes with some trade-offs.
- jlongster 6y ago
  I built an app (https://actualbudget.com/ https://actualbudget.com/) that uses a local sqlite db and syncs changes, and that's exactly how it works. It takes quite a different approach though, using CRDTs to represent changes and those are synced around. When a fresh client comes into play, it downloads the latest sqlite snapshot from a server and then syncs up.
  - pdimitar 6y ago
    Consider adding family sharing or just general making / joining of a group with shared expenses and accounts. Me and my wife use such an app currently but it lacks features and ergonomy.
- otoolep 6y ago
  rqlite creator here. I'm not sure I follow why it's a "big limitation"? Is it causing you long start-up times? I'm definitely interested in improving this, if it's an issue. What are you actually seeing? Also, rqlite does do log truncation (as per Raft spec), so after a certain amount of log entries (8192 by default) node restarts work exactly like you suggested. The SQLite database is restored from a snapshot, and any remaining Raft Log entries are applied to the database.
  - mrkurt 6y ago
    Ah, ok that's some nuance I didn't know about! We're storing a few GB of data in the sqlite DB. Rebuilding those when rqlite restarts is slow and intensive process compared to just using the file on disk over again. Our particular use case means we'll end up restarting 100+ replica nodes all at once, so the way we're doing things makes it more painful than necessary.
    - otoolep 6y ago
      But how do you know it's intensive? Are you watching disk IO? Is there a noticeable delay when the node starts before it's ready to receive requests? Try setting "-raft-snap" to a lower number, maybe 1024, and see if it helps. You'll have much fewer log entries to apply on startup. However the node will perform a snapshot more often, and writes are blocked during the snapshotting. It's a trade-off. It might be possible to always restart using some sort of snapshot, independent of Raft, but that would add significant complexity to rqlite. The fact the SQLite database is built from scratch on startup, from the data in Raft log, means rqlite is much more robust.
      - mrkurt 6y ago
        Oh, we're reading the sqlite files directly. rqlite is really just a mechanism for us to propagate read only data to a bunch of clients. We need that sqlite file to never go away. Even a few seconds is bad. And since our replicas are spread all over the world, it's not feasible to move 1GB+ data from the "servers" fast enough. Is there a way for us to use that sqlite file without it ever going away? We've thought about hardlinking it elsewhere and replacing the hardlink when rqlite is up, but haven't built any tooling to do that.
webmaven 6y ago
> I just open-sourced a streaming replication tool for SQLite called Litestream that does physical replication (raw pages) instead of logical replication (SQL commands). Each approach has its pros and cons. Physical replication logs tend to be larger than logical logs but I agree that you avoid a lot of issues if you do physical replication. Hmm. Not having dug into your solution much, is it safe to say that the physical replication logs have something like logical checkpoints? If so, would it make sense to only keep physical logs on a relatively short rolling window, and logical logs (ie. only the interleaved logical checkpoints) longer?
- benbjohnson 6y ago
  I suppose you could save both the physical and logical logs if you really needed log term retention. SQLite databases (and b-trees in general) tend to compress well so the physical logging isn't as bad as it sounds. You could also store a binary diff of the physical page which would shrink it even smaller. One benefit to using physical logs is that you end up with a byte-for-byte copy of the original data so it makes it easy to validate that your recovery is correct. You'd need to iterate all the records in your database to validate a logical log. However, all that being said, Litestream runs as a separate daemon process so it actually doesn't have access to the SQL commands from the application.
  - webmaven 6y ago
    So, no logical checkpoints, logical logging would have to be a separate process. The main reason I would want both is to be certain that I was restoring the DB to a consistent state, even if the application is doing things stupidly (eg. not using transactions correctly. Or at all). I suppose timestamps would be enough to view the logs interleaved or side-by-side in some fashion.
hinkley 6y ago
Do you use compression? And if so, how that affects the relative amount of network traffic vs logical.
- benbjohnson 6y ago
  Yes, Litestream uses LZ4 compression. I originally used gzip but the compression speed was pretty slow. B-tree pages tend compress well because they tend to be 50-75% full because they need space to insert new records and because pages split when they get full. I'm seeing files shrink down to 14% of their size (1.7MB WAL compressed to 264KB). However, your exact compression will vary depending on your data.
  - hinkley 6y ago
    Ah, that makes sense. Most inserts don't split pages, so are around n worst case pages, but once in a while you get 2n updates where most of them are half full, and so compress better. So how does that compare to logical replication? (Also I imagine packet size plays a role, since you have to flush the stream quite frequently, right? 1000 bytes isn't much more expensive than 431)
    - benbjohnson 6y ago
      Litestream defaults to flushing out to S3 every 10 seconds but that's mainly because of PUT costs. Each request costs $0.00005 so it costs about $1.30 per month. If you flushed every second then it'd cost you $13/month. Logical replication would have significantly smaller sizes although the size cost isn't a huge deal on S3. Data transfer in to S3 is free and so are DELETE requests. The data only stays on S3 for as long as your Litestream retention specifies. So if you're retaining for a day then you're just keeping one day's worth of WAL changes on the S3 at any given time.
jgraettinger1 6y ago
Here's another approach to the problem [0]: This package is part of Gazette [1], and uses a gazette journal (known as a "recovery log") to power raw bytestream replication & persistence. On top of journals, there's a recovery log "hinting" mechanism [2] that is aware of file layouts on disk, and keeps metadata around the portions of the journal which must be read to recover a particular on-disk state (e.x. what are the current live files, and which segments of the log hold them?). You can read and even live-tail a recovery log to "play back" / maintain the on-disk file state of a database that's processing somewhere else. Then, there's a package providing RocksDB with an Rocks environment that's configured to transparently replicate all database file writes into a recovery log [3]. Because RocksDB is a a continuously compacted LSM-tree and we're tracking live files, it's regularly deleting files which allow for "dropping" chunks of the recovery log journal which must be read or stored in order to recover the full database. For the SQLite implementation, SQLite journals and WAL's are well-suited to recovery logs & their live file tracking, because they're short-lived ephemeral files. The SQLite page DB is another matter, however, because it's a super-long lived and randomly written file. Naively tracking the page DB means you must re-play the _entire history_ of page mutations which have occurred. This implementation solves this by using a SQLite VFS which actually uses RocksDB under the hood for the SQLite page DB, and regular files (recorded to the same recovery log) for SQLite journals / WALs. In effect, we're leveraging RocksDB's regular compaction mechanisms to remove old versions of SQLite pages which must be tracked / read & replayed. [0] https://godoc.org/go.gazette.dev/core/consumer/store-sqlite https://godoc.org/go.gazette.dev/core/consumer/store-sqlite [1] https://gazette.readthedocs.io/en/latest/ https://gazette.readthedocs.io/en/latest/ [2] https://gazette.readthedocs.io/en/latest/consumers-concepts.html#recovery-logs https://gazette.readthedocs.io/en/latest/consumers-concepts.... [3] https://godoc.org/go.gazette.dev/core/consumer/store-rocksdb https://godoc.org/go.gazette.dev/core/consumer/store-rocksdb
paulryanrogers 6y ago
FYI in Postgresql (Pgpool anyway) and MySQL that would be query replication. Logical replication often means the row data. Binary replication often uses block level data.

mrkurt 6y ago

I've been following this, and am anxious for the direct-to-sqlite replication. One of rqlite's big limitations is that it resyncs the entire DB at startup time. Being able to start with a "snapshot" and then incrementally replicate changes would be a big help.

benbjohnson 6y ago
That can be painful for sure. Litestream will do a snapshot on startup if it detects that it can't pick up from where it left off in the WAL. That can happen if Litestream is shut down and another process perform a checkpoint. But generally a restart will just use the existing snapshot & continue with the WAL replication.
CuriouslyC 6y ago
This is tangential, but depending on your sql needs, CouchDB's replication story is amazing, and you can replicate to the browser using PouchDB. There is an optional SQL layer, but obviously the good replication story comes with some trade-offs.
jlongster 6y ago
I built an app (https://actualbudget.com/ https://actualbudget.com/) that uses a local sqlite db and syncs changes, and that's exactly how it works. It takes quite a different approach though, using CRDTs to represent changes and those are synced around. When a fresh client comes into play, it downloads the latest sqlite snapshot from a server and then syncs up.
- pdimitar 6y ago
  Consider adding family sharing or just general making / joining of a group with shared expenses and accounts. Me and my wife use such an app currently but it lacks features and ergonomy.
otoolep 6y ago
rqlite creator here. I'm not sure I follow why it's a "big limitation"? Is it causing you long start-up times? I'm definitely interested in improving this, if it's an issue. What are you actually seeing? Also, rqlite does do log truncation (as per Raft spec), so after a certain amount of log entries (8192 by default) node restarts work exactly like you suggested. The SQLite database is restored from a snapshot, and any remaining Raft Log entries are applied to the database.
- mrkurt 6y ago
  Ah, ok that's some nuance I didn't know about! We're storing a few GB of data in the sqlite DB. Rebuilding those when rqlite restarts is slow and intensive process compared to just using the file on disk over again. Our particular use case means we'll end up restarting 100+ replica nodes all at once, so the way we're doing things makes it more painful than necessary.
  - otoolep 6y ago
    But how do you know it's intensive? Are you watching disk IO? Is there a noticeable delay when the node starts before it's ready to receive requests? Try setting "-raft-snap" to a lower number, maybe 1024, and see if it helps. You'll have much fewer log entries to apply on startup. However the node will perform a snapshot more often, and writes are blocked during the snapshotting. It's a trade-off. It might be possible to always restart using some sort of snapshot, independent of Raft, but that would add significant complexity to rqlite. The fact the SQLite database is built from scratch on startup, from the data in Raft log, means rqlite is much more robust.
    - mrkurt 6y ago
      Oh, we're reading the sqlite files directly. rqlite is really just a mechanism for us to propagate read only data to a bunch of clients. We need that sqlite file to never go away. Even a few seconds is bad. And since our replicas are spread all over the world, it's not feasible to move 1GB+ data from the "servers" fast enough. Is there a way for us to use that sqlite file without it ever going away? We've thought about hardlinking it elsewhere and replacing the hardlink when rqlite is up, but haven't built any tooling to do that.

webmaven 6y ago

> I just open-sourced a streaming replication tool for SQLite called Litestream that does physical replication (raw pages) instead of logical replication (SQL commands). Each approach has its pros and cons. Physical replication logs tend to be larger than logical logs but I agree that you avoid a lot of issues if you do physical replication. Hmm. Not having dug into your solution much, is it safe to say that the physical replication logs have something like logical checkpoints? If so, would it make sense to only keep physical logs on a relatively short rolling window, and logical logs (ie. only the interleaved logical checkpoints) longer?

benbjohnson 6y ago
I suppose you could save both the physical and logical logs if you really needed log term retention. SQLite databases (and b-trees in general) tend to compress well so the physical logging isn't as bad as it sounds. You could also store a binary diff of the physical page which would shrink it even smaller. One benefit to using physical logs is that you end up with a byte-for-byte copy of the original data so it makes it easy to validate that your recovery is correct. You'd need to iterate all the records in your database to validate a logical log. However, all that being said, Litestream runs as a separate daemon process so it actually doesn't have access to the SQL commands from the application.
- webmaven 6y ago
  So, no logical checkpoints, logical logging would have to be a separate process. The main reason I would want both is to be certain that I was restoring the DB to a consistent state, even if the application is doing things stupidly (eg. not using transactions correctly. Or at all). I suppose timestamps would be enough to view the logs interleaved or side-by-side in some fashion.

hinkley 6y ago

Do you use compression? And if so, how that affects the relative amount of network traffic vs logical.

benbjohnson 6y ago
Yes, Litestream uses LZ4 compression. I originally used gzip but the compression speed was pretty slow. B-tree pages tend compress well because they tend to be 50-75% full because they need space to insert new records and because pages split when they get full. I'm seeing files shrink down to 14% of their size (1.7MB WAL compressed to 264KB). However, your exact compression will vary depending on your data.
- hinkley 6y ago
  Ah, that makes sense. Most inserts don't split pages, so are around n worst case pages, but once in a while you get 2n updates where most of them are half full, and so compress better. So how does that compare to logical replication? (Also I imagine packet size plays a role, since you have to flush the stream quite frequently, right? 1000 bytes isn't much more expensive than 431)
  - benbjohnson 6y ago
    Litestream defaults to flushing out to S3 every 10 seconds but that's mainly because of PUT costs. Each request costs $0.00005 so it costs about $1.30 per month. If you flushed every second then it'd cost you $13/month. Logical replication would have significantly smaller sizes although the size cost isn't a huge deal on S3. Data transfer in to S3 is free and so are DELETE requests. The data only stays on S3 for as long as your Litestream retention specifies. So if you're retaining for a day then you're just keeping one day's worth of WAL changes on the S3 at any given time.

jgraettinger1 6y ago

Here's another approach to the problem [0]: This package is part of Gazette [1], and uses a gazette journal (known as a "recovery log") to power raw bytestream replication & persistence. On top of journals, there's a recovery log "hinting" mechanism [2] that is aware of file layouts on disk, and keeps metadata around the portions of the journal which must be read to recover a particular on-disk state (e.x. what are the current live files, and which segments of the log hold them?). You can read and even live-tail a recovery log to "play back" / maintain the on-disk file state of a database that's processing somewhere else. Then, there's a package providing RocksDB with an Rocks environment that's configured to transparently replicate all database file writes into a recovery log [3]. Because RocksDB is a a continuously compacted LSM-tree and we're tracking live files, it's regularly deleting files which allow for "dropping" chunks of the recovery log journal which must be read or stored in order to recover the full database. For the SQLite implementation, SQLite journals and WAL's are well-suited to recovery logs & their live file tracking, because they're short-lived ephemeral files. The SQLite page DB is another matter, however, because it's a super-long lived and randomly written file. Naively tracking the page DB means you must re-play the _entire history_ of page mutations which have occurred. This implementation solves this by using a SQLite VFS which actually uses RocksDB under the hood for the SQLite page DB, and regular files (recorded to the same recovery log) for SQLite journals / WALs. In effect, we're leveraging RocksDB's regular compaction mechanisms to remove old versions of SQLite pages which must be tracked / read & replayed. [0] https://godoc.org/go.gazette.dev/core/consumer/store-sqlite https://godoc.org/go.gazette.dev/core/consumer/store-sqlite [1] https://gazette.readthedocs.io/en/latest/ https://gazette.readthedocs.io/en/latest/ [2] https://gazette.readthedocs.io/en/latest/consumers-concepts.html#recovery-logs https://gazette.readthedocs.io/en/latest/consumers-concepts.... [3] https://godoc.org/go.gazette.dev/core/consumer/store-rocksdb https://godoc.org/go.gazette.dev/core/consumer/store-rocksdb

paulryanrogers 6y ago

FYI in Postgresql (Pgpool anyway) and MySQL that would be query replication. Logical replication often means the row data. Binary replication often uses block level data.