ConnectionCache: a crazy Ecto connection pool

cjbottaro · June 27, 2016, 10:28pm

Hi. I’ve been asking a lot about how to implement a custom Ecto connection pool and a custom adapter to accommodate said pool. Here is the result of all that:

https://github.com/cjbottaro/ecto_pools_connection_cache

tl;dr: We have a multi-tenanted application that has too many databases spread across too many servers for normal connection pooling to work (even with pgBouncer). We need a “connection pool” that can create connections on demand and try to reuse ones that have been recently used, while evicting ones that haven’t been used in a while (like an LRU cache).

So it works fine…

# config.exs
config :my_app, MyRepo,
  adapter: Ecto.Adapters.PostgresWithoutCache,
  pool: Ecto.Pools.ConnectionCache,
  prepare: :unnamed,
  pool_size: 2,
  host: "db01.internal",
  database: "shard01"

# in iex or some such...

# Creates new connection to "default" database as specified by config.exs
MyRepo.get(...)

# Uses cached connection to "default" database
MyRepo.get(...)

# Creates a new connection to db02.internal/shard50
MyRepo.set_database(host: "db02.internal", database: "shard50")
MyRepo.get(...)

# Uses cached connection to "default" database
MyRepo.set_database(:default)
MyRepo.get(...)

# Creates new connection to db03.internal/shard99
# and closes/evicts the connection to db02.internal/shard50
# since our pool_size is set to 2 and it was least recently used.
MyRepo.get_database(host: "db03.internal", database: "shard99")

But… I’m new to Elixir and OTP. I have no idea if my code is any good or not…

Also, how Ecto connection pools work is very confusing to me. I figured it would be as easy as implementing the DBConnection.Pool behaviour, but it was not obvious from the documentation.

Ok, so checkout/2 is supposed to return {:ok, pool_ref, module, state}… but what does any of that mean? After many hours IE.pry and IO.inspect and reading the source for the poolboy based pool, I figured out that module and state should come from the return value of DBConnection.Connection.checkout/2.

And that’s the part that’s really throwing me off. I though my connection pool was supposed to manage connections to the database… but it looks like it’s managing other connection pools (each of size 1).

Also, these connections / connection pools are started outside a supervision tree; which that’s how the poolboy based pool works, I just copied it:

pid = DBConnection.Connection.start_link(module, connection_options)
# Then store the pid in my pool's GenServer state.

So everything generally works, but I have no idea if I’m doing things “right” or not. Nor do I really understand how Ecto expects connection pools to work:

What is a connection? It looks like a connection is it itself a pool. So a connection pool is pooling other smaller connection pools? Why?
What does Ecto expect checkout/2 to return? Both conceptually and also as in “what interface”?
Why aren’t these “connections” started in a supervision tree?

Anyway… just wanted to share my first kinda deep dive into Elixir code. I seriously doubt anyone will have a similar use case, but it would be super awesome to get any feedback.

Thanks!