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

91–99 of 99 posts

Re: SQLite: Wal2 Mode

#91

Post by anarazel »

dist-epoch wrote: post #89
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.

I'm a postgres dev, so I'm surely too close to actually see what's nebulous. That said, I don't think it's that nebulous:

- data in WAL segments has to be checkpointed

- no replication slot, physical or logical, may require the WAL file (see the pg_replication_slots view)

- archiving, if configured, has to have archived the file (see pg_stat_archiver)

It used to be more complicated, for historical reasons we used to keep two checkpoints worth of WAL around, but I don't think any supported versions of postgres still have that behavior.

Edit:

What's more mysterious is whether WAL files are removed when not necessary, or whether they're recycled (renamed to be reused). That's indeed a bit hard to get insight to.

Re: SQLite: Wal2 Mode

#92

Post by pstuart »

alberth wrote: post #66
pstuart wrote: post #65

Earlier quoted context omitted.

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.

I'd put it more at "multi user" rather than client/server. I'm confident that that goal will never be acceptable reasoning for any changes or improvements to the code.

Re: SQLite: Wal2 Mode

#93

Post by kijin »

deberon wrote: post #33
kijin wrote: post #29

Earlier quoted context omitted.

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.

We would back up double-digit GB MySQL databases by piping mysqldump into gzip as well. Like you I’m sure there’s a size limit where that doesn’t work. I never found out if it was CPU or memory constrained though.

I've used mysqldump|gzip when migrating a ~1TB database to an incompatible version of MySQL/MariaDB. It's slow but very reliable. I think the single-threaded pipe is the bottleneck. The process never took up more than one full CPU core.

Re: SQLite: Wal2 Mode

#94

Post by alberth »

pstuart wrote: post #92
alberth wrote: post #66

Earlier quoted context omitted.

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.

I'd put it more at "multi user" rather than client/server. I'm confident that that goal will never be acceptable reasoning for any changes or improvements to the code.

I’d still say “client/server” because:

a. SQLite, by default, doesn’t allow multiple writers.

b. There’s also real challenges to writing to a non-local (network) filesystems

https://www.sqlite.org/useovernet.html

Re: SQLite: Wal2 Mode

#95

Post by alberth »

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

While HC-tree looks promising, keep in mind the very first sentence of your link

  This project contains no code stable enough to deploy. The database backend works well enough to run some test cases, but is still quite incomplete.
Link to shortcomings, some of which are significant

https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html#s...

Re: SQLite: Wal2 Mode

#96

Post by ddorian43 »

mike_hearn wrote: post #82
ddorian43 wrote: post #78

Earlier quoted context omitted.

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.

WAL splitting isn't connected to memtable flushing, they are separate processes.

Re: SQLite: Wal2 Mode

#97

Post by worksonmine »

IshKebab wrote: post #81
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.

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

That's hyperbole.

Re: SQLite: Wal2 Mode

#98

Post by pstuart »

alberth wrote: post #94
pstuart wrote: post #92

Earlier quoted context omitted.

I'd put it more at "multi user" rather than client/server. I'm confident that that goal will never be acceptable reasoning for any changes or improvements to the code.

I’d still say “client/server” because: a. SQLite, by default, doesn’t allow multiple writers. b. There’s also real challenges to writing to a non-local (network) filesystems https://www.sqlite.org/useovernet.html

For sport I'll counter with:

a. SQLite, by default, does allow multiple writers to connect, but only one can write at a time.

b. NFS was never suggested to be used -- embedding it in an app that exposes a network api works fine.

c. The behavior being discussed (multiple concurrent writers) already kind of exists (multiple writers) and this would just make them more performant.

Re: SQLite: Wal2 Mode

#99

Post by runiq »

gregw2 wrote: post #73
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…

Thanks a lot for this extensively sourced post, I learned a lot today! :)
Post reply on HN