Rails: switch connection on each request but keep a connection pool

Question

In our Rails application we need to use different databases depending on the subdomain of the request (different DB per country).

Right now we're doing something similar to what's recommended in this question. That is, calling ActiveRecord::Base.establish_connection on each request.

But it seems ActiveRecord::Base.establish_connection drops the current connection pool and establishes a new connection each time it's called.

I made this quick benchmark to see if there was any significant difference between calling establish_connection each time and having the connections already established:

require 'benchmark/ips'

$config = Rails.configuration.database_configuration[Rails.env]
$db1_config = $config.dup.update('database' => 'db1')
$db2_config = $config.dup.update('database' => 'db2')

# Method 1: call establish_connection on each "request".
Benchmark.ips do |r|
  r.report('establish_connection:') do
    # Simulate two requests, one for each DB.
    ActiveRecord::Base.establish_connection($db1_config)
    MyModel.count # A little query to force the DB connection to establish.
    ActiveRecord::Base.establish_connection($db2_config)
    MyModel.count
  end
end

# Method 2: Have different subclasses of my models, one for each DB, and 
# call establish_connection only once
class MyModelDb1 < MyModel
  establish_connection($db1_config)
end

class MyModelDb2 < MyModel
  establish_connection($db2_config)
end

Benchmark.ips do |r|
  r.report('different models:') do
    MyModelDb1.count
    MyModelDb2.count
  end
end

I run this script with rails runner and pointing to a local mysql with some couple thousand records on the DBs and the results seem to indicate that there in fact is a pretty big difference (of an order of magnitude) between the two methods (BTW, i'm not sure if the benchmark is valid or i screwed up and therefore the results are misleading):

Calculating -------------------------------------
establish_connection: 8 i/100ms
-------------------------------------------------
establish_connection: 117.9 (±26.3%) i/s -        544 in   5.001575s
Calculating -------------------------------------
    different models:  119 i/100ms
-------------------------------------------------
    different models:  1299.4 (±22.1%) i/s -       6188 in   5.039483s

So, basically, i'd like to know if there's a way to maintain a connection pool for each subdomain and then re-use those connections instead of establishing a new connection on each request. Having a subclass of my models for each subdomain is not feasible, as there are many models; i just want to change the connection for all the models (in ActiveRecord::Base)

Answer 1

Well, i've been digging into this a bit more and managed to get something working.

After reading tenderlove's post about connection management in ActiveRecord, which explains how the class hierarchy gets unnecessarily coupled with the connection management, i understood why doing what i'm trying to do in not as straightforward as one would expect.

What i ended up doing was subclassing ActiveRecord's ConnectionHandler and using that new connection handler at the top of my model hierarchy (some fiddling on the ConnectionHandler code was needed to understand how it works internally; so of course this solution could be very tied to the Rails version i'm using (3.2)). Something like:

# A model class that connects to a different DB depending on the subdomain 
# we're in
class ModelBase < ActiveRecord::Base
  self.abstract_class = true
  self.connection_handler = CustomConnectionHandler.new
end

# ...

class CustomConnectionHandler < ActiveRecord::ConnectionAdapters::ConnectionHandler
  def initialize
    super
    @pools_by_subdomain = {}
  end

  # Override the behaviour of ActiveRecord's ConnectionHandler to return a
  # connection pool for the current domain.
  def retrieve_connection_pool(klass)
    # Get current subdomain somehow (Maybe store it in a class variable on 
    # each request or whatever)
    subdomain = @@subdomain
    @pools_by_subdomain[subdomain] ||= create_pool(subdomain)
  end

  private
  def create_pool(subdomain)
    conf = Rails.configuration.database_configuration[Rails.env].dup
    # The name of the DB for that subdomain...
    conf.update!('database' => "db_#{subdomain}")
    resolver = ActiveRecord::Base::ConnectionSpecification::Resolver.new(conf, nil)
    # Call ConnectionHandler#establish_connection, which receives a key 
    # (in this case the subdomain) for the new connection pool
    establish_connection(subdomain, resolver.spec)
  end
end

This still needs some testing to check if there is in fact a performance gain, but my initial tests running on a local Unicorn server suggest there is.