Why does WAL prevent the need for async IO?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Why does WAL prevent the need for async IO?

test user
Hello,

On this page:
https://www.sqlite.org/asyncvfs.html

Quote: The use of WAL mode largely obviates the need for this asynchronous
I/O module.


The WAL mode does not change the fact that these operations will still
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?


Thanks.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Why does WAL prevent the need for async IO?

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

-----Urspr√ľngliche Nachricht-----
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?

Hello,

On this page:
https://www.sqlite.org/asyncvfs.html

Quote: The use of WAL mode largely obviates the need for this asynchronous I/O module.


The WAL mode does not change the fact that these operations will still 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?


Thanks.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why does WAL prevent the need for async IO?

Keith Medcalf
In reply to this post by test user

On Tuesday, 13 August, 2019 03:44, test user <[hidden email]> wrote:

>On this page:
>https://www.sqlite.org/asyncvfs.html

>Quote: The use of WAL mode largely obviates the need for this
>asynchronous
>I/O module.

>The WAL mode does not change the fact that these operations will
>still
>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.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users