Unicorns eat up MySQL connections not respecting the pool size - Rails

Question

I run 4 Unicorn processes for my Rails app and they eat up all the available MySQL connections causing it to collapse with 'too many connections' error. Today I had to reboot my DB instance 4 times. =(

Processes

$ ps ax | grep [u]ni
21618 ?        Sl     0:15 unicorn master -D -c /home/deployer/apps/XXX/shared/config/unicorn.rb -E production                                                           
21632 ?        Sl     0:20 unicorn worker[0] -D -c /home/deployer/apps/XXX/shared/config/unicorn.rb -E production                                                        
21636 ?        Sl     0:14 unicorn worker[1] -D -c /home/deployer/apps/XXX/shared/config/unicorn.rb -E production                                                        
21640 ?        Sl     0:20 unicorn worker[2] -D -c /home/deployer/apps/XXX/shared/config/unicorn.rb -E production                                                        
21645 ?        Sl     0:12 unicorn worker[3] -D -c /home/deployer/apps/XXX/shared/config/unicorn.rb -E production

My database.yml is setting up 22 connections for the ActiveRecord pool...

...
production:
  adapter: mysql2
  encoding: utf8
  database: xxx
  username: xxx
  password: xxx
  host: xxx
  port: 3306
  pool: 22
...

And the Unicorn config file looks like this:

working_directory "/home/deployer/apps/XXX/current"
pid "/home/deployer/apps/XXX/shared/pids/unicorn.pid"
stderr_path "/home/deployer/apps/XXX/shared/log/unicorn.log"
stdout_path "/home/deployer/apps/XXX/shared/log/unicorn.log"

listen "/tmp/unicorn.XXX.sock"
worker_processes 4
timeout 100

preload_app true

before_fork do |server, worker|
  # Disconnect since the database connection will not carry over
  if defined? ActiveRecord::Base
    ActiveRecord::Base.connection.disconnect!
  end

  # Quit the old unicorn process
  old_pid = "#{server.config[:pid]}.oldbin"
  if File.exists?(old_pid) && server.pid != old_pid
    begin
      Process.kill("QUIT", File.read(old_pid).to_i)
    rescue Errno::ENOENT, Errno::ESRCH
      # someone else did our job for us
    end
  end
end

after_fork do |server, worker|
  # Start up the database connection again in the worker
  if defined?(ActiveRecord::Base)
    ActiveRecord::Base.establish_connection
  end
  child_pid = server.config[:pid].sub(".pid", ".#{worker.nr}.pid")
  system("echo #{Process.pid} > #{child_pid}")
end

And if we look into the DB console, we'll see something like this. They've eaten most of the connections. (I had nothing but Unicorn running at the moment) To my mind there should have been 1 connection * 4 unicorns = 4 connections.

mysql> show full processlist;
+-----+----------+--------------------------------------------------+------------------------+---------+------+-------+-----------------------+
| Id  | User     | Host                                             | db                     | Command | Time | State | Info                  |
+-----+----------+--------------------------------------------------+------------------------+---------+------+-------+-----------------------+
|   2 | rdsadmin | localhost:31383                                  | NULL                   | Sleep   |    9 |       | NULL                  |
|  52 | level    | 212.100.140.42:50683                             | leveltravel_production | Query   |    0 | NULL  | show full processlist |
|  74 | level    | ip-10-55-10-151.eu-west-1.compute.internal:38197 | leveltravel_production | Sleep   |    5 |       | NULL                  |
|  75 | level    | ip-10-55-10-151.eu-west-1.compute.internal:38199 | leveltravel_production | Sleep   |    8 |       | NULL                  |
|  76 | level    | ip-10-55-10-151.eu-west-1.compute.internal:38201 | leveltravel_production | Sleep   |    8 |       | NULL                  |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ CUT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

| 157 | level    | ip-10-55-10-151.eu-west-1.compute.internal:38321 | leveltravel_production | Sleep   |  154 |       | NULL                  |
| 158 | level    | ip-10-55-10-151.eu-west-1.compute.internal:38322 | leveltravel_production | Sleep   |   17 |       | NULL                  |
| 159 | level    | ip-10-55-10-151.eu-west-1.compute.internal:38325 | leveltravel_production | Sleep   |   54 |       | NULL                  |
| 160 | level    | ip-10-55-10-151.eu-west-1.compute.internal:38326 | leveltravel_production | Sleep   |   54 |       | NULL                  |
| 161 | level    | ip-10-55-10-151.eu-west-1.compute.internal:38327 | leveltravel_production | Sleep   |   54 |       | NULL                  |
| 162 | level    | ip-10-55-10-151.eu-west-1.compute.internal:38329 | leveltravel_production | Sleep   |   42 |       | NULL                  |
+-----+----------+--------------------------------------------------+------------------------+---------+------+-------+-----------------------+
90 rows in set (0.15 sec)

You may also have a look at Issue #503 in sidekiq repository for the background of this problem https://github.com/mperham/sidekiq/issues/503

Alexander Randa Alexander Randa · Accepted Answer · 2012-11-14T20:05:00

You have run 4 unicorn process. It is the PROCESS, NOT THREAD.
Every process have 22 connections in pool. Totally they have 22*4 = 88 connections.
If you want have 4 connection for 4 worker process, you can set pool: 1 in database.yml

Unicorns eat up MySQL connections not respecting the pool size - Rails

1 Answers