Hacker News Reader

Hacker News

Comment by batmansmk | Hacker News Reader

Parent Full thread

batmansmk

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.

View on HN

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

I'm not very familiar with the details here. What are the problems with cp?

You can end up getting a corrupt copy of the database. See here:

https://www.sqlite.org/howtocorrupt.html#_backup_or_restore_...

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

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

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

> > > 0h to manage backups ("cp")

> > 0h to manage backups ("cp")

not if you are on btrfs and use a snapshot (same for xfs or even lvm)

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

> 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

But that's my point. People using sqlite as the store for a web application seems like the wrong tool for the job.

Why, if you just read from it? It's single writer multiple reader.

Because a single writer limits throughput significantly. For a small hobby website or a blog that may not be an issue.

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

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

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

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

Nice. Do you have any advice for true SQLite replication? Mobile scenarios for instance.

i'm not if is what you need, but the sqlite backup api is really useful, and i find it very simple to use

Reply on news.ycombinator.com