33 ms·
I'd be fascinated to hear more about this. Where do the SQLite files live? Are you using some kind of NFS or EFS or similar for them? Sharding them across many
by simonw 5y ago
I'd be fascinated to hear more about this.
Where do the SQLite files live? Are you using some kind of NFS or EFS or similar for them? Sharding them across many machines?
How are you handling backups and high availability? Are you using Litestream?
How many SQLite files do your application servers have open at any one time?
- mosselman 5y agoInterested in more info as well. Also, how do you handle migrations, both schema as well as data?
- deleted 5y ago[deleted]
- ngrilly 5y agoAlso super interested in hearing more.
- tyingq 5y agoSQLite mentions NFS as problematic because SQLite makes a lot of use of advisory locking. Which is supposed to work on NFS, but apparently is often buggy.
- mceachen 5y agoWriting to a SQLite file on a remote filesystem only works with a heap of caveats: don't use WAL, and disable SHM. SQLite is crazy fast in WAL mode, but it's because they use a memory-mapped file to coordinate consistency across multiple processes/threads. You can't mmap a remote file. (Source: hacking in a DB replica workaround to PhotoStructure so people can store their library on arbitrary filesystems... BTW, I'd love to be wrong here: if someone's figured out how to do this, please share!)
- exikyut 5y agoI'm idly curious why you need to use replicas in a workaround context, and why arbitrary filesystems are a problem.
- mceachen 5y agoThe local-replica approach is only applied when your library database is on a remote filesystem, which SQLite can't read/write to (due to the absence of shm and flock).
- exikyut 5y agoI think I get it now: you want people to be able to point PhotoStructure at a NAS and have it Just Work. That was the context I was missing. I was wondering if maybe the cute hack that was posted recentlyish (https://news.ycombinator.com/item?id=27016630 https://news.ycombinator.com/item?id=27016630) to enable SQLite3 to access large remote databases over HTTP might be relevant, but that approach requires a small server to be sitting at the remote, and only implements read-only access in any case. Now I'm wondering what an NFS/SMB translation of the above idea (which uses SQLite3's VFS hooks) might look like: ramp internal buffering to the max, use separate transient files (with eg date-based unique names) for each in-flight request, etc. Append-only journal files also come to mind... It's crazy how flaky network filesystems are at the lower levels. Like, I use NFS at home every day, without Kerberos (which lets me enable encryption, which IIUC provides AEAD), all while knowing my data integrity entirely depends on the IP checksum computed by my 15 year old ISP router. :D (I already know I need to upgrade. Shhh.)
- kurko 5y agoAlso super interested, specially handling migrations and structure/schema syncs.