Rails Reaper not working

Question

As per the API docs, the rails reaper is meant to find and recover connections from dead threads.The reaper is run based on the reaping_ frequency.

I am encountering cases when the number of database connections are exceeding the specified limit and there are connections in idle states but the reaper is not resetting those connections. I tried running the reaper manually but it doesn't seem to have any effect.

reaper = ActiveRecord::ConnectionAdapters::ConnectionPool::Reaper.new(ActiveRecord::Base.connection, 10)
reaper.run

That it doesn't have any effect was verified using

ActiveRecord::Base.connection.execute("SELECT * FROM pg_stat_activity WHERE pid <> pg_backend_pid()")
PgHero.total_connections

Is this a bug with the reaper in ActiveRecord or is it not meant to work like this? If that is the case how is the option to write a custom reaper to recover dead connections? The pg gem is being used for connecting to postgres db. The query that is eating up the connections is:

SHOW TRANSACTION ISOLATION LEVEL

Rails version: 4.2.3

pg gem version: 0.17.1

Postgres version: 9.4.6

Rails app server: Puma

Answer 1

A couple of notes first:

• It is probably not the SHOW TRANSACTION ISOLATION LEVEL query that is "eating up" your db connections. The pg_stat_activity view simply shows the active or, in your case, the last query executed on each connection. So the more important info from the stats is just that there are too many connections open.

• The ConnectionPool::Reaper frees db connections but only from the dead threads, i.e. those stopped or unexpectedly terminated. But it does not affect connections active or sleeping threads. The fact that Reaper does not work for you IMO simply means that those threads are probably sleeping, not dead.

Now, there can be a number of reasons for exceeding the max. allowed connections on the db server:

• You might have too many threads with a checked connection to the db at the same time. The ConnectionPool normally reserves one connection to the db per thread, up to the pool size configured in database.yml. So if your pool size is rather large and you have many threads, you can exceed the max. db connections. For example, puma creates up to 16 threads by default.

• Each rails process defines its own connection pool. So if for example your pool size is defined as 10 and you have 10 rails processes, the connections to db can raise up to 10 * 10 = 100 connections. Puma server allows to run multiple workers (which are separate processes), so you might have too many puma workers running.

• The same logic applies to all background processes and threads. Sidekiq for example by default creates up to 25 threads for background jobs. So if you use threads in your code or any gem that uses threads internally, e.g. for background jobs functionality, you must be aware of the precise setup of them so that you don't exceed the max number of connections.

• You might suffer from db connection leakage. Puma server needs a special setup when preload_app (App preloading) is used so that db connections are not leaked. This is documented e.g. here and here.