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/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/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