fosstodon.org is one of the many independent Mastodon servers you can use to participate in the fediverse.
Fosstodon is an invite only Mastodon instance that is open to those who are interested in technology; particularly free & open source software. If you wish to join, contact us for an invite.

Administered by:

Server stats:

11K
active users

bert hubert 🇺🇦🇪🇺

"The second advantage of WAL-mode is that writers do not block readers and readers to do not block writers. This is mostly true" - coming to you live from the part where this is not true. sqlite.org/wal.html

sqlite.orgWrite-Ahead Logging

@bert_hubert also not entirely true in my experience. I guess it might depend on whether the writer accesses the same table as readers or not.

In Signal Desktop (since recently) we have a pool of threads with one writer thread and multiple readers, and it seems mostly optimal, but still writing blocks reads from time to time.

@bert_hubert even with the different tables I sometimes think that creating a separate database might be more performant. Need to do some benchmarks to quantify that, though.

@indutny would probably work well - for my setup I have 3 separate sqlite databases already and it is a big win, they don't even see each other. And you can still ATTACH them together if you need to. I other places I've also sharded data over arrays of databases - they are all free!

@indutny @bert_hubert yeah can confirm, we also went with a multi-threaded approach in the end and it’s still not ideal.

@indutny trying to get a handle on it, it mostly seems related to opening and closing databases. Or running a pretty old sqlite (in Debian). Probably going to vendor a newer release. At least this makes it easier to complain on the (very responsive) forum!

@bert_hubert if you actually need that non-blocking behavior you should use SQLite backed by LMDB. Sqlightning or lumosql.

@hyc @bert_hubert mmmm this makes me wonder if I can coerce Elixir/Ecto to use an LMDB-backed SQLite ...