8 ms·

Around the World with SQLite3 and Rsync

112 points by Liriel 3y ago

jatins 3y ago
I am halfway through the article but had no idea what it was trying to accomplish. It's possible that it's because I am not deeply embedded in Rails ecosystem. Is there a tl;dr to what this does and why?
- deleted 3y ago
  [deleted]
- deleted 3y ago
  [deleted]
EGreg 3y ago
This can certainly work for files, if you don’t use a CDN. But also, you can just use a CDN! As for the database that handles your business logic … 3 minutes of googling would show that rsync is a poor (or at least a brittle) substitute for multi-master replication when it comes to database engines like InnoDB for MySQL (which is what most websites on the Internet use): https://dba.stackexchange.com/questions/91287/is-possible-to-backup-database-online-using-rsync https://dba.stackexchange.com/questions/91287/is-possible-to... However, you can consider setting up multi-master replication, or if you want your app to be portable, you can set up a replication layer in the app itself. The order of operations should only matter “per activity” (eg chatroom, document etc) and not globally. That’s what we set up at https://qbix.com/platform https://qbix.com/platform — except that the instances don’t all have to be under the control of one company. It’s basically a decentralized social networking platform. Federated, like Mastodon, but far more general-purpose, so you can build any applicatiom with it
- pipo234 3y ago
  We sell live http media streaming and use sqlite3 locally to solve most of the hairy race conditions you would expect when serving files while they are simultaneously being written to. Despite our advice and common wisdom, every now and then customers complain about corrupted databases and after talks we discover they are using rsync or NFS to replicate the sqlite DB file. So, I was very interested in this article which seemed to suggest that fly.io, somehow found a way to make this work anyhow. TL;DR — looks like the answer is: they did not.
  - nateabele 3y ago
    This is the blog post I want to read. What’s your setup for that?
    - pipo234 3y ago
      So the absolute killer features that I would say SQLite offers are: 1. Embedded (single, small library, very fast) 2. Portable concurrency using native OS/FS abstractions. Full SQL support is nice, but I'd also be happy if it only offered plain key values. I love that you don't need to manage DB processes, network connections, FS partitions, shard memory, mutexes — just point your process or thread to a file and it magically fixes atomicity, consistency, transactions, the whole enchilada. SQL databases are not great at storing huge chunks to data (petabytes of video) but those are easily handled by the native filesystem. Just make sure the write access is safely wrapped in a DB transaction, RAII style. This works and scales well enough except for the failure mode: over stressed egress (typically SELECT queries) will starve ingest (a more or less constant load of UPDATE & INSERT queries). At that point, you want to scale horizontally. As mentioned, replicating the SQL backend file over NFS or rsync is simply a horrible idea. Surprisingly we ultimately discovered that SQL-level replication did not solve the problem reliably either: scale improved, but the starvation failure mode (read locks blocking DB writes) persisted (be it less frequently). The replication layer we needed was at the application level where it's much easier to express that actually we did not need the real-time coupling between media ingest and egress. Eventual consistency is good enough: if the system is about to collapse, simply fail by adding latency so you can always keep ingest healthy. Worst case, people watching a popular sports event will see players buffering. Which is bad — don't get me wrong — but far better than a catastrophically blocked pipeline and congested video encoders.
  - pstuart 3y ago
    Use the backup api and expose that as a means of copying the database. https://sqlite.org/backup.html https://sqlite.org/backup.html
loloquwowndueo 3y ago
It’s not the most complicated part of the process but I wonder if they could use dqlite instead.
meghan_rain 3y ago
content marketing
sixhobbits 3y ago
This feels like a pretty significant step down in terms of quality for technical content output from Fly.io? To be fair, "explains a solution without explaining a problem" is one of my most common criticisms for technical content in general, but I am pretty sure this wouldn't have gotten to front page if it wasn't for fly's reputation. There's a lot of passive voice which makes it really hard to figure out who is doing what to what, and most importantly _why_?
- Mizoguchi 3y ago
  Never heard of them and that was exactly what I thought. "What problem are they trying to solve?".
- samruby 3y ago
  Author here, and fly.io employee. I have an app that allows dance studios to schedule "heats" for competitions. Multiple dance studios, in separate locations, use this app with separate databases. I merely blogged some techniques that I found useful for my app. Most apps won't need any of this, but in the future when somebody has a related problem, I wanted something I could point to to show some techniques that might be helpful.
  - barnabask 3y ago
    Why rsync and not Fly.io's own https://fly.io/docs/litefs/ https://fly.io/docs/litefs/?
    - samruby 3y ago
      The short answer is that litefs uses a single primary node (see: https://fly.io/docs/litefs/proxy/ https://fly.io/docs/litefs/proxy/). I work with Ben so I imagine that over time we'll find a configuration that works with multiple databases each with their own primary node, but until then, this works for now.
- tptacek 3y ago
  This feels weird to say, but, honestly, and I feel sheepish saying this, when it comes to technology stuff I'm an HN person first and everything else second, so I guess I get to say it: Can I ask you not to say things like this? Specifically: this idea that there's a bar for technical stunt driving that we need to clear to get things up on the blog. I know I can't demand that of anybody; I'm just asking. I can't tell you how many things we haven't written on our main blog because we're worried about whether it's as technically interesting as the "Gossip Glomers" thing we did with Aphyr. Sam's a good writer and this is a pragmatic post about an approach he took to building something. The world is incrementally better with this post in it than without it. Holding off until we could have integrated LiteFS and written a 4 paragraph coda about sqlite3 internals would not make the world a better place. I worry all the time about "Fly.io fatigue" on HN, for what it's worth! But the only sane response we can have to do that is: we just write what we want to write, and we let the community do what it's going to do. If 'dang gave us a header we could use to dampen some of our posts, we'd use it. :) If I'm crazy to ask this, say so. We've been grappling with this for years now; if we knew exactly how to handle it, I wouldn't have to write weird things like this.
jmholla 3y ago
Slightly off topic, I wish websites would provide an auto-detected dark mode or at least test with dark reader. On my phone, a lot of the article looked redacted.
0xbadcafebee 3y ago
Author, please rewrite this. My suggestions: 1) Delete the first two paragraphs. 2) Tell me what you're going to tell me, but without all the highlights and bullet points. 3) Sell me on why I want to read this article. Don't focus on the technical; focus on the human, what I as a person will get from it. 4) In later technical sections, don't give me instructions. Tell me what you are doing in general to solve some problem, then just show me the commands, and I will infer that I could also do X, do Y, do Z. 5) If you want to give a lot of technical detail and backstory, either put it in an expand, or in some kind of colored box so I have the visual context that I can probably skip this whole, continue the story, and come back to this later. 6) The recap shouldn't be larger than two paragraphs.

112 points by Liriel 3y ago

I am halfway through the article but had no idea what it was trying to accomplish. It's possible that it's because I am not deeply embedded in Rails ecosystem. Is there a tl;dr to what this does and why?

deleted 3y ago
[deleted]
deleted 3y ago
[deleted]

EGreg 3y ago

This can certainly work for files, if you don’t use a CDN. But also, you can just use a CDN! As for the database that handles your business logic … 3 minutes of googling would show that rsync is a poor (or at least a brittle) substitute for multi-master replication when it comes to database engines like InnoDB for MySQL (which is what most websites on the Internet use): https://dba.stackexchange.com/questions/91287/is-possible-to-backup-database-online-using-rsync https://dba.stackexchange.com/questions/91287/is-possible-to... However, you can consider setting up multi-master replication, or if you want your app to be portable, you can set up a replication layer in the app itself. The order of operations should only matter “per activity” (eg chatroom, document etc) and not globally. That’s what we set up at https://qbix.com/platform https://qbix.com/platform — except that the instances don’t all have to be under the control of one company. It’s basically a decentralized social networking platform. Federated, like Mastodon, but far more general-purpose, so you can build any applicatiom with it

pipo234 3y ago
We sell live http media streaming and use sqlite3 locally to solve most of the hairy race conditions you would expect when serving files while they are simultaneously being written to. Despite our advice and common wisdom, every now and then customers complain about corrupted databases and after talks we discover they are using rsync or NFS to replicate the sqlite DB file. So, I was very interested in this article which seemed to suggest that fly.io, somehow found a way to make this work anyhow. TL;DR — looks like the answer is: they did not.
- nateabele 3y ago
  This is the blog post I want to read. What’s your setup for that?
  - pipo234 3y ago
    So the absolute killer features that I would say SQLite offers are: 1. Embedded (single, small library, very fast) 2. Portable concurrency using native OS/FS abstractions. Full SQL support is nice, but I'd also be happy if it only offered plain key values. I love that you don't need to manage DB processes, network connections, FS partitions, shard memory, mutexes — just point your process or thread to a file and it magically fixes atomicity, consistency, transactions, the whole enchilada. SQL databases are not great at storing huge chunks to data (petabytes of video) but those are easily handled by the native filesystem. Just make sure the write access is safely wrapped in a DB transaction, RAII style. This works and scales well enough except for the failure mode: over stressed egress (typically SELECT queries) will starve ingest (a more or less constant load of UPDATE & INSERT queries). At that point, you want to scale horizontally. As mentioned, replicating the SQL backend file over NFS or rsync is simply a horrible idea. Surprisingly we ultimately discovered that SQL-level replication did not solve the problem reliably either: scale improved, but the starvation failure mode (read locks blocking DB writes) persisted (be it less frequently). The replication layer we needed was at the application level where it's much easier to express that actually we did not need the real-time coupling between media ingest and egress. Eventual consistency is good enough: if the system is about to collapse, simply fail by adding latency so you can always keep ingest healthy. Worst case, people watching a popular sports event will see players buffering. Which is bad — don't get me wrong — but far better than a catastrophically blocked pipeline and congested video encoders.
- pstuart 3y ago
  Use the backup api and expose that as a means of copying the database. https://sqlite.org/backup.html https://sqlite.org/backup.html

loloquwowndueo 3y ago

It’s not the most complicated part of the process but I wonder if they could use dqlite instead.

meghan_rain 3y ago

content marketing

sixhobbits 3y ago

This feels like a pretty significant step down in terms of quality for technical content output from Fly.io? To be fair, "explains a solution without explaining a problem" is one of my most common criticisms for technical content in general, but I am pretty sure this wouldn't have gotten to front page if it wasn't for fly's reputation. There's a lot of passive voice which makes it really hard to figure out who is doing what to what, and most importantly _why_?

Mizoguchi 3y ago
Never heard of them and that was exactly what I thought. "What problem are they trying to solve?".
samruby 3y ago
Author here, and fly.io employee. I have an app that allows dance studios to schedule "heats" for competitions. Multiple dance studios, in separate locations, use this app with separate databases. I merely blogged some techniques that I found useful for my app. Most apps won't need any of this, but in the future when somebody has a related problem, I wanted something I could point to to show some techniques that might be helpful.
- barnabask 3y ago
  Why rsync and not Fly.io's own https://fly.io/docs/litefs/ https://fly.io/docs/litefs/?
  - samruby 3y ago
    The short answer is that litefs uses a single primary node (see: https://fly.io/docs/litefs/proxy/ https://fly.io/docs/litefs/proxy/). I work with Ben so I imagine that over time we'll find a configuration that works with multiple databases each with their own primary node, but until then, this works for now.
tptacek 3y ago
This feels weird to say, but, honestly, and I feel sheepish saying this, when it comes to technology stuff I'm an HN person first and everything else second, so I guess I get to say it: Can I ask you not to say things like this? Specifically: this idea that there's a bar for technical stunt driving that we need to clear to get things up on the blog. I know I can't demand that of anybody; I'm just asking. I can't tell you how many things we haven't written on our main blog because we're worried about whether it's as technically interesting as the "Gossip Glomers" thing we did with Aphyr. Sam's a good writer and this is a pragmatic post about an approach he took to building something. The world is incrementally better with this post in it than without it. Holding off until we could have integrated LiteFS and written a 4 paragraph coda about sqlite3 internals would not make the world a better place. I worry all the time about "Fly.io fatigue" on HN, for what it's worth! But the only sane response we can have to do that is: we just write what we want to write, and we let the community do what it's going to do. If 'dang gave us a header we could use to dampen some of our posts, we'd use it. :) If I'm crazy to ask this, say so. We've been grappling with this for years now; if we knew exactly how to handle it, I wouldn't have to write weird things like this.

jmholla 3y ago

Slightly off topic, I wish websites would provide an auto-detected dark mode or at least test with dark reader. On my phone, a lot of the article looked redacted.

0xbadcafebee 3y ago

Author, please rewrite this. My suggestions: 1) Delete the first two paragraphs. 2) Tell me what you're going to tell me, but without all the highlights and bullet points. 3) Sell me on why I want to read this article. Don't focus on the technical; focus on the human, what I as a person will get from it. 4) In later technical sections, don't give me instructions. Tell me what you are doing in general to solve some problem, then just show me the commands, and I will infer that I could also do X, do Y, do Z. 5) If you want to give a lot of technical detail and backstory, either put it in an expand, or in some kind of colored box so I have the visual context that I can probably skip this whole, continue the story, and come back to this later. 6) The recap shouldn't be larger than two paragraphs.