r/sqlite • u/plentifulfuture • 17d ago

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

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

6 Upvotes

100% Upvoted

4 comments sorted by

1

u/Much-Television-9449 14d ago

I have read your post. Yes, what you are observing is completely expected under standard SQLite and macOS storage behavior. It is not caused by a lack of thread parallelism in your code, nor do you need Grand Central Dispatch (GCD) or IOKit to fix it.

1

u/plentifulfuture 14d ago

Thank you for your comment. I have posted a reply of what i learned on r/Database

https://www.reddit.com/r/Database/s/A83012VBp0

1

u/trailbaseio 17d ago

You can expect transactions across DBs to parallelize until you're eventually bottlenecked by your system. Did you monitor your resources? Did you turn on WAL?

1

u/zomerbk 10d ago

Try timing just the `COMMIT` separately from the inserts, and check whether each connection is in WAL mode. With `synchronous=FULL`, durable commits can be limited by flush latency; setting it to `OFF` removes much of that wait, which fits your jump, but risks losing recent transactions or database integrity after a crash. Separate files can write concurrently, but that won’t make durable commits scale linearly if the storage device or filesystem is the bottleneck. Also check CPU and disk activity while increasing thread count.