5 ms·

> In wal2 mode, the system uses two wal files instead of one. The files are named "<database>-wal" and "<database>-wal2", where "<database>" is of course the na

> In wal2 mode, the system uses two wal files instead of one. The files are named "<database>-wal" and "<database>-wal2", where "<database>" 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 (after which it can be overwritten). Then, once the second wal file has grown large enough and the first wal file has been checkpointed, writers switch back to the first wal file. And so on. Looks so logical that I don't understand why WAL mode was not implemented like this from the get go. Probably an optimization wrongly dismissed as premature? Anyways, looking forward to this mode reaching general availability.

forinti 3y ago
I'm just speculating here, but in a normal database you would have different processes writing the wal files to the database or archives. You don't have that with sqlite, so I don't see an obvious advantage for this, except if they now spawn a process or thread to do this concurrently. Edit: so I read the doc (shame on me) and it has nothing to do with speed. Its purpose is to prevent a wal file from growing too large.
- hahn-kev 3y ago
  I believe SQLite supports accessing a single database file from multiple processes or threads at once
  - arcbyte 3y ago
    There can be many readers simultaneously, but only ever one writer at a time.
- WolfOliver 3y ago
  I guess it is to make it crash save, e.g. in case the server dies in the middle of a write, the database would be corrupt after restart, with the wal file it can recover.
- deleted 3y ago
  [deleted]
tomashubelbauer 3y ago
Now I can't help but wonder if there should be a `waln` mode where the WAL files would round robin instead of alternate between just two potentially allowing for much more intense write cadence.
- rmbyrro 3y ago
  That would make reading slower, since readers need to read from WAL as well.
  - nbevans 3y ago
    There is a .shm (shared memory) file that all SQLite connections to the database would use to determine which page ranges are in the WAL versus main DB file. So that overhead already exists when WAL is enabled.
    - rmbyrro 3y ago
      I'm not familiar with its inner workings, but if there were n WAL files, I'd expect the overhead to be a function of n.
      - dspillett 3y ago
        Possibly a bad function of n (linear or worse) on IO systems based on traditional drives or dirt cheap SSDs, once IO becomes your main bottleneck (i.e. when you data no longer fits easily in RAM).
- avereveard 3y ago
  Il give this thread two or three more replies before it reimplement logrotate from first principles
- dspillett 3y ago
  Presumably it was at least considered. I would guess that the negatives would be taking more disk space (and potentially imposing greater IO concurrency though that probably isn't a great issue with modern storage systems), and it being more complex (if not in the core design, then in having extra edge cases to make sure are all covered). Other databases do do similar to what you suggest, though obviously the trade-offs will differ because of other different internals and product priorities, so it would have been thought about. For instance MS SQL Server has multiple “virtual logs” in its log files, for at least some overlapping reasons.
WolfOliver 3y ago
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.
athoscouto 3y ago
> 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?
- stavros 3y ago
  I suspect that having two WAL files means you can freely checkpoint the one that isn't currently being written to, but that's just a guess.
- CuriousCosmic 3y ago
  > 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 main database. This means you can treat the actual db file and the immutable wal file together as one immutable database file with some underlying abstraction. That abstraction then allows you to perform the checkpoint operation since the abstraction can keep all that immutable data accessible in some form or another while reworking the data structure of the db file. Then once the checkpoint is complete, the abstraction can clear the now redundant immutable wal file, become transparent, and just present the underling single DB file. And now once the wal file you are actively appending to reaches a sufficient size, you "lock" that one, rendering it immutable, and switch over to appending to the cleared wal file you were previously checkpointing. With this you can now checkpoint again without blocking reads or writes.
foofie 3y ago
> Looks so logical (...) https://en.wikipedia.org/wiki/Log-structured_merge-tree https://en.wikipedia.org/wiki/Log-structured_merge-tree
quietbritishjim 3y ago
It may simply have been a design oversight at the time. However, the page notes the following difference of WAL2 from WAL, which could be an issue in some cases: > In wal mode, a checkpoint may be attempted at any time. In wal2 mode, the checkpointer has to wait until writers have switched to the "other" wal file before a checkpoint can take place.
dspillett 3y ago
> Looks so logical that I don't understand why WAL mode was not implemented like this from the get go. Probably an optimization wrongly dismissed as premature? While it has advantages, it is also more code so more possible places to hide, and other disadvantages hence it doesn't completely deprecate the other WAL mode. Also the advantages might not have been as commonly cared about in sqlite in earlier times, but it is being used in more & more places and sometimes at larger scales or with more significant concurrency needs, and the core has been pretty darn stable for quite some time, all of which factors change the dynamics of what is worth committing the dev/testing time to in terms of usefulness to the end users.

forinti 3y ago

I'm just speculating here, but in a normal database you would have different processes writing the wal files to the database or archives. You don't have that with sqlite, so I don't see an obvious advantage for this, except if they now spawn a process or thread to do this concurrently. Edit: so I read the doc (shame on me) and it has nothing to do with speed. Its purpose is to prevent a wal file from growing too large.

hahn-kev 3y ago
I believe SQLite supports accessing a single database file from multiple processes or threads at once
- arcbyte 3y ago
  There can be many readers simultaneously, but only ever one writer at a time.
WolfOliver 3y ago
I guess it is to make it crash save, e.g. in case the server dies in the middle of a write, the database would be corrupt after restart, with the wal file it can recover.
deleted 3y ago
[deleted]

tomashubelbauer 3y ago

Now I can't help but wonder if there should be a `waln` mode where the WAL files would round robin instead of alternate between just two potentially allowing for much more intense write cadence.

rmbyrro 3y ago
That would make reading slower, since readers need to read from WAL as well.
- nbevans 3y ago
  There is a .shm (shared memory) file that all SQLite connections to the database would use to determine which page ranges are in the WAL versus main DB file. So that overhead already exists when WAL is enabled.
  - rmbyrro 3y ago
    I'm not familiar with its inner workings, but if there were n WAL files, I'd expect the overhead to be a function of n.
    - dspillett 3y ago
      Possibly a bad function of n (linear or worse) on IO systems based on traditional drives or dirt cheap SSDs, once IO becomes your main bottleneck (i.e. when you data no longer fits easily in RAM).
avereveard 3y ago
Il give this thread two or three more replies before it reimplement logrotate from first principles
dspillett 3y ago
Presumably it was at least considered. I would guess that the negatives would be taking more disk space (and potentially imposing greater IO concurrency though that probably isn't a great issue with modern storage systems), and it being more complex (if not in the core design, then in having extra edge cases to make sure are all covered). Other databases do do similar to what you suggest, though obviously the trade-offs will differ because of other different internals and product priorities, so it would have been thought about. For instance MS SQL Server has multiple “virtual logs” in its log files, for at least some overlapping reasons.

WolfOliver 3y ago

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.

athoscouto 3y ago

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

stavros 3y ago
I suspect that having two WAL files means you can freely checkpoint the one that isn't currently being written to, but that's just a guess.
CuriousCosmic 3y ago
> 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 main database. This means you can treat the actual db file and the immutable wal file together as one immutable database file with some underlying abstraction. That abstraction then allows you to perform the checkpoint operation since the abstraction can keep all that immutable data accessible in some form or another while reworking the data structure of the db file. Then once the checkpoint is complete, the abstraction can clear the now redundant immutable wal file, become transparent, and just present the underling single DB file. And now once the wal file you are actively appending to reaches a sufficient size, you "lock" that one, rendering it immutable, and switch over to appending to the cleared wal file you were previously checkpointing. With this you can now checkpoint again without blocking reads or writes.

foofie 3y ago

> Looks so logical (...) https://en.wikipedia.org/wiki/Log-structured_merge-tree https://en.wikipedia.org/wiki/Log-structured_merge-tree

quietbritishjim 3y ago

It may simply have been a design oversight at the time. However, the page notes the following difference of WAL2 from WAL, which could be an issue in some cases: > In wal mode, a checkpoint may be attempted at any time. In wal2 mode, the checkpointer has to wait until writers have switched to the "other" wal file before a checkpoint can take place.

dspillett 3y ago

> Looks so logical that I don't understand why WAL mode was not implemented like this from the get go. Probably an optimization wrongly dismissed as premature? While it has advantages, it is also more code so more possible places to hide, and other disadvantages hence it doesn't completely deprecate the other WAL mode. Also the advantages might not have been as commonly cared about in sqlite in earlier times, but it is being used in more & more places and sometimes at larger scales or with more significant concurrency needs, and the core has been pretty darn stable for quite some time, all of which factors change the dynamics of what is worth committing the dev/testing time to in terms of usefulness to the end users.