9 ms·

Strong Consistency with Raft and SQLite

114 points by marcobambini 4y ago

AYBABTME 4y ago
Is this somehow related to rqlite? https://rqlite.io/ https://rqlite.io/ The architecture is very similar.
- otoolep 4y ago
  rqlite creator here. It's not related to my work, best of luck to them. [1] https://rqlite.io https://rqlite.io
lukev 4y ago
Seems similar to https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite or https://dqlite.io https://dqlite.io. Would be interesting to see a breakdown of the differences.
- otoolep 4y ago
  rqlite[1] creator here. This offering is completely new to me. Welcome to the party, that's what I say. I started doing what they are doing about 10 years ago. :-) https://www.philipotoole.com/9-years-of-open-source-database-development-the-design-docs/ https://www.philipotoole.com/9-years-of-open-source-database... https://www.philipotoole.com/replicating-sqlite-using-raft-consensus/ https://www.philipotoole.com/replicating-sqlite-using-raft-c... https://www.philipotoole.com/rqlite-v3-0-1-globally-replicating-sqlite/ https://www.philipotoole.com/rqlite-v3-0-1-globally-replicat... The post makes a fairly big point of the shortcomings of statement-based replication (like what rqlite does). What they state is correct -- non-deterministic SQL will be a problem, but it's not a difficult problem to solve. Just parse and rewrite the SQL statement before sending it to Raft. It's exactly what rqlite does[2] (though there is more to do). However they claim that there are two concerns with this approach. >This solution introduces a runtime delay because each SQL statement must be parsed and analyzed. The delay is insignificant compared with the delay introduced by Raft consensus. This is not a valid concern. I'm surprised they even mentioned it. >More importantly, it increases the difficulty of staying up-to-date with the new versions of the underline SQL engine (SQLite in this specific case) because the custom parser must be updated each time a new SQL function/feature is introduced. Theoretically somewhat true, but not true in practice. Any competent programmer can update any parsing code in a matter of hours. Also just because a new feature is introduced does not mean that the parser needs to be rewritten. That's because SQLite will not introduce breaking changes into their SQL specification. Any parser needs to be updated only if you want to support any new, non-deterministic feature. [1] https://rqlite.io https://rqlite.io [2] https://rqlite.io/docs/api/non-deterministic/ https://rqlite.io/docs/api/non-deterministic/
  - justinclift 4y ago
    With rqlite, the docs indicate that a single node setup should run ok. Is that supposed to still apply when a small cluster (eg three nodes) is reduced to a single node? eg during node failures, maintenance, node failures during maintenance ;), etc. When I was trying rqlite last week - eg prototyping a new SQLite "cloud" offering for DBHub.io - a remaining single node would consistently refuse to elect itself as the leader. Using default settings for everything. Tried it out several times, though I didn't give it more than a minute or two to get itself happy. Anyway, had to go with an initial non-RAFT approach for the prototype, but will likely investigate rqlite again in the future if I was just doing something dumb / fixable with tweaking. :)
    - otoolep 4y ago
      A single node should work perfectly fine when it's the only node in the cluster i.e. you're running a single-node cluster. If you couldn't bring up a single node from scratch, you're doing something wrong. :-) Show me your launch command, or join the rqlite Slack channel for help (https://www.philipotoole.com/join-rqlite-slack https://www.philipotoole.com/join-rqlite-slack) However if you create a 3-node cluster (or add 2 nodes to an existing single-node cluster) then at least a quorum of nodes (N/2 +1) must be up and running for the cluster to be available. That's the way Raft works -- Cockroach, etcd, Consul -- all of them will work the same way. So in your case a 3-node cluster with 2 nodes subsequently going down (or deliberately taken offline) is a cluster which is offline. There is extensive documentation available on clustering, which you should review: https://rqlite.io/docs/clustering/ https://rqlite.io/docs/clustering/
      - justinclift 4y ago
        Oh, the "getting a single node cluster" up and running was easy, as you said. It's just it wasn't clear from the initial docs I saw that a 3 -> 1 cluster would be considered any differently. Clearly, an error on my part. :) I'll circle back to rqlite in a while, after I get this existing prototype code rolled out and see what people make of it. Hopefully it turns out to be useful for people.
eatonphil 4y ago
I'm not an expert and every distributed database is different. My intuition about strapping a consensus library to a database is that you're going to have way higher latency because every operation, writes and reads, must go through consensus. (IIRC some databases like Cockroach mitigate this by having many mini Raft clusters rather than one single one [which IIRC is how rqlite works anyway].) The advantage you undeniably get is durability/high availability. > Scalability: Distributed databases can be scaled horizontally by adding more nodes to the network, making handling larger amounts of data and higher volumes of requests easier. This is only possible if you have a data partitioning scheme, right? But data partitioning is sort of a different problem from consensus/HA, right? If you do the simplest version of a consensus + database implementation, every disk on every instance of the consensus cluster is basically identical. So you don't benefit in that simple approach from adding more disks. It potentially just makes write latency worse unless you do flexible quorums so the quorum size is independent of the number of nodes in the cluster? > Localized access: With a distributed database, data can be stored closer to the users who need it, which can improve response times and reduce network latency. This would also only be true if you relax consistency requirements, right? If you need to be at the highest level of consistency you must send reads through consensus too otherwise if you read from the node nearest you it may be out of date. It would be cool to see some numbers about the difference between latency of reads/writes in sqlitecloud and sqlite on a single node! It would help my intuition anyway.
- adamgordonbell 4y ago
  So you don't benefit in that simple approach from adding more disks. Not sure about this DB, but that's my understanding of consensus as well. The more nodes, the less likely any one going down harms things but the harder it is to reach consensus. You have to write to more places. It becomes slower not faster.
  - eatonphil 4y ago
    > The more nodes, the less likely any one going down harms things but the harder it is to reach consensus Flexible quorums deals with this in the usual read/write case. But yeah leader election then becomes the bottleneck. So if the cluster is unstable overall performance gets even worse as you scale number of nodes. https://fpaxos.github.io/ https://fpaxos.github.io/
aeyes 4y ago
Where are the specs of the offering? I couldn't find information on the maximum DB size, maximum number of connections, network throughput, datacenter locations and other basic information. The screenshot on the homepage clearly shows that there is a disk and CPU limitation. It looks like this is a database with an SQLite API but internally it could be anything.
conqrr 4y ago
The most interesting thing for me in this is the article implies that they offer globally distributed consensus. Not sure if this is their actual offering too, but the example of NYC and Signapore implies this. While reads would be fine, the latency for writes could be atleast 2*250ms (roughly the speed between furthest AWS regions). I can still see this useful for applications that require strong consistency and global distribution, but likely a niche. Would be real interesting to see some actual numbers!
- pclmulqdq 4y ago
  It looks like you would still want something like CockroachDB instead of this for a global database layer.
  - conqrr 4y ago
    Agreed. Cockroach does a better job with multiple raft clusters, even Spanner which I believe does 3 Regional clusters and are battle tested.
jcutrell 4y ago
Totally unrelated to the content, but it's interesting to see these types of articles clearly using something like Midjourney to generate their header images. It almost feels like a dream-state when I first land on the page... my brain can't totally parse what I'm looking at on this one, for example. For me, that was actually pretty distracting.
robertlagrant 4y ago
I'm curious about sending changesets instead of commands. I get there are some things are nondeterministic, but I'm surprised that they couldn't be converted into deterministic commands before sending them out. E.g. send the result of `RANDOM()` instead of the call itself. I don't know how much that would buy you, but I'd imagine it might make the amount of information to send much smaller.
xrd 4y ago
I love that the rqlite author has chimed in here. Anyone know how this compares to the work done by litestream: https://litestream.io/how-it-works/ https://litestream.io/how-it-works/ Is this a fundamentally different idea: consistency vs. replication?
- otoolep 4y ago
  This is my take: https://rqlite.io/docs/faq/#how-is-it-different-than-litestream https://rqlite.io/docs/faq/#how-is-it-different-than-litestr... From the Litestream page: https://litestream.io/alternatives/#raft-based-consensus https://litestream.io/alternatives/#raft-based-consensus
VWWHFSfQ 4y ago
I see a lot of this kind of stuff but does anybody know what the actual DBA/SA story is for some big SQLite cluster? What if you have a non-trivial amount of data and you actually care about it? Do you just have a bunch of 800 gigabyte db.sqlite files sitting around?
- bastawhiz 4y ago
  I mean, nobody thinks of Postgres and MySQL as 800gb of files of tuples and btree data sitting around. Traditional DBMS software handles the replication and coordination of all those files. SQLite doesn't do that, and there's some bespoke external software that handles that. If you're a DBA/SA working with one of those systems, you're going to need to be an expert not just at SQLite, but at the software that does the work that SQLite isn't doing on its own. That'll be special skills.
- jasonwatkinspdx 4y ago
  I'm working on something in this area so I'd like to flip back the question to you. If you had a clustering system that managed an arbitrary number of sqlite databases for you across a pool of servers, what would be your desired features or priorities?
lmc 4y ago
Marco, you're probably going to want to rethink your branding. It sounds like you're associated SQLite officially, when AFAIK this isn't the case (?) The code is open source, but the name is a registered trademark. Similar issue here: https://github.com/vapor/sqlite-kit/issues/47 https://github.com/vapor/sqlite-kit/issues/47
- marcobambini 4y ago
  Dr. Richard Hipp (the SQLite author) is one of our shareholders, and we have the right to use the SQLite name.
  - lmc 4y ago
    Great, that is good to know! Perhaps a little about this on the site could be helpful for others.
    - marcobambini 4y ago
      You are right, thanks.
      - avinassh 4y ago
        +1 on the previous comment. This improves the credibility and I wouldn't think twice before adopting SQLite Cloud
deleted 4y ago
[deleted]
justinclift 4y ago
Looking at the SQLite Cloud repo on GitHub, it seems like the docs and SDK are open sourced. Is there a plan to Open Source the server side code as well? :)

114 points by marcobambini 4y ago

AYBABTME 4y ago

Is this somehow related to rqlite? https://rqlite.io/ https://rqlite.io/ The architecture is very similar.

otoolep 4y ago
rqlite creator here. It's not related to my work, best of luck to them. [1] https://rqlite.io https://rqlite.io

lukev 4y ago

Seems similar to https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite or https://dqlite.io https://dqlite.io. Would be interesting to see a breakdown of the differences.

otoolep 4y ago
rqlite[1] creator here. This offering is completely new to me. Welcome to the party, that's what I say. I started doing what they are doing about 10 years ago. :-) https://www.philipotoole.com/9-years-of-open-source-database-development-the-design-docs/ https://www.philipotoole.com/9-years-of-open-source-database... https://www.philipotoole.com/replicating-sqlite-using-raft-consensus/ https://www.philipotoole.com/replicating-sqlite-using-raft-c... https://www.philipotoole.com/rqlite-v3-0-1-globally-replicating-sqlite/ https://www.philipotoole.com/rqlite-v3-0-1-globally-replicat... The post makes a fairly big point of the shortcomings of statement-based replication (like what rqlite does). What they state is correct -- non-deterministic SQL will be a problem, but it's not a difficult problem to solve. Just parse and rewrite the SQL statement before sending it to Raft. It's exactly what rqlite does[2] (though there is more to do). However they claim that there are two concerns with this approach. >This solution introduces a runtime delay because each SQL statement must be parsed and analyzed. The delay is insignificant compared with the delay introduced by Raft consensus. This is not a valid concern. I'm surprised they even mentioned it. >More importantly, it increases the difficulty of staying up-to-date with the new versions of the underline SQL engine (SQLite in this specific case) because the custom parser must be updated each time a new SQL function/feature is introduced. Theoretically somewhat true, but not true in practice. Any competent programmer can update any parsing code in a matter of hours. Also just because a new feature is introduced does not mean that the parser needs to be rewritten. That's because SQLite will not introduce breaking changes into their SQL specification. Any parser needs to be updated only if you want to support any new, non-deterministic feature. [1] https://rqlite.io https://rqlite.io [2] https://rqlite.io/docs/api/non-deterministic/ https://rqlite.io/docs/api/non-deterministic/
- justinclift 4y ago
  With rqlite, the docs indicate that a single node setup should run ok. Is that supposed to still apply when a small cluster (eg three nodes) is reduced to a single node? eg during node failures, maintenance, node failures during maintenance ;), etc. When I was trying rqlite last week - eg prototyping a new SQLite "cloud" offering for DBHub.io - a remaining single node would consistently refuse to elect itself as the leader. Using default settings for everything. Tried it out several times, though I didn't give it more than a minute or two to get itself happy. Anyway, had to go with an initial non-RAFT approach for the prototype, but will likely investigate rqlite again in the future if I was just doing something dumb / fixable with tweaking. :)
  - otoolep 4y ago
    A single node should work perfectly fine when it's the only node in the cluster i.e. you're running a single-node cluster. If you couldn't bring up a single node from scratch, you're doing something wrong. :-) Show me your launch command, or join the rqlite Slack channel for help (https://www.philipotoole.com/join-rqlite-slack https://www.philipotoole.com/join-rqlite-slack) However if you create a 3-node cluster (or add 2 nodes to an existing single-node cluster) then at least a quorum of nodes (N/2 +1) must be up and running for the cluster to be available. That's the way Raft works -- Cockroach, etcd, Consul -- all of them will work the same way. So in your case a 3-node cluster with 2 nodes subsequently going down (or deliberately taken offline) is a cluster which is offline. There is extensive documentation available on clustering, which you should review: https://rqlite.io/docs/clustering/ https://rqlite.io/docs/clustering/
    - justinclift 4y ago
      Oh, the "getting a single node cluster" up and running was easy, as you said. It's just it wasn't clear from the initial docs I saw that a 3 -> 1 cluster would be considered any differently. Clearly, an error on my part. :) I'll circle back to rqlite in a while, after I get this existing prototype code rolled out and see what people make of it. Hopefully it turns out to be useful for people.

eatonphil 4y ago

I'm not an expert and every distributed database is different. My intuition about strapping a consensus library to a database is that you're going to have way higher latency because every operation, writes and reads, must go through consensus. (IIRC some databases like Cockroach mitigate this by having many mini Raft clusters rather than one single one [which IIRC is how rqlite works anyway].) The advantage you undeniably get is durability/high availability. > Scalability: Distributed databases can be scaled horizontally by adding more nodes to the network, making handling larger amounts of data and higher volumes of requests easier. This is only possible if you have a data partitioning scheme, right? But data partitioning is sort of a different problem from consensus/HA, right? If you do the simplest version of a consensus + database implementation, every disk on every instance of the consensus cluster is basically identical. So you don't benefit in that simple approach from adding more disks. It potentially just makes write latency worse unless you do flexible quorums so the quorum size is independent of the number of nodes in the cluster? > Localized access: With a distributed database, data can be stored closer to the users who need it, which can improve response times and reduce network latency. This would also only be true if you relax consistency requirements, right? If you need to be at the highest level of consistency you must send reads through consensus too otherwise if you read from the node nearest you it may be out of date. It would be cool to see some numbers about the difference between latency of reads/writes in sqlitecloud and sqlite on a single node! It would help my intuition anyway.

adamgordonbell 4y ago
So you don't benefit in that simple approach from adding more disks. Not sure about this DB, but that's my understanding of consensus as well. The more nodes, the less likely any one going down harms things but the harder it is to reach consensus. You have to write to more places. It becomes slower not faster.
- eatonphil 4y ago
  > The more nodes, the less likely any one going down harms things but the harder it is to reach consensus Flexible quorums deals with this in the usual read/write case. But yeah leader election then becomes the bottleneck. So if the cluster is unstable overall performance gets even worse as you scale number of nodes. https://fpaxos.github.io/ https://fpaxos.github.io/

aeyes 4y ago

Where are the specs of the offering? I couldn't find information on the maximum DB size, maximum number of connections, network throughput, datacenter locations and other basic information. The screenshot on the homepage clearly shows that there is a disk and CPU limitation. It looks like this is a database with an SQLite API but internally it could be anything.

conqrr 4y ago

The most interesting thing for me in this is the article implies that they offer globally distributed consensus. Not sure if this is their actual offering too, but the example of NYC and Signapore implies this. While reads would be fine, the latency for writes could be atleast 2*250ms (roughly the speed between furthest AWS regions). I can still see this useful for applications that require strong consistency and global distribution, but likely a niche. Would be real interesting to see some actual numbers!

pclmulqdq 4y ago
It looks like you would still want something like CockroachDB instead of this for a global database layer.
- conqrr 4y ago
  Agreed. Cockroach does a better job with multiple raft clusters, even Spanner which I believe does 3 Regional clusters and are battle tested.

jcutrell 4y ago

Totally unrelated to the content, but it's interesting to see these types of articles clearly using something like Midjourney to generate their header images. It almost feels like a dream-state when I first land on the page... my brain can't totally parse what I'm looking at on this one, for example. For me, that was actually pretty distracting.

robertlagrant 4y ago

I'm curious about sending changesets instead of commands. I get there are some things are nondeterministic, but I'm surprised that they couldn't be converted into deterministic commands before sending them out. E.g. send the result of `RANDOM()` instead of the call itself. I don't know how much that would buy you, but I'd imagine it might make the amount of information to send much smaller.

xrd 4y ago

I love that the rqlite author has chimed in here. Anyone know how this compares to the work done by litestream: https://litestream.io/how-it-works/ https://litestream.io/how-it-works/ Is this a fundamentally different idea: consistency vs. replication?

otoolep 4y ago
This is my take: https://rqlite.io/docs/faq/#how-is-it-different-than-litestream https://rqlite.io/docs/faq/#how-is-it-different-than-litestr... From the Litestream page: https://litestream.io/alternatives/#raft-based-consensus https://litestream.io/alternatives/#raft-based-consensus

VWWHFSfQ 4y ago

I see a lot of this kind of stuff but does anybody know what the actual DBA/SA story is for some big SQLite cluster? What if you have a non-trivial amount of data and you actually care about it? Do you just have a bunch of 800 gigabyte db.sqlite files sitting around?

bastawhiz 4y ago
I mean, nobody thinks of Postgres and MySQL as 800gb of files of tuples and btree data sitting around. Traditional DBMS software handles the replication and coordination of all those files. SQLite doesn't do that, and there's some bespoke external software that handles that. If you're a DBA/SA working with one of those systems, you're going to need to be an expert not just at SQLite, but at the software that does the work that SQLite isn't doing on its own. That'll be special skills.
jasonwatkinspdx 4y ago
I'm working on something in this area so I'd like to flip back the question to you. If you had a clustering system that managed an arbitrary number of sqlite databases for you across a pool of servers, what would be your desired features or priorities?

lmc 4y ago

Marco, you're probably going to want to rethink your branding. It sounds like you're associated SQLite officially, when AFAIK this isn't the case (?) The code is open source, but the name is a registered trademark. Similar issue here: https://github.com/vapor/sqlite-kit/issues/47 https://github.com/vapor/sqlite-kit/issues/47

marcobambini 4y ago
Dr. Richard Hipp (the SQLite author) is one of our shareholders, and we have the right to use the SQLite name.
- lmc 4y ago
  Great, that is good to know! Perhaps a little about this on the site could be helpful for others.
  - marcobambini 4y ago
    You are right, thanks.
    - avinassh 4y ago
      +1 on the previous comment. This improves the credibility and I wouldn't think twice before adopting SQLite Cloud

deleted 4y ago

[deleted]

justinclift 4y ago

Looking at the SQLite Cloud repo on GitHub, it seems like the docs and SDK are open sourced. Is there a plan to Open Source the server side code as well? :)