VFS benchmarks (including the official SQLite WASM OPFS VFS)

[rhashimoto]

I have been measuring the performance of AccessHandlePoolVFS against other VFS classes - some from wa-sqlite and also the offical SQLite WASM OPFS VFS (OSW henceforth). The SQL for the benchmarks can be found in the .sql files here, and they are based on an old SQLite benchmarks page I originally discussed here.

Here are timings (in seconds) on my 2014 Mac mini and its SSD using Chrome 112:

I ran the benchmarks three times for OSW OPFS (shown in three columns) just to provide a feel for the variance in the timings.

My initial attempts at timing OSW had it running faster than AccessHandlePoolVFS, which didn't make sense to me. A little digging uncovered a dangerous OSW synchronization bug (fixed immediately after I reported it) which wasn't waiting for the journal to be written before modifying the database. If you want to play with OSW yourself make sure to get a version with the fix.

I wouldn't regard these numbers as absolute truth - it's an artificial workload on one particular browser and machine, implemented by a fallible developer - but here are some of my notes:

• The setup SQL shown attempts to provide the same conditions for all the VFS implementations. AccessHandlePoolVFS doesn't support multiple connections, so it makes sense to let all implementations optimize for a single connection with locking_mode=exclusive (it's still possible to allow multiple contexts to share a single connection at the application level). Page size and cache largely determines the number of calls made to the VFS. The database file size for this example is just less than 8 MB, so that is where I set the cache size. IDBBatchAtomicVFS also uses the cache for its journal so it might be disadvantaged by this, but I considered that more fair than setting the cache size higher than the other implementations could actually use.

• AccessHandlePoolVFS beats OriginPrivateFileSystemVFS on every benchmark except one which I consider a tie. OriginPrivateFileSystemVFS does allow concurrent reads (the other OPFS implementations do not), so that's an advantage not shown here, but AccessHandlePoolVFS wins the read benchmarks (Test 4, 5, and 7) by 2-4x so that's the amount of concurrency needed to break even. I think the circumstances where OriginPrivateFileSystemVFS could be the best choice are going to be pretty rare, so I'm not going to discuss it further here (and I'm not planning to update it going forward).

• AccessHandlePoolVFS is about even with OSW on some benchmarks and a bit faster on others. It seems to do the best on the benchmarks with the most statements (Test 2, 3, 7, 9, and 10) whether reading or writing. If the benchmarks are set up correctly, both implementations should be executing exactly the same VFS calls, so the expected difference is that OSW proxies some calls over SharedArrayBuffer and AccessHandlePoolVFS does not. OSW's own analysis estimates that overhead at 40% (obviously on a different machine than mine), which is roughly in the same ballpark. I'm not fully confident in that as the explanation, though, because the benchmarks with the most VFS calls (and thus the most per-call overhead) aren't necessarily the ones with the most SQL statements. It might be interesting to count the number of calls to each method to see whether that correlation holds up.

• IDBBatchAtomicVFS dominates Test 1, which emphasizes write transaction costs. The likely reason for this has to do with how SQLite exploits filesystem guarantees and is explained here. It gets even better - another order of magnitude better - in relaxed durability mode (which still preserves atomicity and consistency), also discussed earlier. IDBBatchAtomicVFS loses all the other benchmarks to both AccessHandlePoolVFS and OSW except Test 16. IDBBatchAtomicVFS does support concurrent reads, but it has a very similar breakeven point against AccessHandlePoolVFS as OriginPrivateFileSystemVFS.

[sgbeal]

Re. the OSW timing variance: try the same thing on the OSW with the dev tools open and you may see the times triple (or more) even though no console output is generated. Why that happens is unclear but it's reliably observable in Chrome.

[rhashimoto]

Yes, I'm aware of that; I never do timings with any dev tools open. The variance is similar for all the VFS implementations; it was just easiest to show in the screen capture with OSW.

[rhashimoto]

It might be interesting to count the number of calls to each method to see whether that correlation holds up.

It is interesting! Here are the method call counts:

AccessHandlePoolVFS

	xOpen	xClose	xRead	xWrite	xTruncate	xSync	xFileSize	xDevChar	xAccess	xDelete
Preamble	3	2	5	3	0	3	4	9	6	1
Test 1	1	0	1001	6038	1001	4004	0	3004	0	0
Test 2	0	0	1	173	1	4	0	3	0	0
Test 3	0	0	1	337	1	4	0	3	0	0
Test 4	0	0	0	0	0	0	0	0	0	0
Test 5	0	0	0	0	0	0	0	0	0	0
Test 6	0	0	2	78	2	8	0	6	0	0
Test 7	0	0	0	0	0	0	0	0	0	0
Test 8	0	0	1	39	1	4	0	3	0	0
Test 9	0	0	1	825	1	4	0	3	0	0
Test 10	0	0	1	727	1	4	0	3	0	0
Test 11	0	0	1	721	1	4	0	3	0	0
Test 12	0	0	1	2046	1	4	0	3	0	0
Test 13	0	0	1	532	1	4	0	3	0	0
Test 14	0	0	1	444	1	4	0	3	0	0
Test 15	0	0	1	331	1	4	0	3	0	0
Test 16	0	0	3	48	3	12	0	9	0	0

OSW

	xOpen	xClose	xRead	xWrite	xTruncate	xSync	xFileSize	xDevChar	xAccess	xDelete
Preamble	2	0	4	4	0	4	3	7	2	0
Test 1	0	0	1001	6038	1001	4004	0	2002	0	0
Test 2	0	0	1	173	1	4	0	2	0	0
Test 3	0	0	1	337	1	4	0	2	0	0
Test 4	0	0	0	0	0	0	0	0	0	0
Test 5	0	0	0	0	0	0	0	0	0	0
Test 6	0	0	2	78	2	8	0	4	0	0
Test 7	0	0	0	0	0	0	0	0	0	0
Test 8	0	0	1	39	1	4	0	2	0	0
Test 9	0	0	1	825	1	4	0	2	0	0
Test 10	0	0	1	727	1	4	0	2	0	0
Test 11	0	0	1	721	1	4	0	2	0	0
Test 12	0	0	1	2046	1	4	0	2	0	0
Test 13	0	0	1	532	1	4	0	2	0	0
Test 14	0	0	1	444	1	4	0	2	0	0
Test 15	0	0	1	331	1	4	0	2	0	0
Test 16	0	0	3	48	3	12	0	6	0	0

My notes:

• The counts are mostly the same, but not identical. The two implementations do somewhat different things on startup and in the preamble, and AccessHandlePoolVFS appears to call xDeviceCharacteristics one additional time per write transaction. I don't think these differences affect the analysis much, but it is curious why they exist. The underlying SQLite C source amalgamations are at slightly different versions, so that could be a cause, or it could be differences in build flags, etc.
• No VFS methods are called for Test 4, 5, and 7 (I was initially surprised, but obvious in hindsight). The timings for Test 4 and 5 look pretty even, but there does appear to be a timing difference for Test 7. This may show a per-statement performance advantage for wa-sqlite over OSW not related to VFS. If true, this would also contribute to timings for Test 2, 3, 9 and 10, which are statement-heavy. Note that this does not necessarily indicate a performance defect in OSW; it could just as easily be a bug in wa-sqlite or how the benchmark invokes OSW (my initial suspect). This deserves further investigation.
• I didn't expect xRead to be called at all in the tests since the database fits in the cache. These calls turn out to be reads of the journal file, most likely at the start of a write transaction to make sure there is no "hot" journal. It's possible for SQLite to skip this check under an exclusive lock when the previous transaction succeeded but I guess it doesn't. In any case, it should be noted that these runs are useless for measuring read performance because they don't do any real reads. The cache size would need to be reduced for that.
• You can see that xSync is called four times for every write transaction, which is good because that matches the expectation. The journal file is synced twice, the database file is synced once, and finally the journal file is truncated and synced again.
• Test 12 looks to be the best to examine the per-call costs between the implementations. It is one statement, so also one transaction, and produces 2000+ VFS method calls.