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

81–90 of 99 posts

Re: SQLite: Wal2 Mode

#81

Post by IshKebab »

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.

"If people just randomly don't look at the road and crash then maybe they should die."

You make mistakes. Do you want them to be as painful as possible?

Re: SQLite: Wal2 Mode

#82

Post by mike_hearn »

ddorian43 wrote: post #78
mike_hearn wrote: post #77

Earlier quoted context omitted.

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.

Seems a bit pedantic. The memtable is (when fully flushing writes) a derivative of the WAL. Or vice-versa if you like. They hold equivalent data, organized differently (yes yes modulo tombstones). Anyway you're right, I was being lazy in not writing out memtable explicitly.

Re: SQLite: Wal2 Mode

#83

Post by HALtheWise »

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

After investigating, it looks like there's actually two separate things here: "bedrock" for accessing the sqlite database over the network and "page-level locking" for allowing concurrent write transactions.

https://sqlite.org/src/doc/754ad35c/README-server-edition.ht...

I assume you could use the latter without the former.

Re: SQLite: Wal2 Mode

#84

Post by bob1029 »

magnat wrote: post #18

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

The design of SQL Server has grown increasingly interesting to me over the years. I still prefer SQLite for personal projects, but as the size of our team and average customer goes up, I am looking for something that others can reason with if need be. Ideally, something that I can throw over the fence and not have to answer a bunch of confused questions about pragmas and quirky connection reuse code.

This WAL2 feature is a perfect example of a new kind of concern I have. SQLite has a really competent facility for handling write-ahead today, but it has these edge cases where it may fail under adverse (but totally plausible) scenarios. I haven't yet had a completely corrupted SQLite database, but I have had one incident on a QA server where I had to delete the WAL/SHM files to get the database to work again.

Re: SQLite: Wal2 Mode

#85

Post by dspillett »

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…

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

Re: SQLite: Wal2 Mode

#86

Post by dspillett »

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.

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.

Re: SQLite: Wal2 Mode

#87

Post by HALtheWise »

HALtheWise wrote: post #83
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...

After investigating, it looks like there's actually two separate things here: "bedrock" for accessing the sqlite database over the network and "page-level locking" for allowing concurrent write transactions. https://sqlite.org/src/doc/754ad35c/README-server-edition.ht... I assume you could use the latter without the former.

It could also be they're talking about the hc-tree branch https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html

Re: SQLite: Wal2 Mode

#88

Post by dspillett »

rmbyrro wrote: post #38
nbevans wrote: post #13

Earlier quoted context omitted.

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.

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.

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

Re: SQLite: Wal2 Mode

#89

Post by dist-epoch »

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…

Postgres creates multiple WAL files of fixed size. Old ones are deleted according to nebulous rules.

Re: SQLite: Wal2 Mode

#90

Post by fweimer »

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

Some operating systems and file systems do not support renaming files that are opened by the same or another process.
Post reply on HN