More benchmarks

[rhashimoto]

I implemented the benchmarks from this SQLite page. The page is old (SQLite 2.7.6 is from 2003) but the benchmark operations themselves are still interesting. The benchmarks can be found at demo/benchmarks/, and are online here.

The benchmark page runs the same 16 tests with different builds and VFS classes. The combinations can be specified by modifying the query parameter values on the page URL. The link above includes these configurations:

• IDBBatchAtomicVFS on the Asyncify build.
• AccessHandlePoolVFS on the default build.
• OPFSCoopSyncVFS on the default build.
• OPFSAdaptiveVFS on the Asyncify build.
• OPFSPermutedVFS on the Asyncify build.

I changed one benchmark from the SQLite page. The original Test 7 is not wrapped in a transaction and I added one. I did this because Test 7 should be compared with Test 4 - SELECT with/without an index - and Test 4 uses a single transaction.

To run the benchmarks, first change the preamble SQL to set up any options, e.g. type of journaling, locking, etc., then click the Start button. You may need to be patient (especially with Test 1); completing the table can take a couple minutes or more.

Note that artificial benchmarks like this one are typically poor predictors of application performance because an application workload will rarely (if ever) be anything like this. In addition, this benchmark run as-is with the same preamble can't be fair because different VFS classes need different PRAGMA settings for best results. It is a tool that can be both informative and misleading, and needs to be applied and interpreted with some care.

[fabiospampinato]

Interesting results 🤔 To my understanding the OPFS is meant to be an high-performance interface for storing data, how is it possible that in the benchmark inserts are 1000x slower than when storing the database in memory?

[rhashimoto]

It's strange though because if you run the same test with better-sqlite3, which can also either keep everything in memory or flush changes to disk, I don't think one would see a 1000x difference in performance 🤔 Like that's a drastic difference. 40+ seconds for 1000 inserts seems absurdly slow.

I'm not familiar with better-sqlite3 but it looks to be a nodejs-only package for SQLite. The node environment is different from the browser environment, especially when it comes to using native code and APIs. In node, a package would be able to connect to a native SQLite library and a VFS that makes calls to native OS APIs. In the browser, we have WASM (sometimes Asyncify-ed WASM) and only browser APIs.

OPFS access handles are pretty fast at raw I/O, but they have some drawbacks, particularly for write transactions. One drawback is that currently they are exclusive - only one access handle can be open on a file - so for any VFS that supports multiple simultaneous connections (e.g. OriginPrivateFileSystemVFS), an access handle is typically opened and closed for each transaction, which usually translates to OS open() and close() calls. Another drawback is that OPFS access handles don't provide certain guarantees so under the default settings SQLite will perform effectively 4 flushes for every write transaction, while better-sqlite can probably get down to 1 flush in WAL journaling mode.

OPFS access handles are especially slow on Chrome on Macs, because the flush implementation there is extra conservative (i.e. slow). Having a super slow flush and doing 4 of them is not a recipe for performance.

Maybe someone out there knows how to avoid these speed bumps. I personally don't see it happening until multiple access handles can operate on a file simultaneously (proposal).

[AntonOfTheWoods]

Yeah, I think the old adage of not trusting benchmarks too much is also pretty valid here. I have found for all the real uses that I have put AHP through, it's just super fast. Also, there are typically ways of structuring your code processes that mean, for example, you can get around doing 1000 individual insert transactions in a row... and just batch them in a single transaction. IDB is almost 2 orders of magnitude slower than AHP for certain queries I have, unless you look at the indexes and can force better query plans, where the difference then becomes negligible (+40% on a basis of very fast).. It's nice to not have to bother with optimisations but I didn't find anything that ended up being difficult to make super fast!

[rhashimoto]

not sure if you can tolerate synchronous = 0; for your use case but it speed things up 10x for OPFS for test case 1 on my hardware

It was the best of ideas, it was the worst of ideas 😀

So yes, in certain situations this could work. Say if you always PRAGMA integrity_check your database on page start and discard it and start over if it fails.

But no, I would never recommend that anyone do this. Note that only using the database for a single browser session isn't safe because a session can be interrupted and resumed.

As much as this might look like the same sort of tradeoff as relaxing durability - exchanging some guarantees for performance - it is not at all the same thing. Setting synchronous=0 will eventually corrupt your database.

@tantaman knows all this and I upvoted his post. But afterwards I thought it should be made clear that this is not something you try and see what happens. If you don't know what to do when SQLite tells you your database is corrupted then you shouldn't even consider it.

[fabiospampinato]

To add some numbers for context, in better-sqlite3@latest:

• 1000 inserts in memory takes ~1.5ms on my computer.
• 1000 inserts in memory, but inside a single transaction, takes ~1ms.
• 1000 inserts on disk takes ~220ms 😨
• 1000 inserts on disk, but inside a single transaction, takes ~1.5ms.
• 1000 inserts on disk, but under WAL mode, takes ~12ms.
• 1000 inserts on disk, but inside a single transaction and under WAL mode, takes ~1ms. 🔥

So I guess the main takeaways are:

• we would need a way to use WAL mode to get into serious performance territory.
• transactions can make a huge difference for inserts, especially outside of WAL mode, kinda obviously I guess.

[rhashimoto]

Getting the access handle upgrades to implement WAL would certainly be an improvement, but that's not the only thing that makes browsers slower. WASM will never be as fast as native code anyway, and there are also significant performance hits in bridging synchronous calls to asynchronous APIs (JSPI would help) and the Chromium flush on Apple devices.