CODEWITHSUNDEEP
ruby-on-railspostgresqlamazon-rds
Henry Boisgibault
Henry Boisgibault

Reputation: 826

Slow and frequent PostgreSQL database connection booting in Rails API

I have a Rails API with a PostgreSQL database.

Some requests to the API show a strange behavior that doesn't depend on the endpoint.

These requests (around 5-10% of total requests) start with the same 7 database queries :

The request also takes a long time to start before the 7 queries are executed.

It seems to be the database adapter initiating a connection. ActiveRecord::ConnectionAdapters::PostgreSQLAdapter

This significantly slows down the query.

I am using a PostegreSQL 11.6 AWS RDS instance, with default parameters.

Here is my database.yml config :

default: &default
  adapter: postgresql
  encoding: unicode
  username: *****
  password: *****
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

production:
  <<: *default
  database: *****
  username: *****
  password: *****
  pool: 50

How do I reduce the number of connections initiating ? Is there a way to cache the queries ?

Thank you,

Upvotes: 2

Views: 1523

Answers (1)

user1032752
user1032752

Reputation: 871

Ran into the same thing and here's what I think is happening:

Every time a new connection is instantiated it performs the bootstrapping queries you mention above. Assuming a new process is not spawned, a new connection would need to be instantiated because existing connections have been reaped by ActiveRecord.

By default, the ConnectionPool::Reaper will disconnect any connection that has been idle for over 5 minutes. See: https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/ConnectionPool.html

If your API does not receive any requests for a period of 5 minutes and all the connections are reaped the next request will need to instantiate a new connection and therefore run the queries.

How do I reduce the number of connections initiating ?

You could set an idle_timeout of 0 in database.yml. This would prevent ActiveRecord from reaping the connections but could potentially cause issues depending on how many processes are running and what your PG max_connections value is.

Is there a way to cache the queries ?

There's a closed issue that talks about this but it doesn't look like it's possible to cache these today. https://github.com/rails/rails/issues/35311

Upvotes: 2

Related Questions