7 ms·

It looks like this layers Raft on top of SQLite. I don't like when systems replicate high-level changes like "update users set salary = salary + 1000 where ...;

It looks like this layers Raft on top of SQLite. I don't like when systems replicate high-level changes like "update users set salary = salary + 1000 where ...;" Instead, I prefer they replicate low-level changes like "replace key/block X, which should have contents C_x, with C_x'". Why? Imagine you're doing a rolling update. Some of your replica are running the newer version of SQLite and some are running the older version. They may not execute the high-level query in exactly the same way. For example, in the absence of an "order by" clause, select results' order is unstated. So imagine someone makes a mutation that depends on this: "insert ... select ... limit". (Maybe a dumb example but it can happen anyway.) Now the databases start to diverge, not only in underlying bytes and implementation-defined ordering but in actual row data. I worked on a major distributed system that originally replicated high-level changes and switched to replicating low-level changes for this reason. We had a system for detecting when replicas didn't match, and replication of high-level changes was the biggest reason for diffs. (Hardware error was the second biggest reason; we added a lot of checksumming because of that.)

ttul 6y ago
Sounds like you need to submit a patch!
- scottlamb 6y ago
  lol, this is an unhelpful reflex answer to any criticism of open source software. What I'm describing is a redesign. There's no point in submitting a patch for that. It'd be much more effort than a few lines of code, and it wouldn't be accepted. Open source means that anyone can fork. It doesn't mean that maintainers will automatically merge patches replacing their software with completely different software. The only way that will happen is if the maintainers decide for themselves it needs to be redesigned, and that starts with discussion rather than a patch. It's also a long shot compared to just finding different software that already has the design I prefer. If I want replicated SQLite, I'll look at dqlite or litestream instead, which sound more compatible with my design sensibilities. (Thanks, tyingq and benbjohnson!)
  - monadic3 6y ago
    Frankly your bad-faith commentary isn't helping the conversation either. I sincerely appreciate your cleaning up the tone at the end.
benbjohnson 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. 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.
tyingq 6y ago
Dqlite replicates at the VFS layer of sqlite, which sounds like what you're looking for. https://github.com/canonical/dqlite https://github.com/canonical/dqlite
- hinkley 6y ago
  I haven’t gotten a straight answer out of the k3s people about why they dumped dqlite, just some comment about bugs. I could see myself using dqlite in the future so I’d like some more user reports from the trenches. Anyone shed some light on this?
  - tyingq 6y ago
    The initial issue seems to be saying that it's because they need to have etcd anyway, so consolidating on that removes a dependency. Which fits their simplicity goal. Though the issue appears to have been created by a user, not a maintainer. "Since the HA direction needs etcd anyway.. I'm proposing dropping support for sqlite as the default embedded non-HA option and switch to embedded etcd as the default. This will reduce overall effort of maintainability of two entirely different datastores." https://github.com/k3s-io/k3s/issues/845 https://github.com/k3s-io/k3s/issues/845
    - hinkley 6y ago
      I accepted that reason for them, but as I don’t benefit directly from switching to etcd, I’d rather know about what started the conversation than how it was concluded.
      - merb 6y ago
        dqlite support was flaky and did go trough their translation layer which probably added complexity.
lrossi 6y ago
If you replicate low level changes, you might not be able to do a live upgrade/downgrade if the version change affects the on disk format. Another downside is that you might also propagate data corruption in case of bugs in the DB software (e.g. memory corruption) or hardware defects.
- scottlamb 6y ago
  > If you replicate low level changes, you might not be able to do a live upgrade/downgrade if the version change affects the on disk format. It certainly requires care to ensure all the replicas have software capable of understanding the new format before it's actually written, but it can be done. Likewise after writing the new format, you want to have a roll-back plan. In SQLite's case, https://sqlite.org/formatchng.html https://sqlite.org/formatchng.html says: "Since 2004, there have been enhancements to SQLite such that newer database files are unreadable by older versions of the SQLite library. But the most recent versions of the SQLite library should be able to read and write any older SQLite database file without any problems." I don't believe upgrading SQLite automatically starts using any of those enhancements; you'd have to do a schema change like "PRAGMA journal_mode=WAL;" first. > Another downside is that you might also propagate data corruption in case of bugs in the DB software (e.g. memory corruption) or hardware defects. This happens regardless.
deleted 6y ago
[deleted]
xd 6y ago
In MySQL/MariaDB this is what's known as non-deterministic behaviour so row or mixed replication is used to mitigate. Statement based (high level) replication is very useful for i.e. "insert into tbl0 select col0 from tbl1 order by col1" as you would only need to send the query not the individual row data.
otoolep 6y ago
rqlite creator here. I understand what you're saying, but I don't think it's a compelling objection. Obviously, differences between versions -- even patched versions -- can results in subtle, unintended, differences in how the code works for a given program. But there is no reason to think a system that operates at a lower level ("replace key/block X, which should have contents C_x, with C_x'") is less vulnerable to this kind of issue, compared to one that operates at a higher level i.e. statement-based replication, which rqlite uses. In fact I would argue that the system that operates on higher-level of abstraction is less vulnerable i.e. to care about the subtle changes.

ttul 6y ago

Sounds like you need to submit a patch!

scottlamb 6y ago
lol, this is an unhelpful reflex answer to any criticism of open source software. What I'm describing is a redesign. There's no point in submitting a patch for that. It'd be much more effort than a few lines of code, and it wouldn't be accepted. Open source means that anyone can fork. It doesn't mean that maintainers will automatically merge patches replacing their software with completely different software. The only way that will happen is if the maintainers decide for themselves it needs to be redesigned, and that starts with discussion rather than a patch. It's also a long shot compared to just finding different software that already has the design I prefer. If I want replicated SQLite, I'll look at dqlite or litestream instead, which sound more compatible with my design sensibilities. (Thanks, tyingq and benbjohnson!)
- monadic3 6y ago
  Frankly your bad-faith commentary isn't helping the conversation either. I sincerely appreciate your cleaning up the tone at the end.

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

tyingq 6y ago

Dqlite replicates at the VFS layer of sqlite, which sounds like what you're looking for. https://github.com/canonical/dqlite https://github.com/canonical/dqlite

hinkley 6y ago
I haven’t gotten a straight answer out of the k3s people about why they dumped dqlite, just some comment about bugs. I could see myself using dqlite in the future so I’d like some more user reports from the trenches. Anyone shed some light on this?
- tyingq 6y ago
  The initial issue seems to be saying that it's because they need to have etcd anyway, so consolidating on that removes a dependency. Which fits their simplicity goal. Though the issue appears to have been created by a user, not a maintainer. "Since the HA direction needs etcd anyway.. I'm proposing dropping support for sqlite as the default embedded non-HA option and switch to embedded etcd as the default. This will reduce overall effort of maintainability of two entirely different datastores." https://github.com/k3s-io/k3s/issues/845 https://github.com/k3s-io/k3s/issues/845
  - hinkley 6y ago
    I accepted that reason for them, but as I don’t benefit directly from switching to etcd, I’d rather know about what started the conversation than how it was concluded.
    - merb 6y ago
      dqlite support was flaky and did go trough their translation layer which probably added complexity.

lrossi 6y ago

If you replicate low level changes, you might not be able to do a live upgrade/downgrade if the version change affects the on disk format. Another downside is that you might also propagate data corruption in case of bugs in the DB software (e.g. memory corruption) or hardware defects.

scottlamb 6y ago
> If you replicate low level changes, you might not be able to do a live upgrade/downgrade if the version change affects the on disk format. It certainly requires care to ensure all the replicas have software capable of understanding the new format before it's actually written, but it can be done. Likewise after writing the new format, you want to have a roll-back plan. In SQLite's case, https://sqlite.org/formatchng.html https://sqlite.org/formatchng.html says: "Since 2004, there have been enhancements to SQLite such that newer database files are unreadable by older versions of the SQLite library. But the most recent versions of the SQLite library should be able to read and write any older SQLite database file without any problems." I don't believe upgrading SQLite automatically starts using any of those enhancements; you'd have to do a schema change like "PRAGMA journal_mode=WAL;" first. > Another downside is that you might also propagate data corruption in case of bugs in the DB software (e.g. memory corruption) or hardware defects. This happens regardless.

deleted 6y ago

[deleted]

xd 6y ago

In MySQL/MariaDB this is what's known as non-deterministic behaviour so row or mixed replication is used to mitigate. Statement based (high level) replication is very useful for i.e. "insert into tbl0 select col0 from tbl1 order by col1" as you would only need to send the query not the individual row data.

otoolep 6y ago

rqlite creator here. I understand what you're saying, but I don't think it's a compelling objection. Obviously, differences between versions -- even patched versions -- can results in subtle, unintended, differences in how the code works for a given program. But there is no reason to think a system that operates at a lower level ("replace key/block X, which should have contents C_x, with C_x'") is less vulnerable to this kind of issue, compared to one that operates at a higher level i.e. statement-based replication, which rqlite uses. In fact I would argue that the system that operates on higher-level of abstraction is less vulnerable i.e. to care about the subtle changes.