Comment by EGreg | Hacker News Reader

Comment by EGreg | Hacker News Reader

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

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 — 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

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.

This is the blog post I want to read. What’s your setup for that?

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.

Use the backup api and expose that as a means of copying the database.

https://sqlite.org/backup.html

Reply on news.ycombinator.com