Postgresql Heroku keeps connection

[adriano-tirloni]

Ruby version: 2.4.0
Sidekiq

Hello,
I am running sidekiq with the following setting: concurrency: 10, puma: 2 workers/single-threaded
Heroku Redis allows for 20 connections, but it stays at 14 as expected.

I wrote a test job to figure limits for Postgresql pool size:

class TestJob < ApplicationJob
  queue_as :default
  def perform(time, ar)
    User.first if ar == true
    time ||= rand(1..15)
    sleep time
  end
end

When testing without querying pg my pg connections stays at around 4. (heroku pg:info)
When I set ar to true, the connections go up, to around 14, the thing is the connection hangs forever, I waited several minutes and it still maintain the 14 connections open.

Shouldn't sidekiq check-out the connection when the job is over?

Thanks

[mperham]

Seems normal to me. Sidekiq doesn't do anything with the database, AR controls those connections. You are seeing 10 more connections created, one for each sidekiq worker thread.

[adriano-tirloni]

I see, I am actually trying to understand how all of this works. For the sake of documenting below there is my findings and a command to check active connections and where they came from:

heroku pg:psql -c 'SELECT application_name, state, backend_start, query_start, query FROM pg_stat_activity WHERE usename=current_user;'
More information about columns: postgresql documentation.

Some curious stuff that I found:

• When DB pool is set to 4 I could only get 3 connections (sidekiq 5.0.3 app [0 of 12 busy]), it seems that always I can only open (pool - 1) sidekiq connections.
• When a sidekiq connection is working properly it has this application_name => sidekiq 5.0.3 app [0 of 12 busy] when not, this is the name: /app/vendor/bundle/ruby/2.4.0/bin/sidekiq
• After a deploy all sidekiq connections disappear and one bad like I said above appear.

As I mentioned I have 2 puma workers with 1 thread each.

• Puma Workers are more complicated:

puma: cluster worker 0: 4 [app]
puma: cluster worker 1: 4 [app]

This above should be what I am expecting, 2 connections; But, after deploy and several page loads I end up with as many as (pool - 1) duplicated workers with the same name.

• 2 of them process every request OK
• Some of them are never used again
• Occasionally some of them process part of a query of one of the two good ones, like:
  Say I have @posts = Post.all and @comments = Comment.all in the same controller action, one of the good workers (Execution of middleware chain using blocks, unit tests, and minor cleanup #1) query posts and his duplicate query comments, while page loads are being executed by Execution of middleware chain using blocks, unit tests, and minor cleanup #1 and #0. Now I have 3 connections querying postgresql

What I did: I set a cron job to destroy connections not used for 5 minutes. If I have time I will get to the bottom of this puma worker situation.

[hmistry]

@dritir Rails has a setting for closing db connections in database.yml called reaping_frequency.

  # set to close unused db connections every 5 mins in database.yml
  reaping_frequency: 300