2

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 :

  • SET client_min_messages TO ?
  • SET standard_conforming_strings = on
  • SET SESSION timezone TO ?
  • SELECT t.oid, t.typname FROM pg_type WHERE t.typname IN ( ? ) ...

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,

Henry Boisgibault
  • 826
  • 7
  • 26
  • What piece of information do you need ? – Henry Boisgibault Mar 31 '20 at 14:08
  • 1
    In pg_stat_activity, how many 'client backend' connections are generally open, and how long have they been open (backend_start). Also, when you say they take a long time to start, how long are you talking about? 5 milliseconds? 5 seconds? – jjanes Mar 31 '20 at 14:50
  • There are around 3 connections open, they usually last on average 15 minutes I would say. It takes around 50-100ms to start, when the normal request is around 10ms. – Henry Boisgibault Mar 31 '20 at 17:03
  • Do the problem connections also only occur around every 15 minutes, or are they more frequent than that? – jjanes Apr 01 '20 at 14:17

1 Answers1

1

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

user1032752
  • 751
  • 1
  • 11
  • 28