Rails + Sneakers: could not obtain a connection from the pool

Question

We're using Sneakers gem in production for a big application. And sometimes the load can be so huge that one specific queue may contain over 250_000 messages. And in such cases, the exception

ActiveRecord::ConnectionTimeoutError: 
could not obtain a connection from the pool within 5.000 seconds (waited 5.000 seconds); all pooled connections were in use

occurs regularly.

For the database, we are using Amazon RDS based on PostgreSQL 9.6. max_connections PostgreSQL config value is 3296.

Our database.yml file:

production:
  adapter: postgresql
  encoding: utf8
  pool: 40
  database: <%= ENV['RDS_DB_NAME'] %>
  username: <%= ENV['RDS_USERNAME'] %>
  password: <%= ENV['RDS_PASSWORD'] %>
  host: <%= ENV['RDS_HOSTNAME'] %>
  port: <%= ENV['RDS_PORT'] %>

I guess we can increase a pool value, but I can't find info about how to calculate max possible value so it will not break anything.

Also, a copy of an application for background processing using Sneakers gem lives separately (but uses the same database) and can be configured individually. But right now it has the same database.yml config. Sneakers gem config file:

production:
  heartbeat: 2000
  timeout_job_after: 35
  exchange_type: :fanout
  threads: 4
  prefetch: 4
  durable: true
  ack: true
  daemonize: true
  retry_max_times: 5
  retry_timeout: 2000
  workers: 4

We have no problems with connections pool in base runtime application, but ActiveRecord::ConnectionTimeoutError occurs in workers very often and it is a really big problem.

So, please help me to reconfigure databese.yml file:

1. How to correctly calculate the max possible value for pool option if the database max_connections value is 3296?
2. How to correctly calculate the max possible value for pool option when using the Sneakers gem with the configs above?
3. Or, if my configs are good, how can I avoid ActiveRecord::ConnectionTimeoutError in workers?

Thanks in advance.

Answer 1

While waiting for an answer, I kept looking for a solution.

And, I guess, my very base problem was in connection pool size.

On the Sneakers gem issues tracker, I found a comment with the formula for calculating the required number of connections at full load. I changed the code from the comment a bit, so now it makes calculations taking into account the individual settings of each worker:

before_fork = -> {
  ActiveSupport.on_load(:active_record) do
    ActiveRecord::Base.connection_pool.disconnect!
    Sneakers.logger.info('Disconnected from ActiveRecord!')
  end
}

after_fork = -> {
  def count_pool_size
    workers              = ::Sneakers::Worker::Classes
    default_threads_size = ::Sneakers.const_get(:CONFIG)[:threads]
    base_pool_size       = 3 + workers.size * 3

    if Sneakers.const_get(:CONFIG)[:share_threads]
      base_pool_size + default_threads_size
    else
      base_pool_size + connections_per_worker(workers, default_threads_size)
    end
  end

  def connections_per_worker(classes, default)
    classes.inject(0) do |sum, worker_class|
      sum + (worker_class.queue_opts[:threads] || default)
    end
  end

  def reconfig?
    Rails.env.production?
  end

  ActiveSupport.on_load(:active_record) do
    config = Rails.application.config.database_configuration[Rails.env]
    config.merge!('pool' => count_pool_size) if reconfig?

    ActiveRecord::Base.establish_connection(config)
    Sneakers.logger.info("Connected to ActiveRecord! Config: #{config}")
  end
}

Summary: for all workers, I need to have a limit of almost 600 connections at maximum load. But I only had 40. For now, I will use the above code. Hope this helps.

Answer 2

From here.

Active Record didn't seem to be releasing connections after the work() method had completed. If you pass the block using Active Record to ActiveRecord::Base.connection_pool.with_connection, it will release the connections perfectly.

def work(msg)
  id = JSON.parse(msg)['id']
  ActiveRecord::Base.connection_pool.with_connection do
    user = User.find(id)
    user.name="Homer Simpson"
    user.save
  end
  ack!
end