Holding Onto DB Connections

[Jim Mulholland]

I setup a Rufus schedule inside a Ruby Daemon similar to what was done
in this blog post:

http://www.inventivelabs.com.au/weblog/post/scheduling-periodic-tasks-in-a-rails-application

My scheduled task runs great, but once the process is complete it does
not let go of the database connection.

Since the process is running every minute, I run out of MySQL database
connections very quickly resulting in a MySql "Too many connections"
error.

Does anybody have an idea about why this could be happening?

Abridged version of the code looks like this:

**********************************************************

Daemons.run_proc(
'schedule',
:dir_mode => :normal,
:dir => File.join(APP_DIR, 'log'),
:multiple => false,
:backtrace => true,
:monitor => true,
:log_output => true
) do

# Daemonising changes the pwd to /, so we need to switch
# back to RAILS_ROOT.
Dir.chdir(APP_DIR)

# Load our Rails environment.
require File.join('config', 'environment')

begin
# Initialise the Rufus scheduler object.
require 'rufus/scheduler'

scheduler = Rufus::Scheduler.start_new

scheduler.start

# Check every minute to see if any reminders need to be sent
scheduler.schedule_every('1m') do
RAILS_DEFAULT_LOGGER.info "CHECK FOR AND SEND REMINDERS"
ActivityReminder.send_reminders
end

# Tell the scheduler to perform these jobs until the
# process is stopped.
scheduler.join
rescue => e
RAILS_DEFAULT_LOGGER.warn "Exception in schedule: #{e.inspect}"
exit
end
end

**********************************************************

[John Mettraux]

On Fri, Sep 12, 2008 at 2:08 PM, Jim Mulholland
<jim.mul...@gmail.com> wrote:
>
> I setup a Rufus schedule inside a Ruby Daemon similar to what was done
> in this blog post:
>
> http://www.inventivelabs.com.au/weblog/post/scheduling-periodic-tasks-in-a-rails-application
>
> My scheduled task runs great, but once the process is complete it does
> not let go of the database connection.
>
> Since the process is running every minute, I run out of MySQL database
> connections very quickly resulting in a MySql "Too many connections"
> error.
>
> Does anybody have an idea about why this could be happening?

Hi Jim,

no way to recycle / reuse the connection instead of setting up a new
one each time ?

It should be in your send_reminder() method.

Best regards,

--
John Mettraux - http://jmettraux.wordpress.com

[Jim Mulholland]

Thanks for the response, John! I'm not sure how I would reuse a
connection within the send_reminder() method.

I added a "remove_connection" in my scheduler block which seems to
have done the trick.

Does anybody see an issue with this solution?

# Check every minute to see if any reminders need to be sent
scheduler.schedule_every('1m') do

ActivityReminder.send_reminders
ActiveRecord::Base.remove_connection
end

On Sep 12, 12:11 am, "John Mettraux" <jmettr...@openwfe.org> wrote:
> On Fri, Sep 12, 2008 at 2:08 PM, Jim Mulholland
>

> <jim.mulholl...@gmail.com> wrote:
>
> > I setup a Rufus schedule inside a Ruby Daemon similar to what was done
> > in this blog post:
>

> >http://www.inventivelabs.com.au/weblog/post/scheduling-periodic-tasks...

[John Mettraux]

On Sat, Sep 13, 2008 at 12:27 AM, Jim Mulholland
<jim.mul...@gmail.com> wrote:
>
> I added a "remove_connection" in my scheduler block which seems to
> have done the trick.
>
> Does anybody see an issue with this solution?
>
>
> # Check every minute to see if any reminders need to be sent
> scheduler.schedule_every('1m') do
> ActivityReminder.send_reminders
> ActiveRecord::Base.remove_connection
> end

Hi Jim,

I guess you're using ActiveRecord 2.1.0. I've taken a look at
#remove_connection :

http://github.com/rails/rails/tree/v2.1.0/activerecord/lib/active_record/connection_adapters/abstract/connection_specification.rb#L24-38

especially

http://github.com/rails/rails/tree/v2.1.0/activerecord/lib/active_record/connection_adapters/abstract/connection_specification.rb#L26

Seems like it tries to have 1 connection per thread (it uses the
thread's object id to map the connection).

The rufus-scheduler triggers the jobs in their own threads, so as not
to block the whole scheduler. I think it matches your description of
the symptoms (too many open connections).

Thanks for finding this out, it's not a direct rufus-scheduler issue,
but I should document it anyway.

[Jim Mulholland]

Hi John,

Yes, I am using AR 2.1.0.

Thanks for taking a deeper look into this. So I guess as long as my
processes take < 1 minute (or whatever the time is I specify) I should
be okay. I should probably test to see what happens if the process
goes longer than the specified interval time.

Thanks again!

Jim

On Sep 12, 11:24 am, "John Mettraux" <jmettr...@openwfe.org> wrote:
> On Sat, Sep 13, 2008 at 12:27 AM, Jim Mulholland
>

> <jim.mulholl...@gmail.com> wrote:
>
> > I added a "remove_connection" in my scheduler block which seems to
> > have done the trick.
>
> > Does anybody see an issue with this solution?
>
> >    # Check every minute to see if any reminders need to be sent
> >    scheduler.schedule_every('1m') do
> >      ActivityReminder.send_reminders
> >      ActiveRecord::Base.remove_connection
> >    end
>
> Hi Jim,
>
> I guess you're using ActiveRecord 2.1.0. I've taken a look at
> #remove_connection :
>

> http://github.com/rails/rails/tree/v2.1.0/activerecord/lib/active_rec...
>
> especially
>
> http://github.com/rails/rails/tree/v2.1.0/activerecord/lib/active_rec...

[John Mettraux]

On Sun, Sep 14, 2008 at 10:19 PM, Jim Mulholland
<jim.mul...@gmail.com> wrote:
>
> Hi John,
>
> Yes, I am using AR 2.1.0.
>
> Thanks for taking a deeper look into this. So I guess as long as my
> processes take < 1 minute (or whatever the time is I specify) I should
> be okay. I should probably test to see what happens if the process
> goes longer than the specified interval time.

Hi Jim,

I think your solution is right, it seems that ActiveRecord is creating
a connection for each of the threads the scheduler spawns that does
work with the DB. So releasing the connection once the job is done is
a good thing.

http://jmettraux.wordpress.com/2008/09/14/the-scheduler-and-the-active_record/

This has nothing to do with the time spent working on the job. Go on
with your solution. Of course having tests is a must.

[Aaron]

What happens if you have multiple scheduled Rails tasks? How will it
know which connection to kill? It might be better to consider
disabling connection pooling in your Rufus environment.

On Sep 14, 8:56 am, "John Mettraux" <jmettr...@openwfe.org> wrote:
> On Sun, Sep 14, 2008 at 10:19 PM, Jim Mulholland
>

> <jim.mulholl...@gmail.com> wrote:
>
> > Hi John,
>
> > Yes, I am using AR 2.1.0.
>
> > Thanks for taking a deeper look into this.  So I guess as long as my
> > processes take < 1 minute (or whatever the time is I specify) I should
> > be okay.  I should probably test to see what happens if the process
> > goes longer than the specified interval time.
>
> Hi Jim,
>
> I think your solution is right, it seems that ActiveRecord is creating
> a connection for each of the threads the scheduler spawns that does
> work with the DB. So releasing the connection once the job is done is
> a good thing.
>

> http://jmettraux.wordpress.com/2008/09/14/the-scheduler-and-the-activ...