Ecto_sqlite3 - an Ecto3 SQLite3 adapter

@wanton7 and @tj0 I recent went through and fixed the memory leak issue, or rather the perceived leak issue.

I could not reproduce the leak, but, after reading through mailing lists and what not, the issue seemed to stem from the garbage collector not destructing the prepared statements in a timely manner. So instead, when ecto closes the connection we handle it by also releasing the underlying resource (the prepared statement) and then the destructor will run later and free the last remaining bytes that was a pointer to the prepared statement.

Overall this should suffice and would love to see if anyone who was having issues with memory growing like crazy during high load to give the latest release a try.

https://github.com/elixir-sqlite/exqlite

I think they are reference counted. From here Are NIF resources meant to be destroyed immediately on GC?

Sverker Eriksson
I fully agree with Jesper about not relying on GC for resource reclamation. An additional risk is that the resource does not get GC’ed at all if its reference has “leaked” away onto the heap of another process, like for example a logger or an io-server process.

So something could be keeping those references around.

The issue appears to be that at certain times the memory seems to be allocated outside of the processes. This means the GC won’t work. To get around that, a destructor was added and called, but since destructors are queued…I’m not really sure they are going to access the memory space they were supposed to free. Not an expert though.

I think for those cases like process crash or kill just relying on GC should be fine because it’s not a primary resource reclamation path.

Yep we reuse the same pool of connections. I mean in the case the process crashes or is killed - from reading above it seems that because in that case we rely on NIF destructor behavior, memory may stay around for a while waiting to be pruned. If we could somehow detect the crash and clean up more aggressively, that would be an improvement - but I’m not sure how to do that. In terms of a connection being cleaned up in a non-crashing manner, that should work as expected and clean the memory more immediately as we are explicitly calling the sqlite3 close function.

One of the benefits of my library, if it ever gets to a working state, is that the Rust crate it steps on maintains a small pool of prepared statements (size configurable) and aggressively prunes them in a LRU fashion.

I believe you guys can do the same with the poolboy library. It’s super easy to use.

Are SQLite connections being closed? I’ve used SQLite in the past and my understanding is that you can just keep using same connections over and over.

Well there are two possible things we could do:

1. “Manually” clean up prepared statements ourselves after executing and reading the results from a query. We currently do not do this, and it seems like it should be easy enough. Filed this issue for that.
2. Improve how we clean up the database connection resource. This will be much harder. I don’t have a clear idea on how this would be done.

I’m planning to use SQLite for my next Elixir project so I googled a bit. Found this thread that might be of interest.
From 2020 titled Are NIF resources meant to be destroyed immediately on GC?

Quote from there

Jesper Louis Andersen
As a general rule: never use finalizers for the primary resource
reclamation path. You don’t know when they’ll run. You don’t know what
changes are made to a GC in the future, that alters the rule set slightly.
Use them as a safety measure if something goes out of scope. Resources such
as DB connections can be very limited in scope (less than 50 for a typical
non pg_bouncer postgres setup). So you have to manage them quite
explicitly. Memory is far more abundant, and it is also fungible to a large
extent, so you just need a block somewhere.

It seems that starting from OTP 22.0 destructions are scheduled/queued and won’t happen immediately.

If it’s called in a delayed manner, I have no idea how it would de-allocate correctly. The information about the address of where the memory was alloc’d is now gone, right?