R Multithreaded database access

Asked Aug 23 '12 at 14:36

Active Aug 24 '12 at 09:39

Viewed 781 times

I have a a bunch of computation that needs to be done in parallel. In this computation the database is accessed through one global channel. So it is something like this:

library(doMC)
registerDoMC(2)
channel <<- connect_to_database()
results <- foreach(i=indexes) %dopar% {
    data <- get_data_from_db(channel)
    result <- do_computations(data)
}

But them I get this error:

task 1 failed - "cannot open the connection"

The reason, very probably is because the threads are accessing the channel simultaneously. Is there a way to put a mutex around the get_data_from_db? Or create more connections and check if there is a free one before accessing it? Any suggestions?

Thanks a lot!

---------------Edit-------------- Now I am creating one connection for each index. But I'm getting this error now:

task 1 failed - "expired MySQLConnection"

library(doMC)
registerDoMC(2)
channel<<-list()
for(i=indexes){
   channel[[i]] <-connect_to_database(...)
}

results <- foreach(i=indexes) %dopar% {
    data <- get_data_from_db(channel[[ind]])
    result <- do_computations(data)
}

edited Aug 24 '12 at 09:39

asked Aug 23 '12 at 14:36

RodrigoReis

3

Why can't you simply let each worker create a connection to the DB? – Joshua Ulrich Aug 23 '12 at 14:46
Yeah, I'm pretty sure the `channel` needs to be opened by each of the workers. The code above should work serially with `%do%`, but not in parallel. – BenBarnes Aug 23 '12 at 14:58
1

Or get all the data in one query then send pieces to multiple workers? – Justin Aug 23 '12 at 15:01
I would like to reuse existing connections to gain time. – RodrigoReis Aug 23 '12 at 15:15
2

Reusing connections isn't going to save much (any?) time. – Joshua Ulrich Aug 23 '12 at 15:34
Thanks for the answers. Each time I connect to the db, it takes around 3-4 seconds. Now I am creating global channels, but the second time I try to execute the loop, it says the connection has expired. But if I use each channel individually, it works. – RodrigoReis Aug 24 '12 at 07:47

R Multithreaded database access

0 Answers0