4 ms·
We use SQLite in an app with about 1,000 active users. It took us: - 0h to manage backups ("cp"), - 0h to manage seeds and tests fixtures ("cp"), - 0h to con
by batmansmk 8y ago
We use SQLite in an app with about 1,000 active users. It took us:
- 0h to manage backups ("cp"),
- 0h to manage seeds and tests fixtures ("cp"),
- 0h to configure and secure (void),
- 0h to write the deployment scripts (void),
- 0h monitoring/watchdog jobs (void),
- 1h to rsync for failover ("rsync")
My last projects always spent at least a good 100h to do all of this the right way. Then, if it is not good enough, we'll move to RDS or equivalent.
- jimktrains2 8y agoDo you have a write-heavy workload? How do you handle contention? Do you have multiple servers? How do you handle real-time filesystem sync? If you only have one server, how do you handle its inevitable failure? Do you loose any data collected between last backup and failure? (I'm not saying SQLite isn't good, because it's f-ing amazing. I'm just not sold on it as a multi-process, multi-user database.) (If you have a filesystem capable of atomic snapshots, then you can simply snapshot any database and treat the backup as a single file.)
- Boulth 8y ago> Do you have a write-heavy workload? How do you handle contention? I'd also be interested in that. Last time I wanted to use Sqlite opening twice the same file for writing either would not succeed or could time out.
- jacquesm 8y agoThat's because SQLite is meant for single user applications. If you try to open the file the second time it will wait to acquire the lock.
- WA 8y agoSingle process applications if I’m not mistaken. And somewhere in the docs it says: most writes take a few milliseconds at most, so even in a multi-process environment (say 5-10 PHP processes), it should be fine. Haven’t tried this though.
- deleted 8y ago[deleted]
- deleted 8y ago[deleted]
- Sohcahtoa82 8y ago> I'm just not sold on it as a multi-process, multi-user database. That's fine. It was never designed to be a multi-process, multi-user database. Even the authors don't pretend it works in that use case: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- jimktrains2 8y agoBut that's my point. People using sqlite as the store for a web application seems like the wrong tool for the job.
- petre 8y agoWhy, if you just read from it? It's single writer multiple reader.
- jimktrains2 8y agoBecause a single writer limits throughput significantly. For a small hobby website or a blog that may not be an issue.
- jstimpfle 8y ago> 0h to manage backups ("cp") Are you aware that's unsafe? To make a safe backup use sqlite3's ".dump" command (or filesystem snapshotting, but I've had bad experiences with that, at least on btrfs).
- ufo 8y agoI'm not very familiar with the details here. What are the problems with cp?
- jstimpfle 8y agocp does not make atomic snapshots. It copies by reading (usually sequentially) chunk by chunk from the source file, and writing these chunks to the destination file. This takes time. If the database has writes at the time of backup, the backup might be invalid (it contains some old parts and some new parts). (Unless you use e.g. the --reflink option of GNU cp, in which case it makes atomic snapshots on filesystems that support it).
- candiodari 8y agoIsn’t one of the more important points of POSIX that reading sequentially results in an atomic copy ? As long as you keep the file handle open and the fs supports it.
- jstimpfle 8y agoI don't know which point you mean, but that would mean that any writer would be blocked indefinitely by any other reader. That amounts to a read lock. You don't get a read lock just by opening a file for reading. You can test that with a simple shell script { echo line1 echo line2 } > test.txt { read line echo got "$line" sleep 2 read line echo got "$line" } < test.txt & # concurrent write sleep 1; { echo line2 echo line1 } > test.txt wait This script does a concurrent write while the reader is in the "sleep 2" phase. The output should be got line1 got line1 (given that the sleeps do the expected thing). POSIX might contain something that requires aligned blocks of 512 bytes or so to be read or written atomically. But only if you do that in a single system call, of course.
- tabulatouch 8y agoNice. Do you have any advice for true SQLite replication? Mobile scenarios for instance.
- hexmiles 8y agoi'm not if is what you need, but the sqlite backup api is really useful, and i find it very simple to use
- trumped 8y agoI've had locking issues with an sqlite db while being the only user (only 2 processes were accessing it), I guess I'm doing something majorly wrong...