Great information! I'm looking forward to seeing this mode in action. Do you know when it will be available for general use?
SQLite: Wal2 Mode
21–30 of 99 posts
Re: SQLite: Wal2 Mode
#22> In wal2 mode, the system uses two wal files instead of one. The files are named " -wal" and " -wal2", where " " is of course the name of the database file. When data is written to the database, the writer begins by appending the new data to the first wal file. Once the first wal file has grown large enough, writers switch to appending data to the second wal file. At this point the first wal file can be checkpointed…
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 and number of files less predictable would make that mode not worth it for many use cases
Re: SQLite: Wal2 Mode
#23>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.
Re: SQLite: Wal2 Mode
#24So it's basically double buffering, but for databases? That makes sense.
Re: SQLite: Wal2 Mode
#25Earlier 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…
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.
Re: SQLite: Wal2 Mode
#26So it's basically double buffering, but for databases? That makes sense.
Tripple
Re: SQLite: Wal2 Mode
#27As far as I can tell, this feature has not yet been released.
> As far as I can tell, this feature has not yet been released.
It's been off-trunk since its inception in Oct. 2017 and there's been no discussion within the project of merging it into trunk (why that is i cannot speculate). It is actively maintained for use with the bedrock branch, as can be seen in the project's timeline:
Re: SQLite: Wal2 Mode
#28> In wal2 mode, the system uses two wal files instead of one. The files are named " -wal" and " -wal2", where " " is of course the name of the database file. When data is written to the database, the writer begins by appending the new data to the first wal file. Once the first wal file has grown large enough, writers switch to appending data to the second wal file. At this point the first wal file can be checkpointed…
> 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 to do so again
Probably because of this.
> but it does mean that the wal file may grow indefinitely if the checkpointer never gets a chance to finish without a writer appending to the wal file. There are also circumstances in which long-running readers may prevent a checkpointer from checkpointing the entire wal file - also causing the wal file to grow indefinitely in a busy system.
> Wal2 mode does not have this problem. In wal2 mode, wal files do not grow indefinitely even if the checkpointer never has a chance to finish uninterrupted.
I don't get how wal2 fixes the long-running reader problem though. Maybe they were just referring to the former problem?
Re: SQLite: Wal2 Mode
#29Earlier quoted context omitted.
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…
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.
Re: SQLite: Wal2 Mode
#30tangential 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 general. Never found an answer to this.