Is it possible to create a shared connection pool that can be used across many repos?
I attempted to create a naive implementation that would just delegate everything to DBConnection.Poolboy
just to see if I could get something working. I assumed Ecto.Repo
started the pool using DBConnection.Poolboy.start_link/2
but that doesn’t seem to be the case. It looks like DBConnection.Connection
does a lot of heavy lifting but it’s not clear from the docs how it works.
Super basic, don’t laugh:
defmodule Data.Pool do
@moduledoc false
@behaviour DBConnection.Pool
@registry Data.Pool.Registry
defdelegate checkin(pool_ref, state, opts), to: DBConnection.Poolboy
defdelegate checkout(pool, opts), to: DBConnection.Poolboy
defdelegate child_spec(mod, opts, child_opts), to: DBConnection.Poolboy
defdelegate disconnect(pool_ref, err, state, opts), to: DBConnection.Poolboy
defdelegate ensure_all_started(opts, type), to: DBConnection.Poolboy
defdelegate stop(pool_ref, err, state, opts), to: DBConnection.Poolboy
def start_link(mod, opts) do
case @registry.get() do
[] ->
@registry.put(DBConnection.Poolboy.start_link(mod, opts))
[ pid ] ->
{ :ok, pid }
end
end
end
This is the reason I just share a repo between all umbrella apps, and declare the schemas separately in each app. Otherwise you can easily max out the 200 connection limit on an AWS db.t2.small instance
This is typically what I do and I believe most other people using umbrella apps do as well. For me, the problem arises when using tools like Postgres schemas. You can have multiple logical datastores within the same physical database which share a connection limit. In that scenario each application should have it’s own Repo with a shared connection pool.
Pus, you can’t generate your migrations within the contextually relevant applications. That’s the real problem! Makes my eye twitch.
@anthonator Just dropping by to say that your approach inspired me to figure out something similar.
I did just slightly differently without a new connection pool module (and thus no delegations).
defmodule DB.SharedConnectionPool do
alias DBConnection.ConnectionPool
def child_spec({mod, opts}) do
opts = Keyword.put_new(opts, :name, pool_name(opts))
Supervisor.Spec.worker(__MODULE__, [{mod, opts}])
end
def start_link({mod, opts}) do
case GenServer.start_link(ConnectionPool, {mod, opts}, start_opts(opts)) do
{:ok, pid} -> {:ok, pid}
{:error, {:already_started, pid}} -> {:ok, pid}
error -> error
end
end
defp pool_name(opts) do
case Keyword.fetch(opts, :shared_pool_id) do
{:ok, pool} -> String.to_atom("#{__MODULE__}-#{pool}")
:error -> __MODULE__
end
end
# Exact same as `DBConnection.ConnectionPool`
defp start_opts(opts) do
Keyword.take(opts, [:name, :spawn_opt])
end
end
And I have the repos configured like this:
config :my_app, MyApp.Repo,
pool: DB.SharedConnectionPool,
shared_pool_id: :my_shared_pool_id
config :my_second_app, MySecondApp.Repo,
pool: DB.SharedConnectionPool,
shared_pool_id: :my_shared_pool_id
This way makes it also possible to specifically configure what repos should share a pool, and multiple shared pools can be created.
The downside is that it still relies on the connection info from the configs, which means if two repos with different configs share the same pool, there’s a race condition and one repo config will be taken to start the pool. But otherwise this approach has the least impact on how Ecto works.
Full code here: https://github.com/omisego/ewallet/pull/853 (disclaimer: I’m employed by this project)
With db_connection 2.4.0, i get this error
** (UndefinedFunctionError) function Solve.Utils.DB.SharedConnectionPool.checkout/3 is undefined or private
(utils 0.1.0) Solve.Utils.DB.SharedConnectionPool.checkout(#PID<0.13239.0>, [#PID<0.13592.0>, #PID<0.13591.0>], [log: #Function<14.6198013/1 in Ecto.Adapters.SQL.with_log/3>, source: "query_history_v1", timeout: 15000, pool_size: 10, pool: Solve.Utils.DB.SharedConnectionPool])
(db_connection 2.4.0) lib/db_connection.ex:1082: DBConnection.checkout/3
(db_connection 2.4.0) lib/db_connection.ex:1407: DBConnection.run/6
(db_connection 2.4.0) lib/db_connection.ex:574: DBConnection.parsed_prepare_execute/5
(db_connection 2.4.0) lib/db_connection.ex:566: DBConnection.prepare_execute/4
(ecto_sql 3.5.4) lib/ecto/adapters/sql.ex:690: Ecto.Adapters.SQL.execute!/4
(ecto_sql 3.5.4) lib/ecto/adapters/sql.ex:682: Ecto.Adapters.SQL.execute/5
(ecto 3.5.6) lib/ecto/repo/queryable.ex:224: Ecto.Repo.Queryable.execute/4
(ecto 3.5.6) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
(saved_query_service 0.1.0) lib/query_service.ex:55: Solve.SavedQueryService.fetch_history/2
(query_runner 0.1.0) lib/query_runner/tidy_history_task.ex:7: Solve.QueryRunner.TidyHistory.run/0
(elixir 1.11.3) lib/task/supervised.ex:90: Task.Supervised.invoke_mfa/2
(stdlib 3.14) proc_lib.erl:226: :proc_lib.init_p_do_apply/3
Adding:
defdelegate checkout(pool, callers, opts), to: ConnectionPool
to the connection pool here Sharing a DBConnection pool with multiple Ecto Repo's - #5 by unnawut seems to fix the issue