Running concurrent background jobs with sequel slowly kills my connection limit

[leenyburger]

Problem

I'm running ~6 queries concurrently in background jobs, and it's killing my database (exceeding connection limits). The connections clean up the first few runs but then increase linearly until the db connection limit is reached and it dies.

Question

Am I disconnecting from the db properly in the jobs? Any suggestions would be greatly appreciated I'm totally stumped. I don't know if I'm managing the connections poorly by calling (my method) connected on the db before I try to run something, or if I'm supposed to be clearing the DB[key] when I disconnect (although I tried that and it didn't seem to solve the issue).

Summary

I'm connecting to a database in a Sidekiq job to create a chart of query results. For reasons I cannot figure out the connections just increase with each job until everything dies. I load and execute a bunch of queries (~6) on a single page, so the jobs kick off around the same time.

Before I try to run the SQL I call a connected method which is designed to tell me if the db successfully connected. In the below method, db is

def db
    DB[key] ||= Sequel.connect(@connection_params)
end

  def connected?
    return false if db.nil?
    !!db.tables
  rescue Sequel::DatabaseConnectionError => e
    @error = "DatabaseConnectionError: #{e.message}"
    false
  end

The problematic job (it seems if I remove this everything is fine)

The job is below. I've tracked the Destroying connection for query logs and it seems this is being called for every query.
Here's the job that opens connections and only sometimes closes them....

class CreateChartDataJob < ApplicationJob
  queue_as :default

  def perform(chart_type, x_key, y_key, chart_name, query_id, database_connection_id)
    @query = Query.find(query_id)
    @database_connection = DatabaseConnection.find(database_connection_id)
    
    if @database_connection.connector.connected?
      begin
        results = @database_connection.connector.execute(@query.sql_string)
        if results
          chart = Chart.new(results: results, chart_type: chart_type, x_key: x_key, y_key: y_key, chart_name: chart_name)
          chart.create_chart_data
          sleep(2)
          Rails.logger.info("Chart created in background for query #{@query.id}")
          Turbo::StreamsChannel.broadcast_replace_to "query_background_#{@query.id}",
            target: "chart_data_#{@query.id}",
            partial: "shared/charts/chart_container",
            locals: { chart: chart, query: @query, database_connection: @database_connection }
        end
      ensure
        Rails.logger.info("Destroying connection for query #{@query.id}")
        @database_connection.connector.db.
      end
    else
      Turbo::StreamsChannel.broadcast_replace_to "query_background_#{@query.id}",
        target: "chart_data_#{@query.id}",
        partial: "shared/database_connector_error", locals: { database_connection: @database_connection, query: @query }
    end
  end
end

I'm running queries locally with ngrok and tracking the open connections

[jeremyevans]

By default, Sequel keeps a reference to all created Database objects. If you do not want that, you can use the keep_reference: false Database option. It could be you are deleting your reference to the Sequel::Database objects, but the reference kept by Sequel itself keeps the connections open.

Note that each Sequel::Database object has it's own pool of connections. I cannot tell if the reason you are doing DB[key] ||= Sequel.connect(@connection_params) is if you are trying to keep your own connection pool, but if so, you should stop as it isn't necessary. You should generally only create multiple Database objects if you are connecting to different databases (maybe that is what you are doing, but I couldn't tell).

[leenyburger]

It could be you are deleting your reference to the Sequel::Database objects, but the reference kept by Sequel itself keeps the connections open.

This seemed to fix everything so thank you! I'm trying to better understand why.

I'm not intentionally creating a connection pool, but definitely learning as I go here 😅

Yes, I'm allowing users to connect to multiple databases but not at the same time. A user logs on, connects their database, and they can connect more than one database (and many do) and switch between them.

I guess the question is - should I be manually disconnecting like that at all?
What's the correct way to tell Sequel to release a connection (or does it do that automatically). I added the explicit disconnect call because I saw the connection numbers creep up.

Since I'm only seeing this in background job, my guess is that because it's executing outside of the normal request/response cycle it doesn't get properly returned to the pool?

[jeremyevans]

Yes, I'm allowing users to connect to multiple databases but not at the same time. A user logs on, connects their database, and they can connect more than one database (and many do) and switch between them.

Understood. The DB[key] ||= Sequel.connect(@connection_params) makes more sense now.

I guess the question is - should I be manually disconnecting like that at all? What's the correct way to tell Sequel to release a connection (or does it do that automatically). I added the explicit disconnect call because I saw the connection numbers creep up.

I do not see any explicit disconnecting in your code example (it's probably in your code base, just not in the example). Calling Database#disconnect will remove the connections from the Database's connection pool.

Since I'm only seeing this in background job, my guess is that because it's executing outside of the normal request/response cycle it doesn't get properly returned to the pool?

The request/response cycle does not matter for Sequel's connection pool. All connection checkouts in Sequel are block based, there is no leaking of connections and cleaning them up later.

If the keep_reference: false option fixes things, the problem is as I expected, which is you probably were no longer holding a reference to the Database object in your code, but the Database objects were still referenced by Sequel, and they still had active connections in them. In this case, you probably are relying on the garbage collector to handle the disconnects, which is suboptimal. You likely want to call disconnect on Database objects before dropping a reference to them (e.g. DB.delete(key).disconnect, assuming you are using DB.delete(key) to remove the reference).

[rgalanakis]

In case it's useful, we have a similar thing for WebhookDB, where we manage our own pool of databases: https://github.com/webhookdb/webhookdb/blob/main/lib/webhookdb/connection_cache.rb

Then you can do stuff like conncache.borrow(connection_url) { |c| c.execute('SELECT 1') }, and know that you'll be reusing the existing Sequel connection pool for the given connection_url, or creating a new connection/pool if there is none. IMO having a pool abstraction, and using the connection URLs as the keys, gives you a lot of flexibility.