3 ms·
Yeah, it's explained in the code[1] SQLite only allows one writer at a time, so concurrent writes will fail with a "database is locked" (SQLITE_BUSY) error. T
by nalgeon 3y ago
Yeah, it's explained in the code[1]
SQLite only allows one writer at a time, so concurrent writes will fail with a "database is locked" (SQLITE_BUSY) error.
There are two ways to enforce the single writer rule:
1. Use a mutex for write operations.
2. Set the maximum number of DB connections to 1.
Intuitively, the mutex approach seems better, because it does not limit the number of concurrent read operations. The benchmarks show the following results:
- GET: 2% better rps and 25% better p50 response time with mutex
- SET: 2% better rps and 60% worse p50 response time with mutex
Due to the significant p50 response time mutex penalty for SET, I've decided to use the max connections approach for now.
[1]: https://github.com/nalgeon/redka/blob/main/internal/sqlx/db.go#L78 https://github.com/nalgeon/redka/blob/main/internal/sqlx/db....
- nasretdinov 3y agoHow about having two pools, one for writes only, and the other one for reads? SQLite allows you to open the DB more than in one thread per application, so you can have a read pool and a write pool with SetMaxConnections(1) for better performance. This of course also means that reads should be handled separately from writes in the API layer too.
- nalgeon 3y agoThought about it, decided to start with simpler and good enough option. The goal here is not to beat Redis anyway.
- acrispino 3y agofor what it's worth, the two pool approach is suggested here by a collaborator to github.com/mattn/go-sqlite3: https://github.com/mattn/go-sqlite3/issues/1179#issuecomment-1638083995 https://github.com/mattn/go-sqlite3/issues/1179#issuecomment...
- nasretdinov 3y agoWell I agree, that's a good starting point. You probably won't be able to beat Redis with SQLite anyway :), although given that WAL mode allows for concurrent reads it might give it a large enough performance boost to match Redis in terms of QPS if the concurrency is high enough.
- Sytten 3y agoThis is really not true in WAL mode with synchronous NORMAL, this was only true with the default journal mode and a lot of people are misusing sqlite because of that. You still have one writer at a time but you wont get the SQLITE_BUSY error. You can check the documentation [1], only some rare edge cases return this error in WAL. We abuse our sqlite and I never saw it happen with a WAL db. [1] https://www.sqlite.org/wal.html#sometimes_queries_return_sqlite_busy_in_wal_mode https://www.sqlite.org/wal.html#sometimes_queries_return_sql...
- kiitos 3y ago> The benchmarks show the following results Where are the benchmarks?
- avinassh 3y agoHave you tried setting BUSY_TIMEOUT and benched it? I believe it would be better too. https://www.sqlite.org/pragma.html#pragma_busy_timeout https://www.sqlite.org/pragma.html#pragma_busy_timeout