Renaud Morvan
2006-Oct-24 22:58 UTC
[Backgroundrb-devel] Broken thread Safe connection Management on Mysql (Mysql too many connections errors)
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
Michael D''Auria
2006-Oct-25 02:07 UTC
[Backgroundrb-devel] Broken thread Safe connection Management on Mysql (Mysql too many connections errors)
If you call this within the worker: ActiveRecord::Base.connection.disconect! It will close it''s connection to the DB. Just make sure you call it after it''s done :) .: Michael :. On 10/24/06, Renaud Morvan <renaud.morvan at feedback20.com> wrote:> > 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 > _______________________________________________ > Backgroundrb-devel mailing list > Backgroundrb-devel at rubyforge.org > http://rubyforge.org/mailman/listinfo/backgroundrb-devel >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://rubyforge.org/pipermail/backgroundrb-devel/attachments/20061024/3792aa5d/attachment-0001.html