Unable to set pool and timeout settings in databases other than the one the app uses per environment

rubyonrails-talk
1

I’m running rails 7.0, and having a bit of trouble configuring the pool and timeout for one of the databases the application needs.

My database.yml file looks like this:

development:
  (my dev env settings here settings here)

production:
  adapter: <%= ENV["PROD_DB_ADAPTER"] %>
  host: <%= ENV["PROD_DB_HOST"] %>
  database: <%= ENV["PROD_DB_DB"] %>
  username: <%= ENV["PROD_DB_USERNAME"] %>
  password: <%= ENV["PROD_DB_PASSWORD"] %>
  port: <%= ENV["PROD_DB_PORT"] %>
  pool: 5
  timeout: 5000

my_other_database:
  adapter: <%= ENV["MY_ADAPTER"] %>
  host: <%= ENV["MY_HOST"] %>
  database: <%= ENV["MY_DB"] %>
  username: <%= ENV["MY_USERNAME"] %>
  password: <%= ENV["MY_PASSWORD"] %>
  port: <%= ENV["MY_PORT"] %>
  pool: 20
  timeout: 10000

I have confirmed the ENV variables are passing the right values

One of my rails models needs to connect to “my_other_database”, which is different from the database that the app uses, and I have that setup as follows:

class MyModel < ActiveRecord::Base
    establish_connection(:my_other_database)
end

According to what I see in the documentation, that is the way to set it:

https://apidock.com/rails/v7.0.0/ActiveRecord/ConnectionHandling/establish_connection

But when I run my application and try to use that model to pull data, I get the following error:

No connection pool for ‘MyModel’ found.

If I change my model to look like this (according to that same documentation):

class MyModel < ActiveRecord::Base
    establish_connection(
      adapter: 'adapter here',
      host: 'host here,
      database: 'database name here',
      username: 'username here',
      password: 'password here',
      port: 'port here',
      pool: 20, (notice these two are not part of the documentation, but I just don't know how else to try them)
      timeout: 1000 (same as the other above)
    )
end

Setting the model that way (almost always) works… with high traffic, it seems to timeout sometimes, with this error:

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

Notice the “within 5.000 seconds” part. It seems it has ignored my “timeout: 10000”, and fall back to the default

So I am a bit confused. Why would establish_connection(:my_other_database) doesn’t just work as advertised in the docs?

And why if I do it the other way, it just ignores pool and timeout, and falls back to the defaults?

Is there a way I could force pool and timeout to what I need them to be in that establish_connection block some other way?

Please help!

2

Hello, @ramrod

Here What you can do to troubleshoot and resolve this issue:

Ensure Environment Variables: Double-check that your environment variables are correctly set in your deployment environment. They should match the values you’re expecting in your database.yml. Configuration in database.yml: Your database.yml seems to be set up correctly. However, make sure that the indentation is correct as YAML files are sensitive to it. Connection Pool: The error message “No connection pool for ‘MyModel’ found” suggests that Rails is unable to find the connection details for my_other_database in the database.yml. Make sure that my_other_database is correctly defined and accessible. Timeout Settings: The default timeout for connection pools is 5 seconds. If you’re experiencing timeouts, you might want to increase the checkout_timeout setting in your database.yml for my_other_database. This is different from the timeout setting, which is often confused with checkout_timeout. The checkout_timeout is the amount of time Rails will wait to acquire a connection before giving up. Establish Connection: When you use establish_connection in your model, Rails expects to find the connection details in the database.yml under the specified key. If you’re defining the connection details directly in the model (as in your second example), you need to ensure that all required parameters are provided, including pool and timeout. Traffic Handling: If you’re still experiencing timeouts during high traffic, consider increasing the pool size to accommodate more concurrent connections. Monitor your database server to ensure it can handle the increased number of connections without performance degradation. Rails Documentation: The Rails documentation might not cover all edge cases, and sometimes community resources like Stack Overflow can provide more context and solutions to specific problems. Here’s an example of how you might configure the establish_connection method with the pool and timeout settings:

class MyModel < ActiveRecord::Base establish_connection( adapter: ‘adapter here’, host: ‘host here’, database: ‘database name here’, username: ‘username here’, password: ‘password here’, port: ‘port here’, pool: 20, checkout_timeout: 10000 # Use checkout_timeout instead of timeout ) end

Remember to replace the placeholder text with your actual database configuration details. The key here is to use checkout_timeout instead of timeout to specify the duration Rails will wait for a connection from the pool.