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

71–80 of 99 posts

Re: SQLite: Wal2 Mode

#71

Post by ddorian43 »

mike_hearn wrote: post #57
grogers wrote: post #51

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…

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.

> and the old one is sorted and written to an sstable asynchronously

This doesn't happen.

Re: SQLite: Wal2 Mode

#72

Post by quietbritishjim »

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…

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.

Re: SQLite: Wal2 Mode

#73

Post by gregw2 »

runiq wrote: post #24

So it's basically double buffering, but for databases? That makes sense.

In a high-level sense, yes! But it kind of depends on which part of the design pattern you mean by "double buffering for databases".

With double-buffering (2d/3d graphics) you are literally writing the final pixel-level data to the back buffer.[1]

In a database WAL scenario, to further analogize, it's more like you are writing the 2d/3d graphics commands to the buffer and executing them later. Because that is part of the point of the WAL -- it results in reduced disk writes because only the log file needs to be flushed to disk to guarantee a transaction is committed, rather than every data file/byte(/pixel) changed by the transaction.[2][3] (The WAL content is loosely a bit more like 3D (or 2D) vertex buffer objects/display lists [4] if you are familiar with those.)

Swapping the two WAL files though and alternating writing to each is yes like double buffering.

A third similar design pattern (to WALs) is used in operating systems' journaling filesystems[5] and actually was a contribution from OSes adopting database WAL techniques back in the 1990s.

Apologies if you know all this.

[1] https://en.wikipedia.org/wiki/Multiple_buffering#Double_buff...

[2] https://www.postgresql.org/docs/15/wal-intro.html

[3] https://en.wikipedia.org/wiki/Write-ahead_logging

[4] https://en.wikipedia.org/wiki/Display_list

[5] https://en.wikipedia.org/wiki/Journaling_file_system

Re: SQLite: Wal2 Mode

#74

Post by CuriousCosmic »

liuliu wrote: post #61
CuriousCosmic wrote: post #48

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

But Wal also keeps "history" so one reader transaction can see x0 from wal1, another reader can see x1 from Wal1. Wal1 cannot be merged into main db otherwise x0 is lost?

Yes that is correct. You can't checkpoint data after the oldest snapshot. But to my knowledge there's no way to force a read snapshot to continue to exist.

So while you can make multiple back to back reads that use the same snapshot, I believe there's no guarantee that the snapshot will still exist when the next read is opened unless the previous read is also still open (in which case an error is returned).

That seems to set an upper bound on how long a reader can block a checkpoint (unless the reader is intentionally staging reads to block the checkpoint).

Theoretically you could implement checkpoints that flatten everything between snapshots into single commits but the complexity and overhead probably isn't worth it given that the only real blocker for wal2 is an edge case that is nigh impossible to encounter unless you intentionally try to trigger it.

Re: SQLite: Wal2 Mode

#76

Post by fbdab103 »

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.

As opposed to the `-journal` file already created?

Re: SQLite: Wal2 Mode

#77

Post by mike_hearn »

ddorian43 wrote: post #71
mike_hearn wrote: post #57

Earlier quoted context omitted.

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.

> and the old one is sorted and written to an sstable asynchronously This doesn't happen.

No? Am I mis-remembering? Their wiki says:

The logfile is a sequentially-written file on storage. When the memtable fills up, it is flushed to a sstfile on storage and the corresponding logfile can be safely deleted.

[...]

Background compaction threads are also used to flush memtable contents to a file on storage. If all background compaction threads are busy doing long-running compactions, then a sudden burst of writes can fill up the memtable(s) quickly, thus stalling new writes. This situation can be avoided by configuring RocksDB to keep a small set of threads explicitly reserved for the sole purpose of flushing memtable to storage.

Re: SQLite: Wal2 Mode

#78

Post by ddorian43 »

mike_hearn wrote: post #77
ddorian43 wrote: post #71

Earlier quoted context omitted.

> and the old one is sorted and written to an sstable asynchronously This doesn't happen.

No? Am I mis-remembering? Their wiki says: The logfile is a sequentially-written file on storage. When the memtable fills up, it is flushed to a sstfile on storage and the corresponding logfile can be safely deleted. [...] Background compaction threads are also used to flush memtable contents to a file on storage. If all background compaction threads are busy doing long-running compactions, then a sudden burst of wri…

The in-memory memtable gets converted to sstable.

The WAL is ONLY read after crashing, to fill a new memtable.

Your comment looked like "WAL is sorted and converted to sstable":

> 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

#79

Post by macintux »

worksonmine wrote: post #23
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.

If people just randomly delete files they don't fully understand on a production system maybe they should be bitten.

~25 years ago I rebooted a Solaris server, only to discover that some previous person in my role apparently had, while scrounging around for things to delete to free up disk space on the root partition, removed the kernel file.

It happens.

(I'm really, really hoping the "previous" person wasn't me.)

Re: SQLite: Wal2 Mode

#80

Post by liuliu »

CuriousCosmic wrote: post #74
liuliu wrote: post #61

Earlier quoted context omitted.

But Wal also keeps "history" so one reader transaction can see x0 from wal1, another reader can see x1 from Wal1. Wal1 cannot be merged into main db otherwise x0 is lost?

Yes that is correct. You can't checkpoint data after the oldest snapshot. But to my knowledge there's no way to force a read snapshot to continue to exist. So while you can make multiple back to back reads that use the same snapshot, I believe there's no guarantee that the snapshot will still exist when the next read is opened unless the previous read is also still open (in which case an error is returned). That seem…

Open an BEGIN transaction forces read from a particular snapshot in SQLite. There are some complications around lock upgrade in that case: BEGIN a, read, BEGIN b, read, write from a transaction fail because cannot upgrade read lock to write lock. Other than that, in WAL mode, it will work exactly as expected:

BEGIN a, read x0 BEGIN b, write x1, END b BEGIN c, read will return x1 Back to a transaction, read again, return x0 still.

Post reply on HN