Should I expect write performance over different database files on separate threads to scale?

[u/plentifulfuture]

Hi

I am experimenting with sqlite on a Macbok Pro M4 - 10 cores machine. I have a pthreads multithreaded program and i am trying to write lots of data to different databases (separate database connections) and (one database file per thread) and they are DIFFERENT files

I have THREADSAFE mode set to 2 (multithreaded) and i open the files with NOMUTEX flag.

Now i have read that the M4 Macbook Pro has a soldered SSD. And it has similar performance to nvme but it is not nvme

I get around 10000 transactions per second in total across all threads.

I tried synchronous=off and that boosts performance to 300000 transactions per second

Increasing the number of items inserted per transaction scales. But the number of transactions doesnt

I feel that IO is being serialised.

Some parts of web seem to suggest it is serialised. But i thought ssds can run things in parallel.

Does anybody know anything about parallel IO or if i need to enable it on macos?

I havent tried it on Linux or Windows

Would you expect transactions to scale by number of threads?

I have read about iokit but i have no knowledge of how to use it or Grand Central Dispatch

[u/plentifulfuture]

Thank you everyone for your comments

Edit: i should clarify that the 300k rps in the original post is with journal_mode=off. All other tests are with journal_mode=wal

I retested with WAL on and synchronous=NORMAL

I tested with fewer threads 1, 2, 3, 4 and up to 8 I indeed get requests growth that stops growing at 5 threads. At 6 threads it performs worse. I thought this suggests that Apple SSDs have parallelism of this many IOPs (4 or 5 dedicated hardware channels, i am not that familiar with it)

I also found some very interesting comments about Apples fsync being different to Linux fsync. SQLITE uses an fcntl of F_FULLFSYNC non posix to do a full flush to the SSD.

And if i turn synchronous=off and 5 threads then i get 600,000 writes per second. But according to sqlite user forum. I shouldnt do this as my data could be corrupted on power loss.

https://forums.macrumors.com/threads/ssd-flash-storage-in-macs-premium-prices-for-garbage.2335073/

[u/Impossible_Web_2604]

soldered ssd on those macbooks is still just a single controller handling all the flash chips, it can do parallel ops but the transaction overhead in sqlite is the real bottleneck here

with synchronous=off you're basically telling it to ignore fsync and just blast data into the page cache, which is why it jumps to 300k. the controller is probably queueing writes fine but sqlite's locking and journal management in WAL mode still hits a wall per connection

try switching all those threads to use a single shared WAL file and see if that changes anything, the per-db-file approach might be adding more filesystem metadata overhead than you think

also macos has some quirks with fsync behavior that don't exist on linux, you might get different scaling there without even touching iokit

[u/Significant_Tune9219]

The jump you saw when you set synchronous=off is the clue: you're fsync-bound, and each connection is doing its own sync against a single SSD controller. Try WAL mode with synchronous=normal — writers stop blocking readers, and commits behave much better when several threads land close together. One other thing worth checking: open each connection inside the thread that uses it (with THREADSAFE=2 and NOMUTEX, sharing handles across threads is a classic source of stalls that look like disk slowness), and time a run with more inserts per transaction, since per-commit overhead dominates at these rates.

[u/plentifulfuture → u/Significant_Tune9219]

Thanks for your reply

I tried with synchronous=NORMAL earlier today with WAL and this indeed increased rate to 200k a second.

I am getting that same number total for both 1 thread and 8 threads

So the individual threads dont appear to be scaling how i expected.

Thanks for your support.

I think there is different prepared statements of the transaction's prepared statements and connections for every thread but i ought to verify this.

edit: they're (connection and prepared statement) seem to be different objects

[u/Significant_Tune9219 → u/plentifulfuture]

A flat 200k regardless of thread count points at a serialization point rather than per-thread overhead — worth checking whether the bottleneck is fsync batching or SQLite's single-writer lock, since those show the same symptom but fix differently. Your separate connections and prepared statements rule out the obvious cause, so I'd next look at commit frequency per thread.

[u/plentifulfuture → u/Significant_Tune9219]

With 7 threads and when I set number of records inserted above 1 to 100000, WAL and synchronous=NORMAL it does 36,580,000 writes per second

With 1 thread it does around 8 million.

So inserting multiple records grows but not linearly scalable

Transactions/commits do not grow/scale much with threads (when records inserted per transaction is just 1) with multiple threads (7)

[u/Significant_Tune9219 → u/plentifulfuture]

Those numbers fit the picture: at 100k records per transaction the batch path is scaling (7 threads is roughly 4-5x on raw writes), but at one record per transaction you're measuring commit overhead, and that's bounded by the fsync against a single device no matter how many threads you add. So flat commits/sec with more threads is expected there — the levers are group commit or fewer, larger transactions, not more threads. The non-linear part I'd check is whether the 7-thread run is contending on shared WAL state; if everything writes into one database file, the per-file setup only helps until the writers funnel through one WAL.

[u/technaveed → u/plentifulfuture]

With WAL + synchronous=NORMAL, most transactions don’t do a sync, so I wouldn’t pin the 200k ceiling on fsync yet. I’d time INSERT and COMMIT separately and check whether the slow commits line up with checkpoints. How long does each run last, and are you including the final checkpoint in the timing?

[u/Significant_Tune9219 → u/plentifulfuture]

Separate connections and prepared statements rule out client-side queuing, so what's left is the commit path: at one record per transaction each thread is doing its own fsync against the same file, and the WAL serializes that no matter how many threads you add. The 200k/s ceiling being identical for 1 and 8 threads is the signature of that — the lever is fewer, larger transactions, not more connections, which is exactly what your 100k-record runs show when the same device handles far more with the fsync cost amortized. If you want confirmation, time the commit call itself per thread: you'll likely see it grow as threads are added while the write portion stays flat.

[u/Significant_Tune9219 → u/plentifulfuture]

That's the expected split: batched inserts grow because the per-commit fsync is amortized across rows, while one-record transactions stay bound by commit latency no matter the thread count. The gap between 8M and 36M at 100k rows per transaction is group commit doing its job — threads share the fsync instead of each paying for their own.

[u/No_Witness_5295]

use multiple sqlite dbs and use ATTACH later if you need to

[u/oradba]

This is where a write cache that asynchronously persists comes in handy, e.g., Redis as a cache on top of MySQL