CODEWITHSUNDEEP
mysqlruby-on-railsruby
Donald Plummer
Donald Plummer

Reputation: 440

Rails MySQL Too Many Connections

I've got a Rails 2.3 app that is keeping too many MySQL connections open. After less than a day (at ~400rpm) one process had 83 ESTABLISHED connections to the two mysql servers we use.

We're using the mysql2 gem (0.2.18), and the mysql client is: mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1.

How can I troubleshoot where these leaks are happening? In our testing, we're never able to leak connections, its only in production.

In MySQL, we can run show processlist; to see the open connections. On the app server, we can count the number of connections per pid with sudo netstat -ntp | grep 3306 | grep ESTABLISHED | awk '{print $7}' | sort | uniq -c | sort -n.

Upvotes: 13

Views: 7716

Answers (4)

Christian
Christian

Reputation: 638

I was getting the Too many connections error, because I used establish_connection for accessing other databases in multiple models.

I had these models

class InternetReference < ActiveRecord::Base
  establish_connection :db_webserver
end

class InternetEmployee < ActiveRecord::Base
  establish_connection :db_webserver
end

The solution is to open the connection in an abstract model and inherit from this model:

class AppsWebserver < ActiveRecord::Base
  self.abstract_class = true
  establish_connection :apps_webserver
end

class InternetReference < AppsWebserver
end

class InternetEmployee < AppsWebserver
end

Now the connections are handled correctly by Rails.

Upvotes: 0

Rene Wooller
Rene Wooller

Reputation: 1147

For what it's worth, the same problem was occurring on our staging server - it was reaching the max_connections number of connections to mysql. I found that running the service directly from the command line rather than using the start up script got around the issue somehow.

I haven't yet found out what it was in the start up script that was causing the problem.

Upvotes: 0

Donald Plummer
Donald Plummer

Reputation: 440

I fixed this by adding "wait_timeout: 300" to our database.yml. While that does close the unused mysql connections, it doesn't explain where they came from.

Upvotes: 7

wless1
wless1

Reputation: 3549

One random idea: fork the mysql2 gem, add some debugging into Mysql2::Client#initialize, and run your app as normal. You could print a few lines of the stack when the client is initialized, and track down who's causing the leak.

Upvotes: 2

Related Questions