I looked at this months ago and thought to myself finally I’m totally going to use this in production one day since I have a ton of read-only data that never gets updated.
And the day is coming pretty soon. I just ported over typeahead locations from postgres and FTS5 is working pretty well and ecto is doing its thing. Before flipping the switch, I wanted to understand the limitations from a system level.
I started reading sqlite open docs and locking and memory, but everyone in the thread seems pretty knowledgeable, so I thought to ask what is actually happening under the covers?
What I’ve deduced so far is the following:
-
The sqlite adapter does an fopen to the database. This will return a new fd. This fd will be used by the each of the processes of the Repo pool of which there are 5 by default. I’m just not sure if it’s a single fd for the BEAM or an fd for each process of the pool.
-
It looks like locking is set to “NORMAL” which means that the database is unlocked after every transaction. This would mean that each of the 5 repo processes can only do queries sequentially on that single filehandle. Of course, if there are 5 different fds, I still don’t quite understand the locking. It looks like a SHARED lock is an internal tracking state for sqlite, but it seems like opening a read-only copy seems to be possible with sqlite_open_v2 and sqlite shared-cache which they have subsequently said not to use .
-
BEAM should know nothing about the memory management of this since it only knows about the fd and sqlite manages its memory dynamically. But since we’ve opened up the FD inside the BEAM process, the memory should be allocated to the BEAM process? I just couldn’t find any major changes in the resident memory or via observer, but it could just be my database isn’t big enough. Linux should handle all the page allocations and what not, so nothing fancy is needed.
-
Reading further, it does appear that there is a new FD for each Repo pool process? At least that’s what it appears to be happening from Ecto.Adapters.SQL.Connection — Ecto SQL v3.14.0? So just a bit confused as to how the memory is managed here. Does each sqlite open put it’s memory on the data / stack of the elixir pid?
Considering that I’m only looking to use this for readonly data, it seems to be pragmatic to open up a new fd per elixir pid and open with a shared lock across all of them.
So instead of writing all of that, I should have probably just asked:
- Does each elixir pid have it’s own fd? If so, how is memory and locking managed for a read-only workload? Are we dynamically loading the sqlite index into the memory of each pid?
- If each elixir pid doesn’t have it’s own fd, how would we stretch the read-only performance?
Help?
