Ruby Threads + ActiveRecord == "Too Many Connections" ?

I recently encountered Mysql "Too Many Connections" issue in my Rails App, which makes my App down periodically. When I checked Mysql processlist, I could see 150+ active DB connections established from Rails App. How could my Rails App can open 150+ connections, though I mentioned AR connection pool size as 30. Baffled me !!!

Following is my development environment,

Ruby(2.0) + Rails(4.0) + Unicorn  + Nginx  + SideKiq + MultiTenant

• No.of Unicorn workers: 5
• No.of SideKiq processes: 1
• AR Connection pool size: 30 

According to experts, it would establish  5 * 1 + 30 = 35 maximum connections. Then why is it making 150+ connections in my App? Started Researching !!!

Found a magical sentence saying that the above calculation is true only for single threaded applications. Oh Yes! In my Rails app, I am spawning application level Threads based on no.of records being processed. So each Thread will try to make a connection to DB. Look at the following example,

class Article < ActiveRecord::Base
  def self.publish_all
    threads = []
    Article.by_unpublished.find_in_batches(batch_size: BATCH_CONST) do |articles|
      threads << Thread.new do
        ActiveRecord::Base.connection_pool.with_connection do
          articles.each do |article|
            article.publish
          end
        end
      end
    end
    threads.map(&:join)
  end
end

According to Rails Doc 'with_connection' method will Check-In the connection back to connection pool once after executing the Block given. But in the above example, it's not  releasing the connection rather it holds as active connection. So further requests to server keep creating new connections, this strange behaviour is the root cause of connection leak issue.

Solution !!!

Yes you are right, Check-In the connection explicitly.  So I created a utility class and wrapped around Thread invocation. For an example,

class ThreadUtility
  def self.with_connection(&block)
    begin
      yield block
    rescue Exception => e
      raise e
    ensure
      # Check the connection back in to the connection pool
      ActiveRecord::Base.connection.close if ActiveRecord::Base.connection
    end
  end
end

 class Article < ActiveRecord::Base
  def self.publish_all
    threads = []
    Article.by_unpublished.find_in_batches(batch_size: BATCH_CONST) do |articles|
      threads << Thread.new do
        ThreadUtility.with_connection do
          articles.each do |article|
            article.publish
          end
        end
      end
    end
    threads.map(&:join)
  end
end

Above example completely solved my connection leak issue, No more "Too Many Connections" notice from server.

Another strange behaviour which I would like to share here from above example is, whenever server spawning new thread, it creates new connection rather than reusing existing connection from pool of connections already available. This is due to the nature of ActiveRecord's Lazy connection pooling technique(connections are opened lazily). Once the no.of established connections in pool reaches the pool size mentioned in config, it grabs and reuses the connections from pool.

Now tell me guys, how many maximum connections that AR can establish from the above code fix for following configuration,

• No.of Unicorn workers: 5
• AR Connection pool size: 10

Is it 10 ? But the truth is 5 * 10 = 50 connections. Bcoz each unicorn process can establish 10 maximum connections in multi threaded application.

Your valuable comments are always Welcome !!!