Running into a memory issue that doesn't happen with `exqlite`

[tcoopman]

I've been trying to debug a memory issue and I think I've been able to pin it down to that it has something to do with ecto_sqlite3 but not with exqlite.

I've posted my journey here: https://elixirforum.com/t/measuring-memory-consumption-how-to-figure-out-what-triggers-jumps-in-memory/73139/9?u=tcoopman

Let me reproduce some context here. This is the script that I use:

Mix.install([
    {:ecto_sql, "~> 3.13.2"},
    {:ecto_sqlite3, "~> 0.22"},
    {:exqlite, "~> 0.33.1"}
])

Application.put_env(:myapp, Repo, database: "./dev.db")

defmodule Repo do
  use Ecto.Repo,
    otp_app: :myapp,
    adapter: Ecto.Adapters.SQLite3
end

defmodule Main do
  @sql ~s"""
  SELECT
    s0."id",
    e1."id",
    e1."type",
    e1."data",
    e1."inserted_at",
    s0."stream_id",
    s0."stream_version"
  FROM "stream_events" AS s0
  INNER JOIN "events" AS e1 ON s0."event_id" = e1."id"
  WHERE s0."stream_id" = '$all' AND s0."stream_version" >= 0
  ORDER BY s0."id" DESC
  LIMIT 1
  """

  def sqlite do
    IO.inspect "before"
    print_memory()
    save_allocations("./before_sqlite")


    {:ok, _} = Repo.start_link([])
    _s1 = Ecto.Adapters.SQL.query!(Repo, @sql, nil)

    IO.inspect "after"
    print_memory()
    save_allocations("./after_sqlite")
  end

  def exqlite do
    IO.inspect "before"
    print_memory()
    save_allocations("./before_exqlite")

    {:ok, conn} = Exqlite.Sqlite3.open("./dev.db")
    {:ok, statement} = Exqlite.Sqlite3.prepare(conn, @sql)
    {:row, _results} = Exqlite.Sqlite3.step(conn, statement)
    IO.inspect "after"
    print_memory()
    save_allocations("./after_exqlite")
  end


  defp print_memory() do
    IO.inspect "memory: #{:erlang.memory(:total) / 1_000_000}"
  end

  defp save_allocations(name) do
    {:ok, x} = :instrument.allocations
    File.write!(name, inspect(x, limit: :infinity, pretty: true))
  end
end

# Main.sqlite()
Main.exqlite()

When running Main.sqlite the before and after memory jumps hugely (more than 50MB in this script, but when running in my phoenix app it's more like 300MB):

The difference I see in allocations is this:

What's weird is that the exact query matters.

• Changing DESC to ASC and the issue is reduced a lot: the memory still jumps but a lot less: from 59 to 95 instead from 59 to 130.
• Changing $all to any other stream_id and the issue is completely gone.

But that doesn't clarify why this issue does not represent itself when running directly with exqlite. If I use exqlite directly I don't see any of these issues?

The database is about 280MB so not small, but not huge. I could probably share it privately if that helps you.

[warmwaffles]

@tcoopman while the application is running for prolonged periods, does it also grow?

Do you have extra connection options specified in your config.exs's like :cache_size :cache_spill, :journal_size_limit, :soft_heap_limit, or :hard_heap_limit?

[tcoopman]

I don't think the application is grwoing in memory for prolonged periods. It's only that query that seems to trigger it. In production it's a lot worse because I currently have 500MB ram, and my app runs happy with about 200-300MB all the time, until I hit that query and then it crashes. I'll see if I give it 1000MB ram if that fully solves the problem (in dev it does).

These are my options in my app:

config :eventstore_sqlite, EventstoreSqlite.RepoRead,
  pool_size: 5,
  journal_mode: :wal,
  synchronous: :normal,
  cache_size: 1_000_000_000,
  busy_timeout: 5_000

but as you can see in the standalone script above I don't have these options and I still run into the issue

[warmwaffles]

Does it continue to grow after issuing it multiple times between measurements? Sorry I haven't checked it out yet, just trying to gather more data before I look further.

[tcoopman]

from memory, it does, but not on every measurement I think.

I'm at a conference this week, will try to have a look, but more details will probably be for next week.

[warmwaffles]

No worries. I'll see if I can spot anything between now and then.