[dead]
SQLite: Wal2 Mode
31–40 of 99 posts
Re: SQLite: Wal2 Mode
#32Wal2 mode was included in the benchmarks for the HC-tree backend research:
https://sqlite.org/hctree/doc/hctree/doc/hctree/threadtest.w...
Re: SQLite: Wal2 Mode
#33Earlier quoted context omitted.
Even the mechanism predating WALv1 (rollback journal) uses two files. I don't think there is any way of using SQLite crash-proof with just a single file. Besides, even if the database is single-file it's still necessary to use filesystem snapshotting for live backup, or it's likely to get an inconsistent copy.
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.
Re: SQLite: Wal2 Mode
#34tangential point: one thing that always bothered me about WAL is that it is supposed to exist to help maintain data integrity, recover from crash etc but that file itself is written (committed to disk reliably) in batches and not after every change to the database, apparently to gain performance. Doesn't that defeat the purpose? How haven't things broken down despite this? Not specific to sqlite but databases in gene…
I think that depends on the setting of PRAGMA synchronous.
I'm not an expert on this, but i think the idea is to separate durability from db corruption. (When synchronous = normal instead of full) you can potentially lose (comitted) data in WAL mode if a power failure happens at just the right moment, however your database won't be corrupt. No data will be half written. Each transaction will either be fully there or fully missing.
Re: SQLite: Wal2 Mode
#35>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.
in the case of sqlite though, the technology is often used as a standalone file format. So it is very tempting to consider the ".sqlite" file to be the one containing all the data, and all the rest to be temporary files that don't matter much.
IMHO this (having a variable number of files containing the data, depending on your configuration) is the only real design quirks of this technology.
Re: SQLite: Wal2 Mode
#36Very similar to the left-right primitive.
https://docs.rs/left-right/latest/left_right/
My understanding is that this technique is older than the linked implementation (though independently rediscovered), but notably, this implementation was written to support a different high concurrency SQL database (for some definition of that) called Noria.
Re: SQLite: Wal2 Mode
#37Earlier 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.
Where I’m at, the DBAs shifted from pipes to temporary files. They said at the double-digit TB size, it would occasionally fail otherwise. This was with Postgres / barman / pigz, FWIW.
Never saw it myself, so I have no idea what the cause was.
Re: SQLite: Wal2 Mode
#38Earlier quoted context omitted.
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.
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.
Re: SQLite: Wal2 Mode
#39Earlier quoted context omitted.
If people just randomly delete files they don't fully understand on a production system maybe they should be bitten.
in the case of sqlite though, the technology is often used as a standalone file format. So it is very tempting to consider the ".sqlite" file to be the one containing all the data, and all the rest to be temporary files that don't matter much. IMHO this (having a variable number of files containing the data, depending on your configuration) is the only real design quirks of this technology.
Indeed sqlite's original mission was "to be a replacement for fopen()", but as more features are being added it looks like that initial simplicity can't be maintained.