Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite: Wal2 Mode

sqlite.org

Post reply on HN
Print view View original

11–20 of 99 posts

Re: SQLite: Wal2 Mode

#11

Post by avereveard »

tomashubelbauer wrote: post #5
miroljub wrote: post #2

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

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.

Il give this thread two or three more replies before it reimplement logrotate from first principles

Re: SQLite: Wal2 Mode

#12

Post by slau »

WolfOliver wrote: post #8
miroljub wrote: post #2

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

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.

Re: SQLite: Wal2 Mode

#13

Post by nbevans »

rmbyrro wrote: post #10
tomashubelbauer wrote: post #5

Earlier quoted context omitted.

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.

That would make reading slower, since readers need to read from WAL as well.

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.

Re: SQLite: Wal2 Mode

#14

Post by [deleted] »

forinti wrote: post #4
miroljub wrote: post #2

> 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 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 larg…

[deleted]

Re: SQLite: Wal2 Mode

#15

Post by andix »

ceeam wrote: post #7
vdaea wrote: post #3

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

Would it be a problem since the wal you delete, its inode, will still be open and processed at the DB closing normally? Just guessing, never tried that.

There are cases where the wal file is not merged on shutdown of the application. I think a corrupted database can also prevent merging the wal file automatically. A corrupted database can often be repaired, but it needs to be done manually.

I've been bitten badly by that issue once. I just mounted the .db file into a docker container and didn't realize that sqlite creates wal files. On an non-graceful shutdown of the application the wal file was not merged into the db and the container deleted. And around a day of changes were lost.

Conclusion: Sqlite databases should be placed into their own folder, so it's obvious that it's not always just one file.

Re: SQLite: Wal2 Mode

#16

Post by ajnin »

WolfOliver wrote: post #8
miroljub wrote: post #2

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

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.

Re: SQLite: Wal2 Mode

#18

Post by magnat »

Microsoft SQL Server uses similar architecture [1], but instead of using separate log files, it allocates Virtual Log Files (VLF) inside a physical (on-disk) log file. VLFs are allocated from a ring buffer and apparently there can be several thousands of them before things start to break.

[1] https://learn.microsoft.com/en-us/sql/relational-databases/s...

Re: SQLite: Wal2 Mode

#19

Post by ricardobeat »

andix wrote: post #15
ceeam wrote: post #7

Earlier quoted context omitted.

Would it be a problem since the wal you delete, its inode, will still be open and processed at the DB closing normally? Just guessing, never tried that.

There are cases where the wal file is not merged on shutdown of the application. I think a corrupted database can also prevent merging the wal file automatically. A corrupted database can often be repaired, but it needs to be done manually. I've been bitten badly by that issue once. I just mounted the .db file into a docker container and didn't realize that sqlite creates wal files. On an non-graceful shutdown of the…

This is configurable, and for small things you might disable WAL completely.

When using WAL, if you’re copying or backing up the database it’s possible to force a checkpoint, then you can copy the .db file alone knowing exactly up to when it contains data.

Re: SQLite: Wal2 Mode

#20

Post by andix »

ricardobeat wrote: post #19
andix wrote: post #15

Earlier quoted context omitted.

There are cases where the wal file is not merged on shutdown of the application. I think a corrupted database can also prevent merging the wal file automatically. A corrupted database can often be repaired, but it needs to be done manually. I've been bitten badly by that issue once. I just mounted the .db file into a docker container and didn't realize that sqlite creates wal files. On an non-graceful shutdown of the…

This is configurable, and for small things you might disable WAL completely. When using WAL, if you’re copying or backing up the database it’s possible to force a checkpoint, then you can copy the .db file alone knowing exactly up to when it contains data.

I had to learn all that the hard way ;)
Post reply on HN