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

61–70 of 99 posts

Re: SQLite: Wal2 Mode

#61

Post by liuliu »

CuriousCosmic wrote: post #48
athoscouto wrote: post #28

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

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?

Re: SQLite: Wal2 Mode

#62

Post by riwsky »

willvarfar wrote: post #56
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…

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

Think of them more as “walblue” and “walgreen”

Re: SQLite: Wal2 Mode

#63

Post by athoscouto »

CuriousCosmic wrote: post #60
willvarfar wrote: post #56

Earlier quoted context omitted.

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

Wouldn't that require the reader to finish? Making the reader block the checkpoint from wrapping up?

Re: SQLite: Wal2 Mode

#65

Post by pstuart »

alberth wrote: post #64

Bedrock Bedrock is the more interesting branch. It’s WAL2 + CONCURRENT It’s also the branch Expensify uses to scale to 4M QPS, on a single node (6-years ago) https://sqlite.org/src/timeline?r=bedrock https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a...

That certainly sounds appealing. Do you have any insights into if and when that might land on trunk?

Re: SQLite: Wal2 Mode

#66

Post by alberth »

pstuart wrote: post #65
alberth wrote: post #64

Bedrock Bedrock is the more interesting branch. It’s WAL2 + CONCURRENT It’s also the branch Expensify uses to scale to 4M QPS, on a single node (6-years ago) https://sqlite.org/src/timeline?r=bedrock https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a...

That certainly sounds appealing. Do you have any insights into if and when that might land on trunk?

I'm not sure it ever will.

The primary use case of this branch is to make SQLite into a more "client/server" like architecture, which deviates from the predominate target use of SQLite (embedded).

Though I too would love a client/server version of SQLite.

Re: SQLite: Wal2 Mode

#67

Post by pstuart »

spiffytech wrote: post #32

Wal2 mode was included in the benchmarks for the HC-tree backend research: https://sqlite.org/hctree/doc/hctree/doc/hctree/threadtest.w...

Yikes! WAL2 + begin-concurrent = minimal TPS. I was expecting the opposite.

Re: SQLite: Wal2 Mode

#68

Post by sureglymop »

wongarsu wrote: post #22
WolfOliver wrote: post #8

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

You can't use a filesystem snapshotting mechanism... Even when not using wal mode, you can't just cp the .db file somewhere (in some circumstances, yes).

Instead, sqlite provides an online backup api specifically for creating backups. This also takes wal mode into account.

Re: SQLite: Wal2 Mode

#69

Post by worksonmine »

paulddraper wrote: post #58
worksonmine wrote: post #23

Earlier quoted context omitted.

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

But in development...

...it can be excused because it's expected to be purged regularly and the developer may not even know databases very well.

Re: SQLite: Wal2 Mode

#70

Post by CuriousCosmic »

athoscouto wrote: post #63
CuriousCosmic wrote: post #60

Earlier quoted context omitted.

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

Wouldn't that require the reader to finish? Making the reader block the checkpoint from wrapping up?

Not if you abstract those reads. It's not like the application is directly opening a file handle. Instead it goes through the sqlite library which accesses not only the db and the wal file but also a non-persistent shm (shared memory) file used for maintaining a consistent view of the write ahead log.

When a reader is reading, it puts a shared lock on the specific data it is reading in the shm file. The checkpointer respects that lock and may (potentially) continue working elsewhere in the db file, slowly updating the indices for checkpointed data in the shm file.

The checkpointer won't change the underlying data that the reader has a lock on but they may have created a new location for it. When the reader is finally done reading, the checkpointer can quickly grab an exclusive lock and update the header in the shm for that data to point to the new destination (and then release said lock). Since the checkpointer never holds this lock for very long, the reader can either block when trying to get a shared lock or it can retry the lock a few moments later. Now that the header in the shm only points to the new location, the checkpointer can safely do whatever it needs to with the data in the old location.

Slowly rinse repeat this until the checkpointer has gotten through the entire write ahead log. At that point there should be no remaining references in the shm to data within the wal file.

Now the wal file can be "unlocked" and if the other wal file is large enough, it can be locked, writes switch over to the other wal, and the cycle repeats anew.

Edit: Importantly, this requires that all readers be on a snapshot that includes at least one commit from the "new" wal file. So compared with wal1, wal2 allows you to have long running readers as long as they start past the last commit of the "previous" wal file.

Post reply on HN