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:

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

Thanks in advance.

You should provide the version of Rails you are using. If you are on Rails 5+ can you try wrapping your application code in `Rails.application.executor { }` (middleware or just hardcoded in your jobs) . I have no experience with Sneakers but I have a suspicion it does not release connections to the pool by itself and that should fix it. More details about the mechanism: https://guides.rubyonrails.org/threading_and_code_execution.html . Please let me know how it goes. — Svetlin Simonyan, Mar 19 '19 at 09:59
@SvetlinSimonyan I'm executing worker's code inside `ActiveRecord::Base.connection_pool.with_connection` block. If I'm not mistaken, `Rails.application.executor.wrap` uses it under the hood. — taras, Mar 21 '19 at 12:16

score 2 · Answer 1 · answered Mar 21 '19 at 12:37

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.

score 1 · Answer 2 · answered Jul 09 '19 at 10:01

1

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

answered Jul 09 '19 at 10:01

Dende

545
6
19

I already use it: https://stackoverflow.com/questions/55229693/rails-sneakers-could-not-obtain-a-connection-from-the-pool/56950078#comment97290170_55229693 – taras Jul 15 '19 at 07:38

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

2 Answers2