[Backgroundrb-devel] Broken thread Safe connection Management on Mysql (Mysql too many connections errors)

[Renaud Morvan]

Hi,

There is an issue on socket connection with backgroundrb (rev 47) and 
mysql backend: after a certain number of job you reach the mysql 
connection limit as socket are never closed and nobody can connect to 
mysql anymore till you stop the backgroundrb daemon.

Already reported on:
http://rubyforge.org/pipermail/backgroundrb-devel/2006-July/000066.html, 
http://rubyforge.org/pipermail/backgroundrb-devel/2006-August/000169.html

It is quite easy to reproduce:
- just have a simple worker with a simple command using AR (Model.find I.E.)
- open a console and launch the work 100+ times, an exception will be 
raised 'too many connection'. The whole system won't be able to use 
mysql anymore as mysql socket are not closed

In my humble opinion there are two reasons for it:
- ActiveRecord::Base.allow_concurrency = true
That will make every thread open it's own socket to mysql
- Mysql adapter implementations (RAILS or gems) that close connection on 
Garbage connexion (using ObjectSpace.define_finalizer )
There is probably a memory leak on connections as the Proc  finalizer is 
  not be executed when worker thread is killed (= connections are not 
GC). In this case the only way to release the sockets is to close the 
backgroundrb daemon.

Those two phenomenons lead to an accumulation of opened socket till you 
reach the system limit, and when you reach it, nothing no new 
connections can be done on mysql anymore on the whole system. (this is 
observable with a netstat |grep mysql)

This can be workaround by setting ActiveRecord::Base.allow_concurrency 
to false (do not see the risks but there are some probably) or by 
manually closing the connections in worker before termination the tread 
(in this case you have to use job_ctrl to avoid thread to be killed 
before having closed the connections)

Cronned restart of backgroundrb also fix this :)

Anyway maybe the problem is in the design of Backgroundrb itself, that 
should use a pool of thread and not create as many thread as there are 
works, in this case the pool of thread will use a finite number of 
socket, whose connections will be reused.

Otherwise it would be good to find the memory leak (= why killing thread 
don't make connections to be GC, or more precisely why 
ObjectSpace.define_finalizer does not work in this case) but it seems to 
be a bit tricky.( some rubyist seems to have the same kind of problem 
with thread and ObjectSpace.define_finalizer 
http://blade.nagaokaut.ac.jp/cgi-bin/scat.rb/ruby/ruby-core/291 or 
http://blade.nagaokaut.ac.jp/cgi-bin/scat.rb/ruby/ruby-talk/145913)

Hope this help, if someone has better solutions please talk :)

Renaud Morvan
http://exalead.feedback20.com