Strategies for higher write throughput.

[gnat]

Sharing a few strategies here. Can be converted to a discussion at your discretion.

• Enabling WAL2: Any plans for WAL2 / BEGIN CONCURRENT? #30
• Write batching / bulk INSERT
  • Used by rqlite (sacrifices durability for throughput).
  • Not all writes may be suitable for batching.
• Multiple databases for writes, then ATTACH for reads.

# main.yaml
scheduledTasks:
  - atStartup: true
    statements:
      - ATTACH 'sessions.db' as sessions

You can then:

SELECT count(*) FROM sessions.users;

Perform JOIN, etc.

Multiple databases enable you to multiply your throughput.

Share your strategies!

[proofrock]

Hi, and thanks for taking time to comment. I converted this into a discussion.

Good points! What's the use case, though? Admittedly I didn't give yet much toughts about performances, much can be done but the relevant points are:

• concurrency at the db level (so, as you say, WAL2)

• transactions and requests are 1:1, I mean a transaction doesn't span more than one request currently. This is by design, as it would make it difficult to keep things smooth if e.g. someone forgets to close the transaction, it would need to implement a sort of timeout ecc. This is also the reason for which BEGIN, COMMIT and ROLLBACK are forbidden, even if the implementation is quite simple and can be bypassed easily.

• a per-db lock serializes access to the database(s), so currently it enforces one-request-at-a-time. This is also a safety measure, maybe a bit extreme, but I tried to remove it and many snakes reared their heads, SQL_BUSY errors etc.

The point is: if you absolutely know what you're doing and how the web service parts interacts with the embedded db part, you could manage to do things reliably; but if you "see" only the web part and use it as you would expect to be able to use it, treating the rest as a black box, I feel these things are required.

This said, I am asking what is the use case because it may be possible to do something towards a few cases. Your workaround is very clever and should be effective; "batch" inserts are already present if you don't need to span beyond a single request; maybe a sort of "CSV import" mode could be put in place. Or an "expert" mode that relaxes the per-db lock and/or allows WAL2 and/or remove the check for BEGIN/COMMIT/ROLLBACK, if you feel you know what you're doing.

But yes, if you have many inserts in many requests, you may find that the bottleneck is the web server or the lock, not the sqlite stuff. So first let's frame the problem at hand.

Thanks!

 G.

PS: for sake of completeness, since I just wrote it on Discord and it's maybe relevant: performances were not a key point so far; the sqlite layer is a transpilation to Go and it's not the most performant, but allows to cross-compile to many platforms. I benchmarked native sqlite solutions in Rust and Java, but they were (strangely) slower than this, so for now Go it is. In the future it will be interesting to understand what can be done, and this discussion is a great starting point.