3 ms·

Previously, I had always used Postgres for database and Rust or NodeJS for my backend. For my new website (https://limereader.com/ https://limereader.com/), I u

by busymom0 11mo ago

Previously, I had always used Postgres for database and Rust or NodeJS for my backend. For my new website (https://limereader.com/ https://limereader.com/), I used Swift for my backend, SQLite for Database, Vapor for web server in the Swift app and am self-hosting the site on an old Mac mini. A sqlite related issue I ran into had to do with accessing the SQLite database from multiple threads. Found out a solution easily: for multi-threading use, SQLite needed to be initialized with a `SQLITE_OPEN_FULLMUTEX` flag. Since then, the website has been running seamlessly for about 3 weeks now.

maxmcd 11mo ago
This will block threads while waiting for other threads to write. That might work great for your threading model but I usually end up putting the writer in one thread and then other threads send writes to the writer thread.
- busymom0 11mo ago
  I do open 2 connections: First one for writing with flags: SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX Second one for reading with flags: SQLITE_OPEN_READONLY | SQLITE_OPEN_FULLMUTEX As you can note, I have SQLITE_OPEN_FULLMUTEX on both of them. Should I only have it for the writing one?
  - maxmcd 11mo ago
    Oh nice, yes I think your threads should be able to perform reads concurrently when the write lock is not held. Would make sure you are in WAL mode as well, since I think that will improve your concurrency.
andersmurphy 11mo ago
You don't need fullmutex if you manage your connections correctly at the application level. I.e ensure each connection is only used from a single thread at a time. I also highly recommend having an MPSC queue for your batch/writes and make them go through a single connection so you don't have to deal with SQLITE_BUSY or SQLITE_LOCKED.
wmanley 11mo ago
Use a connection per-thread instead. By sharing a connection across threads you’ll be limiting concurrency - and transactions won’t work as you’d expect. SQLite connections are not heavy. Also: use WAL mode and enable mmap.

maxmcd 11mo ago

This will block threads while waiting for other threads to write. That might work great for your threading model but I usually end up putting the writer in one thread and then other threads send writes to the writer thread.

busymom0 11mo ago
I do open 2 connections: First one for writing with flags: SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX Second one for reading with flags: SQLITE_OPEN_READONLY | SQLITE_OPEN_FULLMUTEX As you can note, I have SQLITE_OPEN_FULLMUTEX on both of them. Should I only have it for the writing one?
- maxmcd 11mo ago
  Oh nice, yes I think your threads should be able to perform reads concurrently when the write lock is not held. Would make sure you are in WAL mode as well, since I think that will improve your concurrency.

andersmurphy 11mo ago

You don't need fullmutex if you manage your connections correctly at the application level. I.e ensure each connection is only used from a single thread at a time. I also highly recommend having an MPSC queue for your batch/writes and make them go through a single connection so you don't have to deal with SQLITE_BUSY or SQLITE_LOCKED.

wmanley 11mo ago

Use a connection per-thread instead. By sharing a connection across threads you’ll be limiting concurrency - and transactions won’t work as you’d expect. SQLite connections are not heavy. Also: use WAL mode and enable mmap.