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
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
3 per rails thread of execution with a reaping frequency of
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
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
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 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
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 %>