SQLite Forum

WAL mode speed vs normal comparison
Login
>  We're not defining deep storage, OS, FS, versions, etc., here

In that case, we can create a table of values for you which is clear, simple, and wrong.

All items on that list can affect the results significantly, and three of the four can affect the results by an order of magnitude or more:

1. **storage**: This is a complex field all by itself — lone HDD vs RAID vs LVM vs SAN vs SecureDigital vs [m.2][1]... — but even that aside, there are *single aspects* of storage that can give an effect orders of magnitude in difference, such as rotating vs solid state: 7200 RPM divided by 60 seconds per minute divided by 2 rotations per transaction yields 60 transactions per second, *absolute max,* hard-limited by physics, yet a lone modern SSD may achieve 10000 TPS, and a RAID of them may achieve a million TPS.

    You may then object, "But doesn't all of that net out to zero if the test is done on the same storage medium?" Answer: No! Why? Because the factor "2 rotations per transaction" assumes a particular fsync behavior and access pattern in the benchmark. Change those parameters and now you get a *different* physics-based limitation for each concurrency mode.

2. **OS**: Bare-bones Linux vs. out-of-the-box Windows probably gives the advantage significantly to Linux, simply because Windows ships with antivirus active while Linux does not. And if we're talking about consumer Windows as shipped from a given PC manufacturer, or about Windows as preconfigured by site IT, then we may be talking about [orders of magnitude of slowdown][2].

    And once again you may object that this should net out to zero, but it is within the scope of antimalware software to treat a collection of three files on disk (DB, WAL, lockfile) differently than a single file (old-style single SQLite DB file.)

3. **FS**: If that didn't affect anything, then why do the bars flip-flop [in this benchmark][3]? That has a direct effect on this wish of yours, since it means the way you construct the benchmark affects which FS has the advantage. I can also tell you from experience that the *duration* of the benchmark can affect things, with performance dropping off at a nonlinear rate the longer the benchmark runs due to RAM exhaustion, O(log<sup>2</sup>) data structure depth, etc. 

    And again, all of that affects the results you get in this simple table since a collection of 3 files with a different fsync pattern can easily give different runtime performance based on the FS used. ZFS, for example, treats fsync considerably differently than ext4.

4. **versions**: I guess you mean SQLite versions. Yet if that has no effect on things, then why are we talking about WAL vs non-WAL here at all? Shall we compare SQLite 3.0.0 non-WAL with SQLite 3.31.1 WAL?

If you want a constructive answer to this wish, then the best I can come up with is to publish a *family* of results so that there is at least some hope that the reader can interpolate a plausible value for their local access pattern.

I'm suggesting hundreds of tests here, arranged on a multidimensional axis. It'd be a fair bit of work just constructing the data browser for the result set.

We await your results. :)

[1]: https://en.wikipedia.org/wiki/M.2
[2]: https://sqlite.org/forum/forumpost/3fc74eda11
[3]: https://www.phoronix.com/scan.php?page=article&item=ubuntu1910-ext4-zfs&num=2