[Backgroundrb-devel] Creating another database connection for large mysql import?

Mason Hale masonhale at gmail.com
Tue Dec 19 08:01:49 EST 2006


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 


More information about the Backgroundrb-devel mailing list