Re: [EXTERNAL] Why does WAL prevent the need for async IO?
I don't think so.
Async IO module creates a queue of pages that will be written to the database file on disk according to available IO bandwidth.
WAL mode creats a queue of pages from committed transactions that are written to the database file on disk according to available IO bandwidth.
Both allow the writer to not have to wait until the data physically hits the disk. Introducing a second level of buffering is not likely to help with speed but multiplies the risk of losing durability. Since WAL mode in standard sync does not call fsync() during commit, a crash could prevent the commit record reaching the WAL file. And since async IO stores writes in a queue and pretends they are alredy completed, a crash during a checkpoint operation could cause (possibly very much older) writes to be lost even though the WAL file has recorded them as completed.
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von test user
Gesendet: Dienstag, 13. August 2019 11:44
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Why does WAL prevent the need for async IO?
>Quote: The use of WAL mode largely obviates the need for this
>The WAL mode does not change the fact that these operations will
>block the application process that embeds SQLite:
> - Slow read queries.
> - A large amount of writes.
>So would something like the original async module still be useful,
>even with WAL mode?
In WAL mode one may achieve a similar effect by simply setting the autocheckpoint to 0 pages and running the checkpoint operations in their own thread.
This will prevent the checkpoints from delaying the COMMIT operation because no autocheckpoint is done at any COMMIT time ever, and move the overhead taken to do the checkpoint to another thread separate from the threads performing database operations. Not exactly the same, but probably better since it will not affect the ACID properties of the database and have no effect on sharing the database file amongst multiple processes.
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.