3 ms·

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 simultan

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

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