[R] RMysql Package 속도 최적화 방법

최근 shiny공부를 하다가 보니 Mysql 과 같은 DB를 사용하는 것에 대한 중요성을 느끼게 되었습니다.

 

왜냐하면 shiny와 같은 경우 페이지 작동마다 가벼운 동작을 해야하는데, read.table같은 기능을 이용할 경우

 

데이터가 커질수록 느려질수 밖에 없기 때문입니다.

 

그렇기 때문에 Mysql db를 이용하면  필요할 때만 빠르게 읽고, 쓰기가 가능해지게 됩니다.

 

그러나 Mysql db를 RMysql  package를 이용하여 배우게 되면, 속도에 대한 최적화를 하기가 힘들수 있습니다.

 

그래서 이번 포스팅은 Mysql db의 속도를 최적화하는 방법에 대해서 이야기해보도록 하겠습니다.

 

먼저 RMysql을 이용하기 위해서는 Mysql에 접근을 해야하는데, 이때 사용하는 package는 DBI라는 package입니다.

 

dbConnect,dbWriteTable을 이용해서 내가 가지고 있는 데이터를 Mysql db 로 이동시키도록 하겠습니다.

 

예시 데이터로 diamonds를 이용하도록 하겠습니다.

 

library(DBI)
library(RMySQL)
library(dplyr)
# DB연결
con<-dbConnect(
  MySQL(), #MySQL()은 RMySQL package에 있는 기능임
  dbname='dbname',
  host='localhost',
  username='username',
  password='password')
library(ggplot2)
data("diamonds")

# 일반적인 읽고 쓰기
time1=Sys.time()
test<-diamonds[diamonds$cut=='Good',]
time2=Sys.time()
case1=time2-time1

# DB에다 데이터를 넣고 이용하기
dbWriteTable(con,'diamonds',diamonds)
time1=Sys.time()
test<-dbGetQuery(con,'select * from diamonds where cut="Good"')
time2=Sys.time()
case2=time2-time1

# DB안에 있는 데이터를 tbl을 이용하여 다루기
test<-tbl(con,'diamonds')
time1=Sys.time()
test<-test[test$cut=='Good',]
time2=Sys.time()
case3=time2-time1

# DB안에 있는 데이터안에 필터를 적용할 부분을 indexing해서 진행
dbWriteTable(con,'diamonds1',diamonds)
dbGetQuery(con,'alter table diamonds1 modify cut varchar(10);')
dbGetQuery(con,'create index idx_cut on diamonds1 (cut);')
time1=Sys.time()
test<-dbGetQuery(con,'select * from diamonds1 where cut="Good"')
time2=Sys.time()
case4=time2-time1

 

다음과 같이 3개의 케이스를 확인하였습니다.

 

1. 일반적이게 read.table을 이용 후에 필터적용

2. db에다 내 데이터 넣고 db query이용해서 필터적용

3. db에 있는 데이터를 tbl을 통해 필터적용

4. db안에 있는 데이터에 필터할 부분을 indexing을 이용한 후에  db query이용해서 필터적용

 

했을경우 속도의 차이는 다음과 같습니다.

 

경우	반응속도 (등수) .sec
일반적이게 read.table을 이용 후에 필터적용	0.009344339 (2등)
db에다 내 데이터 넣고 db query이용해서 필터적용	0.03129005 (4등)
db에 있는 데이터를 tbl을 통해 필터적용	0.0003006458 (1등)
db안에 있는 데이터에 필터할 부분을 indexing을 이용한 후에  db query이용해서 필터적용	0.01259899 (3등)

 

속도를 보면 tbl을 이용하는 것이 빠른데, 데이터가 일단 커져도 tbl을 이용(3gb 이상의 데이터)하면 빠르게 되는 것을 확인하였고, 2등의 경우 데이터가 커지게 된다면 처음 데이터를 읽을 때 느려지는 시간을 생각을 하면 아마 꼴지가 될 수있을 것 같습니다.

 

---

시간 낭비를 한번 해보도록 하겠습니다.

 

diamond의 크기를 인위적으로 늘려서 (rbind 이용) 각 경우별로 얼마나 걸리는지 확인해 보도록 해보겠습니다.

 

그래프로 용량이 늘어날수록 원시적인 방법(read.table)과 Mysql db를 이용하는것 사이의 속도에 대해서 나타 내 보았습니다.

원시적인 방법의 경우 한번 작동할때마다 read.table을 진행해줘야하기 때문에 시간측정에 포함시켰고,

 

나머지 db에 추가하거나, indexing의 경우 한번 DB에서 진행을 하면 다음 동작에는 따로 안해도 되기때문에 포함 시키지 않았습니다.

 

그래프를 보게 되면  Case 4가 두 개가 나오는데, 데이터를 저장하고 indexing하고 바로 찾기를 한 경우(Case 4)와 indexing후에 시간 텀을 준다음 진행한 경우(Case 4-1)입니다.

 

실험결과 데이터를 db에 넣고 Indexing을 하든 read.table을 진행하든 비슷한 반응시간이 나왔고,

 

가장 빠른 경우는 tbl을 이용할 때가 가장 빠른 반응속도가 나왔습니다.

 

---

참고 : 실험의 경우 두번 정도 반복해서 얻은 결과입니다. 정확한 결과값을 확신하지 않습니다.

실험을 진행할때  한번에 For loop에서 작동을 하였기 때문에 결과값이 다를수 있습니다.

---

 

결과적으로 RMySQL package를 이용시에 tbl(dplyr package)을 이용하면 빠른 결과값을 얻을수 있을 것으로 보입니다.

 

 

---

유용하셨거나, 잘 보셧다면 주변 광고 한번씩만 클릭 부탁드립니다! 감사합니다!