Rails Concurrency and Database Connections (Unicorn & Puma)

Alex Egg,

As Rails is single process process, it only uses 1 database connection per instance. However, occasionally, connections can become stale/dead/hang and rails will need to get a new connection. Rails will continue to create new connections as previous ones go stale. To provide an upper-limit on this connection creation, Rails has a feature called the connection pool.

Connection Pool

The connection pool is scalar number that defines the max number of connections that rails process can draw – the default is 5. Once the pool is empty, rails will throw this error:

ActiveRecord::ConnectionTimeoutError - could not obtain a database connection within 5 seconds. The max pool size is currently 5; consider increasing it

What happens if you empty your pool of connections, for example, for some reason all your connections have hung. Rails has a reaping_frequency feature that will run every N seconds and return stale connections to the pool. So a generally accepted connection pool size is 2 or 3 per rails thread of execution with a reaping frequency of 10.

Example database.yml

production:
  url:  <%= ENV["DATABASE_URL"] %>
  pool: <%= ENV["DB_POOL"] || ENV['MAX_THREADS'] || 2 %>

Heroku Unicorn/hobby DB Example

Hosted databases, like heroku, often have hard connection limits. For example, just consider the hobby database w/ 20 connections. Also, lets go w/ a 3 dyno, unicorn setup:

3 dynos & 3 unicorn processes = 9 rails processes

9 rails processes would optimally only need 9 database connections out of 20, so you have some flexability to add a pool size of 2 to mitigate against stale connections:

3 dynos * 3 unicorn processes * 2 connections in pool = 18 connections out of 20.

Or you could go for a more aggressive approach and increase your dynos at expense of a pool:

6 dynos * 3 unicorn processes = 18 connections out of 20 w/ a high reaping frequency

Testing connections

You can test your connections using this query on postgres, eg:

select count(*) from pg_stat_activity where pid <> pg_backend_pid()  and usename = current_user;
 count
-------
    17
(1 row)

Example Unicorn config (heroku)

#possible to set this to 8
#https://mikecoutermarsh.com/load-testing-heroku-1x-2x-px-dyno/
#would need a DB w/ more connections though
worker_processes (ENV["WEB_CONCURRENCY"] || 3).to_i
timeout (ENV["WEB_TIMEOUT"] || 7).to_i
preload_app true

run_sidekiq_in_this_thread = ENV["SIDEKIQ_HACK"]=="true"
@sidekiq_pid = nil

before_fork do |server, worker|

  Signal.trap 'TERM' do
    puts 'Unicorn master intercepting TERM and sending myself QUIT instead'
    Process.kill 'QUIT', Process.pid
  end

  defined?(ActiveRecord::Base) and
  ActiveRecord::Base.connection.disconnect!

  if run_sidekiq_in_this_thread
    p "RUNNING SIDEKIQ IN UNICORN WORKER!!!!*****"
    @sidekiq_pid ||= spawn("bundle exec sidekiq")
    Rails.logger.info('Spawned sidekiq [email protected]_pid}')
  end
end


after_fork do |server, worker|

  Signal.trap 'TERM' do
    puts 'Unicorn worker intercepting TERM and doing nothing. Wait for master to sent QUIT'
  end

  if defined? ActiveRecord::Base
    config = ActiveRecord::Base.configurations[Rails.env] ||
      Rails.application.config.database_configuration[Rails.env]
    config["reaping_frequency"] = (ENV["DB_REAPING_FREQUENCY"] || 10).to_i
    config["pool"] = (ENV["DB_POOL"] || 2).to_i
    ActiveRecord::Base.establish_connection(config)
  end
end

# 20 db connection available
# 3 dynos * 3 unicorn workers = 9 db connections or 3 per rails process
# 11 remaning for sidekiq

# Then your app will use 3 connections for workers.
# This means each dyno will require 3 connections.
# If you’re on a “Dev” plan, you can scale out to 6
# dynos which will mean 18 active database connections,
# out of a maximum of 20. However, it is possible for a
# connection to get into a bad or unknown state. Due to
# this we recommend setting the pool of your application
# to either 1 or 2 to avoid zombie connections from
# saturating your database. See the “Bad connection”
# section below.

Puma Example

Puma is a threaded server. Each process P, has T threads. The setup is the same as unicorn, except where on unicorn each process only has 1 thread, but on puma it can have T number of threads. Each thread needs a database connection, so the database pool needs to be set to (the number of puma threads + 2).

So for example, if I’m on heroku w/ 4 dynos D=4 and puma processes P=2 and threads T=5, I would need my DB pool to equal T or 5 which would max out at D*P*T = 4*2*5 = 40 DB connections.

Here’s my example puma config:

production:
  url:  <%= ENV["DATABASE_URL"] %>
  pool: <%= ENV["DB_POOL"] || ENV['MAX_THREADS'] || 5 %>

Permalink: rails-concurrency-and-database-connections

Tags:

Last edited by Alex Egg, 2016-10-05 19:04:20
View Revision History