Friday, July 18, 2014

ActiveRecord::ConnectionTimeoutError (could not obtain a database connection within 5.000 seconds)

it turn out there could be 2 reasons,

1. rails connection pool have reached and there might be zombie connection
2. mysql max_connections / max_user_connections may have reached

in most cases, its the first possibility.
ensure that you have set the connection pool to a much higher value,
to handle enough concurrent users at the same time

production:
  adapter: mysql2
  pool: 100

2nd issue is due to zombie connection which may lead filling up pool with undead connections.
add a config/initializers/db_connections.rb
with these code:
-----------------
Rails.application.config.after_initialize do
  ActiveRecord::Base.connection_pool.disconnect!

  ActiveSupport.on_load(:active_record) do
    config = ActiveRecord::Base.configurations[Rails.env] ||
                Rails.application.config.database_configuration[Rails.env]
    config['reaping_frequency'] = ENV['DB_REAP_FREQ'] || 10 # seconds
    #config['pool']              = ENV['DB_POOL']      || ENV['MAX_THREADS'] || 5
    puts "pool size: "+config['pool'].to_s
    ActiveRecord::Base.establish_connection(config)
  end
end
-----------------
these should hopefully handle undead issue for db connections.

another fix is possible to fix network connection bug when writing to a disconnected connection.
------------------
response.stream.write data
#workaround hang stream due to client dc halfway: https://gist.github.com/njakobsen/6257887
sleep 0.0001
-------------------

to check number of active connections for a database,
run rails dbconsole# (makesure you have set RAILS_ENV to the correct environment)
#and:

SHOW FULL PROCESSLIST;

thanks to these articles:


No comments: