Active Record Connection Pool Monitor

active_record_connection_pool_monitor.rb

# frozen_string_literal: true

class ActiveRecordConnectionPoolMonitor
  def initialize(service)
    @service = service
    puts "Starting ActiveRecordConnectionPoolMonitor for #{@service}"
  end

  def start
    require "rufus-scheduler"
    scheduler = Rufus::Scheduler.new

    connection_info = {}

    scheduler.every "15s" do
      collect_metrics(connection_info)
    end
  end

  private

    def collect_metrics(connection_info)
      return unless ActiveRecord::Base.connected?
      
      connection_owner_types = Hash.new(0)
      connection_seconds_idle = []

      ActiveRecord::Base.connection_pool.connections.each do |c|
        connection_info[c.object_id] ||= { creation_time: Time.now }
        connection_info[c.object_id][:active] = true        
        connection_seconds_idle << c.seconds_idle

        owner_name = c.owner&.name.to_s
        owner_type = case owner_name
                     when /puma/ then :puma
                     when /sidekiq/ then :sidekiq
                     else :other
        end
        
        connection_owner_types[owner_type] += 1

        if owner_type == :other
          backtrace = c.owner&.backtrace&.join(", ")
          Rails.logger.info "connection pool owner type: #{owner_name}"
          Rails.logger.info "connection pool owner backtrace: #{backtrace}"
        end
      end

      update_connection_metrics(connection_seconds_idle, connection_info)
      update_owner_metrics(connection_owner_types)
      update_pool_stats

      connection_info.each { |id, info| info[:active] = false }
      connection_info.reject! { |_, info| !info[:active] }
    end

    def update_connection_metrics(connection_seconds_idle, connection_info)
      if connection_seconds_idle.any?
        idle_average = connection_seconds_idle.sum / connection_seconds_idle.size.to_f
        ::NewRelic::Agent.record_metric("Custom/ActiveRecord/ConnectionPool/#{@service}/IdleTime", idle_average)
      end

      if connection_info.any?
        avg_connection_age = connection_info.values.sum { |c| Time.now - c[:creation_time] } / connection_info.size.to_f
        ::NewRelic::Agent.record_metric("Custom/ActiveRecord/ConnectionPool/#{@service}/ConnectionAge", avg_connection_age)
      end
    end

    def update_owner_metrics(connection_owner_types)
      connection_owner_types.each do |owner, count|
        ::NewRelic::Agent.record_metric("Custom/ActiveRecord/ConnectionPool/#{@service}/OwnerCount/#{owner}", count)
      end
    end

    def update_pool_stats
      stats = ActiveRecord::Base.connection_pool.stat
      ::NewRelic::Agent.record_metric("Custom/ActiveRecord/ConnectionPool/#{@service}/Size", stats[:size])
      ::NewRelic::Agent.record_metric("Custom/ActiveRecord/ConnectionPool/#{@service}/Connections", stats[:connections])
      ::NewRelic::Agent.record_metric("Custom/ActiveRecord/ConnectionPool/#{@service}/Busy", stats[:busy])
      ::NewRelic::Agent.record_metric("Custom/ActiveRecord/ConnectionPool/#{@service}/Dead", stats[:dead])
      ::NewRelic::Agent.record_metric("Custom/ActiveRecord/ConnectionPool/#{@service}/Idle", stats[:idle])
      ::NewRelic::Agent.record_metric("Custom/ActiveRecord/ConnectionPool/#{@service}/Waiting", stats[:waiting])
    end
end

info.md

Metric	Description
Pool Size	The maximum number of connections allowed in the pool
Connections	The current number of connections in the pool
Busy	The number of connections currently checked out
Dead	The number of connections that are no longer usable
Idle	The number of connections that are not currently checked out
Waiting	The number of threads waiting for a connection
Connection Age	The average age of connections in seconds
Idle Time	The average idle time of connections in seconds
Owner Count	Distribution of connections by owner type (puma, sidekiq, etc.)

instructions.md

Initialize ActiveRecordConnectionPoolMonitor.

For Puma - add to config/puma.rb

on_worker_boot do
  ActiveRecordConnectionPoolMonitor.new("Web").start
end

For Sidekiq - add to config/initializers/sidekiq.rb

Sidekiq.configure_server do |config|
  ActiveRecordConnectionPoolMonitor.new("Sidekiq").start
end

Note:

• Make sure to add gem 'rufus-scheduler' to your Gemfile.
• Here it is assumed that you use NewRelic as your APM. For other APMs, you can use the corresponding agent to record the metrics.