How common is an automatically expanding WAL across other DBs? I'm most familiar with MySQL which uses a fixed size WAL (unless something changed recently). That of course comes with other tradeoffs like potentially blocking writes if checkpointing falls behind. But I'm curious if SQLite is an oddity in this respect compared to other DBs. Since it is used in embedded contexts it might prefer to save less data on disk in the common case, but with the edge case that wal2 mode now fixes.
SQLite: Wal2 Mode
51–60 of 99 posts
Re: SQLite: Wal2 Mode
#52tangential point: one thing that always bothered me about WAL is that it is supposed to exist to help maintain data integrity, recover from crash etc but that file itself is written (committed to disk reliably) in batches and not after every change to the database, apparently to gain performance. Doesn't that defeat the purpose? How haven't things broken down despite this? Not specific to sqlite but databases in gene…
You only need to achieve durability on a COMMIT.
Re: SQLite: Wal2 Mode
#53I wonder how this would compare to just punching a hole in the already applied section of the WAL (1) file. It seems that this should allow the filesystem to free the disk space similarly to if the file had been deleted while allowing the writers to keep writing. I guess eventually it would become a problem as you hit max logical file size but it seems that you can likely bet on getting a chance to truncate it before hitting exabytes.
Re: SQLite: Wal2 Mode
#54Earlier quoted context omitted.
I'm not much into the details but I'm wondering why is it not always writing to " -wal" once it is full, it is renamed to " -wal-0" and it starts writing again into " -wal", once it is full again it is renamed to " -wal-1" ?
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…
> 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?
Re: SQLite: Wal2 Mode
#55Earlier quoted context omitted.
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.
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.
Or use "VACUUM INTO" to get a backup that is is even faster (and easier) to restore.
Re: SQLite: Wal2 Mode
#56Earlier quoted context omitted.
> Checkpointers do not block writers, and writers do not block checkpointers. This means that, even following the checkpoint, the wal file cannot be overwritten or deleted, and so all subsequent transactions must also be appended to the wal file. The work of the checkpointer is not wasted - SQLite remembers which parts of the wal file have already been copied into the db file so that the next checkpoint does not have…
> I don't get how wal2 fixes the long-running reader problem though. Maybe they were just referring to the former problem? Because with a single wal file you can't checkpoint it during a read since said file may change out from under you. With two wal files, the one you are actively appending to can be treated like in wal1 mode but the one that isn't being appended to is immutable for the time being just like the mai…
> once the wal file you are actively appending to reaches a sufficient size, you "lock" that one
so there might eventually be wal3 and wal4 files and so on?
Re: SQLite: Wal2 Mode
#57How common is an automatically expanding WAL across other DBs? I'm most familiar with MySQL which uses a fixed size WAL (unless something changed recently). That of course comes with other tradeoffs like potentially blocking writes if checkpointing falls behind. But I'm curious if SQLite is an oddity in this respect compared to other DBs. Since it is used in embedded contexts it might prefer to save less data on disk…
For example, RocksDB/LevelDB have done this from the very beginning. If a WAL file gets too long then a new one is created and the old one is sorted and written to an sstable asynchronously.
Re: SQLite: Wal2 Mode
#58>In wal2 mode, the system uses two wal files instead of one. The files are named " -wal" and " -wal2", Heh, I wonder how many people will delete the "wal" file thinking that, since they switched to wal2, the wal file must be a leftover.
If people just randomly delete files they don't fully understand on a production system maybe they should be bitten.
But in development...
Re: SQLite: Wal2 Mode
#59Earlier quoted context omitted.
in the case of sqlite though, the technology is often used as a standalone file format. So it is very tempting to consider the ".sqlite" file to be the one containing all the data, and all the rest to be temporary files that don't matter much. IMHO this (having a variable number of files containing the data, depending on your configuration) is the only real design quirks of this technology.
> in the case of sqlite though, the technology is often used as a standalone file format. So it is very tempting to consider the ".sqlite" file to be the one containing all the data, and all the rest to be temporary files that don't matter much. If you're using it as a standalone file format you presumably shouldn't leave .sqlite files with associated wal files lying around in places where users are going to get conf…
If you use sqlite as a standalone file format for an app that has user managed files then it is hard to avoid this confusion. Rollback journal mode creates a temporary file as well.
Also, a scheduled backup process might come along at any moment and non-atomically copy the database file and any -journal or -wal files.
Ideally, user visible files should survive copying at random points in time without corruption and without losing too much recent data.
Having read "How To Corrupt An SQLite Database File"[1], I'm still not quite sure how to achieve this.
Re: SQLite: Wal2 Mode
#60Earlier quoted context omitted.
> I don't get how wal2 fixes the long-running reader problem though. Maybe they were just referring to the former problem? Because with a single wal file you can't checkpoint it during a read since said file may change out from under you. With two wal files, the one you are actively appending to can be treated like in wal1 mode but the one that isn't being appended to is immutable for the time being just like the mai…
> once the wal file you are actively appending to reaches a sufficient size, you "lock" that one so there might eventually be wal3 and wal4 files and so on?
nope. since if the wal you are checkpointing isn't done yet, you just wait to "lock" and switch files until that operation is complete.
Checkpointing can be considered "lock free" since the operation will always eventually complete. How long it takes will depend on the wal file being checkpointed into the db but it'll eventually complete in some finite amount of time.
Because you know that any given checkpointing operation has to eventually complete, you can simply keep appending to the current "append" wal file and then tackle those changes when you finish the current checkpoint op (at which point the wal file you just finished checkpointing is free to take the appends).