async MySQL query in Future

[王雪松]

Hi. 

Currently, I am workiong on boosting the speed of my shiny app. In the app, I need to do a lot of queries and the database is very big as well. 

First I create the connection inside server.r, as below.

  #

  # Create connection to MySQL ####

  #

  print("Establishing connection to MySQL server...")

  t <- Sys.time()

  MySQL_con <- dbConnect.structdata();

  MySQL_con <- dbConnect.setExpliciteUtf8Mode(MySQL_con);

  print(sprintf("...done (%.2f sec)", as.numeric(difftime(Sys.time(),t,units = "secs"))))

  

  

Seconde I do query then,

getData.analysis <- function(

  con,

  data, 

  data_ids = NULL,

  load_data_elements = FALSE,

  query = NULL, 

  updateProgress = NULL) {

  print(" Creating futures for keywords...")

  print(con)

  t <- Sys.time()

  data$keywords <- future({

    con <- dbConnect.structdata();

    con <- dbConnect.setExpliciteUtf8Mode(con);

    x <-  encoding.data.frame(db.explore.keywords(con, query_db, count = 100))

    dbDisconnect(con); 

    x

  })

  print(sprintf("   ...done (%.2f sec)", as.numeric(difftime(Sys.time(),t,units = "secs"))))
}

Currently, it works in the way I need. But, I need to create a new database connection everytime. It is so expensive to create a connection everytime, since I will have a lot features to do same things.

How can you use a global connection for all, without creating it every time.

Best regards,

Song

[Ger Inberg]

Hi Song,

you might want to have a look at the pool package, it can hold a number of connections to your database so you don't have to connect every time.

See this recent blog: https://www.r-bloggers.com/pool-package-on-cran/

[王雪松]

Hi Ger Inberg.

Thank you for your help. I tried Pool wrapped by Future. Sadly, it did not work with error like:

Warning：Error in: corrupt mysql handle

I guess, future will create a new process(different pid)， and mysql connection or Pool is bound with a specific process. So when I call future, it does not know what is connection is, since it is in a new process. I may be wrong. 

But still, I find the old code create-and-closes connection many times, it seriously slow down the app. I gonna use Pool to fix this before I figure out how I can use connection in Future.

Best regards,

Song


在 2017年11月23日星期四 UTC+1上午4:41:22，Ger Inberg写道：

[Ger Inberg]

Hi Song, 

I haven't used futures in combination with pool, not sure why that's not working..

Maybe your application will speed up that much by using pool, that you don't need the async behaviour. Interested to hear what your results will be. Good luck.

best,Ger