Write-ahead logging on SAN
(1) By anonymous on 2021-09-09 14:55:39 [link] [source]
I was looking at the documentation and was wondering if write-ahead logging is supported on SAN disks. The documentation states that network file systems are not supported (because of exclusive locks? reliability? latency?), but the SAN disk is exclusive, low latency due to FC, not a network file system (as it is block storage).
Thanks for any opinions!
(2) By Warren Young (wyoung) on 2021-09-09 15:00:00 in reply to 1 [link] [source]
WAL requires shared memory between any processes that use the DB to coordinate access to the log. So, the answer is “yes” only if all processes using the DB are on the same host.
(3) By anonymous on 2021-09-09 15:08:16 in reply to 2 [link] [source]
Yes, it is one single host, using the SAN disk exclusively. So the network file system limitation is more about protecting against distributed writers not sharing memory?
(5) By Warren Young (wyoung) on 2021-09-09 15:17:14 in reply to 3 [link] [source]
You might indeed get away with it then. I’d run the public test suite on your setup before committing any precious data to the DB, though.
(6) By anonymous on 2021-09-09 15:29:08 in reply to 5 [source]
Excellent, thanks for the answer!
(4) By Ryan Smith (cuz) on 2021-09-09 15:17:03 in reply to 1 [link] [source]
To add to Warren's already good reply - SQLite's ability to guarantee Database ACID requirements has very little to do with whether the file is physically on a Network storage or not (or a SAN for that matter), but rather depends on the process being fully in control of the locking mechanism for the DB file. With WAL this is achieved as Warren describes, and for other DB journal types it depends on the locking mechanism of the OS.
So the most correct assessment would be: As long as All read/write control of DB connector processes originate from the same machine+OS, or rather, the same OS process space in memory (i.e. not different VM's that are merely on the same hardware), then you should not have any problem.
(7) By anonymous on 2021-09-09 15:29:47 in reply to 4 [link] [source]
Thanks Ryan, appreciate your explanation!