5 ms·

I'm not much into the details but I'm wondering why is it not always writing to "<database>-wal" once it is full, it is renamed to "<database>-wal-0" and it sta

I'm not much into the details but I'm wondering why is it not always writing to "<database>-wal" once it is full, it is renamed to "<database>-wal-0" and it starts writing again into "<database>-wal", once it is full again it is renamed to "<database>-wal-1" ?

slau 3y ago
Presumably because you don't want to keep a full directory of WALs up to infinity. This uses WAL_size*2 on the disk and keeps it from growing beyond that.
ajnin 3y ago
This is to handle the case where transactions keep getting written to the wal file while it is being "checkpointed", preventing it from ever being deleted. You only need to alternate between two files, one checkpointed, one written to, for this.
wongarsu 3y ago
when doing backups, managing file permissions, etc, it's really convenient to only have a fixed number of known files to worry about. Switching to WAL already makes handling Sqlite databases much less convenient, since you now have three files instead of one, and need a filesystem snapshotting mechanism to reliably back them up (so you don't have one state in the database and another in the wal). Making the filenames and number of files less predictable would make that mode not worth it for many use cases
- dezgeg 3y ago
  Even the mechanism predating WALv1 (rollback journal) uses two files. I don't think there is any way of using SQLite crash-proof with just a single file. Besides, even if the database is single-file it's still necessary to use filesystem snapshotting for live backup, or it's likely to get an inconsistent copy.
  - kijin 3y ago
    With smallish databases, just pipe the .dump command to gzip. No need to snapshot an entire filesystem just to back up a few hundred megabytes (what I would consider "smallish"). Backup and restore speeds aren't a significant concern at those sizes, anyway, and you get human-readable backups that can be inspected with standard Unix tools.
    - deberon 3y ago
      We would back up double-digit GB MySQL databases by piping mysqldump into gzip as well. Like you I’m sure there’s a size limit where that doesn’t work. I never found out if it was CPU or memory constrained though.
      - sgarland 3y ago
        Where I’m at, the DBAs shifted from pipes to temporary files. They said at the double-digit TB size, it would occasionally fail otherwise. This was with Postgres / barman / pigz, FWIW. Never saw it myself, so I have no idea what the cause was.
      - kijin 3y ago
        I've used mysqldump|gzip when migrating a ~1TB database to an incompatible version of MySQL/MariaDB. It's slow but very reliable. I think the single-threaded pipe is the bottleneck. The process never took up more than one full CPU core.
    - maskros 3y ago
      Or use "VACUUM INTO" to get a backup that is is even faster (and easier) to restore.
- formerly_proven 3y ago
  Pretty much every journaling mechanism requires snapshots for reliable backups. This includes either of SQLite's modes (undo journal and WAL).
- lupusreal 3y ago
  > need a filesystem snapshotting mechanism to reliably back them up (so you don't have one state in the database and another in the wal) VACUUM INTO?
- sureglymop 3y ago
  You can't use a filesystem snapshotting mechanism... Even when not using wal mode, you can't just cp the .db file somewhere (in some circumstances, yes). Instead, sqlite provides an online backup api specifically for creating backups. This also takes wal mode into account.
fweimer 3y ago
Some operating systems and file systems do not support renaming files that are opened by the same or another process.

slau 3y ago

Presumably because you don't want to keep a full directory of WALs up to infinity. This uses WAL_size*2 on the disk and keeps it from growing beyond that.

ajnin 3y ago

This is to handle the case where transactions keep getting written to the wal file while it is being "checkpointed", preventing it from ever being deleted. You only need to alternate between two files, one checkpointed, one written to, for this.

wongarsu 3y ago

when doing backups, managing file permissions, etc, it's really convenient to only have a fixed number of known files to worry about. Switching to WAL already makes handling Sqlite databases much less convenient, since you now have three files instead of one, and need a filesystem snapshotting mechanism to reliably back them up (so you don't have one state in the database and another in the wal). Making the filenames and number of files less predictable would make that mode not worth it for many use cases

dezgeg 3y ago
Even the mechanism predating WALv1 (rollback journal) uses two files. I don't think there is any way of using SQLite crash-proof with just a single file. Besides, even if the database is single-file it's still necessary to use filesystem snapshotting for live backup, or it's likely to get an inconsistent copy.
- kijin 3y ago
  With smallish databases, just pipe the .dump command to gzip. No need to snapshot an entire filesystem just to back up a few hundred megabytes (what I would consider "smallish"). Backup and restore speeds aren't a significant concern at those sizes, anyway, and you get human-readable backups that can be inspected with standard Unix tools.
  - deberon 3y ago
    We would back up double-digit GB MySQL databases by piping mysqldump into gzip as well. Like you I’m sure there’s a size limit where that doesn’t work. I never found out if it was CPU or memory constrained though.
    - sgarland 3y ago
      Where I’m at, the DBAs shifted from pipes to temporary files. They said at the double-digit TB size, it would occasionally fail otherwise. This was with Postgres / barman / pigz, FWIW. Never saw it myself, so I have no idea what the cause was.
    - kijin 3y ago
      I've used mysqldump|gzip when migrating a ~1TB database to an incompatible version of MySQL/MariaDB. It's slow but very reliable. I think the single-threaded pipe is the bottleneck. The process never took up more than one full CPU core.
  - maskros 3y ago
    Or use "VACUUM INTO" to get a backup that is is even faster (and easier) to restore.
formerly_proven 3y ago
Pretty much every journaling mechanism requires snapshots for reliable backups. This includes either of SQLite's modes (undo journal and WAL).
lupusreal 3y ago
> need a filesystem snapshotting mechanism to reliably back them up (so you don't have one state in the database and another in the wal) VACUUM INTO?
sureglymop 3y ago
You can't use a filesystem snapshotting mechanism... Even when not using wal mode, you can't just cp the .db file somewhere (in some circumstances, yes). Instead, sqlite provides an online backup api specifically for creating backups. This also takes wal mode into account.

fweimer 3y ago

Some operating systems and file systems do not support renaming files that are opened by the same or another process.