Backgroundrb devel - Dec 2006 - Creating another database connection for large mysql import?

I''m using backgroundrb to periodically download a large file via ftp
from a
remote location and then import it into the database.  To perform the
import, I was using the following:

ActiveRecord::Base.connection.execute(%{load data infile ...;})

although on a file with 2.5 million records, this can take 5 minutes, which
seems to tie up my rails application while this executes (even though
it''s
being run from a backgroundrb process).  I figured this was happening
because I''m using the same database connection that the rest of my
rails app
is running on.. I then tried using the following:

Importer.establish_connection(:development)
Importer.connection.execute(%{load data infile ... ;})

where Importer is a dummy class I created just so I could try to establish a
database connection separate from the rest of my rails classes.  However,
this seems to produce the same behavior as when I use ActiveRecord::
Base.connection.execute..

Does anyone have any suggestions on how I might be able to resolve this?

Also, I read a message on the list that an upcoming version of backgroundrb
will have the ability to automatically reload a worker if running in
development mode and the worker file changes.. Just wondering how to enable
this, since I''ve been stopping/restarting the server to get the file
reloaded.

Thanks for any help, and also a big thanks to Ezra (and any other
contributors) for all the hard work that''s been put into backgroundrb,
it''s
truly appreciated!

Mike
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://rubyforge.org/pipermail/backgroundrb-devel/attachments/20061218/8c2f1f3c/attachment.html

On Dec 18, 2006, at 2:48 PM, Mike Garey wrote:
> I''m using backgroundrb to periodically download a large file via  
> ftp from a remote location and then import it into the database.   
> To perform the import, I was using the following:
>
> ActiveRecord::Base.connection.execute (%{load data infile ...;})
>
> although on a file with 2.5 million records, this can take 5  
> minutes, which seems to tie up my rails application while this  
> executes (even though it''s being run from a backgroundrb process).
> I figured this was happening because I''m using the same database  
> connection that the rest of my rails app is running on.. I then  
> tried using the following:
>
> Importer.establish_connection(:development)
> Importer.connection.execute(%{load data infile ... ;})
>
> where Importer is a dummy class I created just so I could try to  
> establish a database connection separate from the rest of my rails  
> classes.  However, this seems to produce the same behavior as when  
> I use ActiveRecord:: Base.connection.execute..
>
> Does anyone have any suggestions on how I might be able to resolve  
> this?
>
> Also, I read a message on the list that an upcoming version of  
> backgroundrb will have the ability to automatically reload a worker  
> if running in development mode and the worker file changes.. Just  
> wondering how to enable this, since I''ve been stopping/restarting
> the server to get the file reloaded.
>
> Thanks for any help, and also a big thanks to Ezra (and any other  
> contributors) for all the hard work that''s been put into  
> backgroundrb, it''s truly appreciated!
>
> Mike

Hey Mike-

	Can you show me exactly how you are calling the worker method that  
does the 5 minute task? The do_work method is run asyncronously when  
the worker is first created but other methods you define in your  
worker will block rails if you call them directly.  In the 0.2.1  
release you can use work_thread to call your worker method in a  
thread so it spins off and works in the background while rails can  
continue on its way.

so
worker = MiddleMan.worker(session[:key)
worker.work_thread(:some_method)


Cheers-
-- Ezra Zygmuntowicz 
-- Lead Rails Evangelist
-- ez at engineyard.com
-- Engine Yard, Serious Rails Hosting
-- (866) 518-YARD (9273)

I''m calling my import method from do_work, so this should be run
asynchronously as well right?  I also tried your method, but I receive the
following error:

    MiddleMan.new_worker(:class => :download_worker, :args => file,
:job_key
=> :download_worker)
    worker = MiddleMan.worker(:download_worker)
    worker.work_thread(:import_file)

NoMethodError (undefined method `[]'' for :import_file:Symbol):
    (drbunix:///tmp/backgroundrbunix_localhost_2000)
(drbunix:///tmp/backgroundrb.99462/download_worker_download_worker_0_0.678415183889324)
/usr/local/www/data-dist/vhosts/mysite/krad/vendor/plugins/backgroundrb/server/lib/backgroundrb/worker.rb:44:in
`work_thread''
    .//app/controllers/products_controller.rb:291:in `import_data_file''

the line that causes the error is:

    worker.work_thread(:import_file)

Are you sure that it isn''t the single database connection that may be
causing rails to stall?  Thanks,

Mike



On 12/18/06, Ezra Zygmuntowicz <ezmobius at gmail.com>
wrote:
>
>
> On Dec 18, 2006, at 2:48 PM, Mike Garey wrote:
>
> > I''m using backgroundrb to periodically download a large file
via
> > ftp from a remote location and then import it into the database.
> > To perform the import, I was using the following:
> >
> > ActiveRecord::Base.connection.execute (%{load data infile ...;})
> >
> > although on a file with 2.5 million records, this can take 5
> > minutes, which seems to tie up my rails application while this
> > executes (even though it''s being run from a backgroundrb
process).
> > I figured this was happening because I''m using the same
database
> > connection that the rest of my rails app is running on.. I then
> > tried using the following:
> >
> > Importer.establish_connection(:development)
> > Importer.connection.execute(%{load data infile ... ;})
> >
> > where Importer is a dummy class I created just so I could try to
> > establish a database connection separate from the rest of my rails
> > classes.  However, this seems to produce the same behavior as when
> > I use ActiveRecord:: Base.connection.execute..
> >
> > Does anyone have any suggestions on how I might be able to resolve
> > this?
> >
> > Also, I read a message on the list that an upcoming version of
> > backgroundrb will have the ability to automatically reload a worker
> > if running in development mode and the worker file changes.. Just
> > wondering how to enable this, since I''ve been
stopping/restarting
> > the server to get the file reloaded.
> >
> > Thanks for any help, and also a big thanks to Ezra (and any other
> > contributors) for all the hard work that''s been put into
> > backgroundrb, it''s truly appreciated!
> >
> > Mike
>
>
> Hey Mike-
>
>         Can you show me exactly how you are calling the worker method that
> does the 5 minute task? The do_work method is run asyncronously when
> the worker is first created but other methods you define in your
> worker will block rails if you call them directly.  In the 0.2.1
> release you can use work_thread to call your worker method in a
> thread so it spins off and works in the background while rails can
> continue on its way.
>
> so
> worker = MiddleMan.worker(session[:key)
> worker.work_thread(:some_method)
>
>
> Cheers-
> -- Ezra Zygmuntowicz
> -- Lead Rails Evangelist
> -- ez at engineyard.com
> -- Engine Yard, Serious Rails Hosting
> -- (866) 518-YARD (9273)
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://rubyforge.org/pipermail/backgroundrb-devel/attachments/20061218/d38fc055/attachment-0001.html

With rails, you should get a single db connection per process (by default).
That a database connection in one process could
tie up the database connection in another, separate process doesn''t
sound
possible.

It sounds more likely to me that the db loading you are doing is resulting
in locks in the database that are blocking other requests from doing their
work.
This would be happening in the db, and would not be related to the number of
connections to the database.

The locking behavior of MySQL varies based on the table type used, in
particular MyISAM tables used table-level locking (which
can easily bite you) while InnoDB tables use row-level locks.
see: http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html

Another possibility is that the import you are doing is monopolizing the
DB''s cpu/io capacity and/or is filling the bandwidth between your app
server
and db server.

Mason

On 12/18/06, Mike Garey <backgroundrb at gmail.com>
wrote:
>
> I''m calling my import method from do_work, so this should be run
> asynchronously as well right?  I also tried your method, but I receive the
> following error:
>
>     MiddleMan.new_worker(:class => :download_worker, :args => file,
> :job_key => :download_worker)
>     worker = MiddleMan.worker(:download_worker)
>     worker.work_thread(:import_file)
>
> NoMethodError (undefined method `[]'' for :import_file:Symbol):
>     (drbunix:///tmp/backgroundrbunix_localhost_2000)
>
(drbunix:///tmp/backgroundrb.99462/download_worker_download_worker_0_0.678415183889324)
>
/usr/local/www/data-dist/vhosts/mysite/krad/vendor/plugins/backgroundrb/server/lib/backgroundrb/worker.rb:44:in
> `work_thread''
>     .//app/controllers/products_controller.rb:291:in
`import_data_file''
>
> the line that causes the error is:
>
>     worker.work_thread(:import_file)
>
> Are you sure that it isn''t the single database connection that may
be
> causing rails to stall?  Thanks,
>
> Mike
>
>
>
> On 12/18/06, Ezra Zygmuntowicz <ezmobius at gmail.com> wrote:
> >
> >
> > On Dec 18, 2006, at 2:48 PM, Mike Garey wrote:
> >
> > > I''m using backgroundrb to periodically download a large
file via
> > > ftp from a remote location and then import it into the database.
> > > To perform the import, I was using the following:
> > >
> > > ActiveRecord::Base.connection.execute (%{load data infile ...;})
> > >
> > > although on a file with 2.5 million records, this can take 5
> > > minutes, which seems to tie up my rails application while this
> > > executes (even though it''s being run from a backgroundrb
process).
> > > I figured this was happening because I''m using the same
database
> > > connection that the rest of my rails app is running on.. I then
> > > tried using the following:
> > >
> > > Importer.establish_connection(:development)
> > > Importer.connection.execute(%{load data infile ... ;})
> > >
> > > where Importer is a dummy class I created just so I could try to
> > > establish a database connection separate from the rest of my
rails
> > > classes.  However, this seems to produce the same behavior as
when
> > > I use ActiveRecord:: Base.connection.execute..
> > >
> > > Does anyone have any suggestions on how I might be able to
resolve
> > > this?
> > >
> > > Also, I read a message on the list that an upcoming version of
> > > backgroundrb will have the ability to automatically reload a
worker
> > > if running in development mode and the worker file changes.. Just
> > > wondering how to enable this, since I''ve been
stopping/restarting
> > > the server to get the file reloaded.
> > >
> > > Thanks for any help, and also a big thanks to Ezra (and any other
> > > contributors) for all the hard work that''s been put into
> > > backgroundrb, it''s truly appreciated!
> > >
> > > Mike
> >
> >
> > Hey Mike-
> >
> >         Can you show me exactly how you are calling the worker method
> > that
> > does the 5 minute task? The do_work method is run asyncronously when
> > the worker is first created but other methods you define in your
> > worker will block rails if you call them directly.  In the 0.2.1
> > release you can use work_thread to call your worker method in a
> > thread so it spins off and works in the background while rails can
> > continue on its way.
> >
> > so
> > worker = MiddleMan.worker(session[:key)
> > worker.work_thread(:some_method)
> >
> >
> > Cheers-
> > -- Ezra Zygmuntowicz
> > -- Lead Rails Evangelist
> > -- ez at engineyard.com
> > -- Engine Yard, Serious Rails Hosting
> > -- (866) 518-YARD (9273)
> >
> >
> >
>
> _______________________________________________
> 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/20061219/3ca864b9/attachment.html