Slow writing data to MySQL database hosted on Google Cloud

[ben519]

I recently created a database using Google Cloud MySQL (2nd generation) and have been running into some performance issues trying to write data to the database via RMariaDB. Observe the following...

# Connect to database using MariaDB driver (slow)
mycon <- DBI::dbConnect(
  drv = RMariaDB::MariaDB(),
  dbname = "mydb", 
  host = "11.111.111.111", 
  user = "foo",
  password = ""
)

# Write table 'iris'
system.time(RMariaDB::dbWriteTable(conn = mycon, name = "iris", value = iris, row.names = FALSE, overwrite = TRUE))
# 9.515 seconds

RMariaDB::dbDisconnect(mycon)

# Connect to database using MySQL driver (fast)
mycon <- DBI::dbConnect(
  drv = DBI::dbDriver("MySQL"),
  dbname = "mydb", 
  host = "11.111.111.111", 
  user = "foo",
  password = ""
)

# Write table 'iris'
system.time(RMariaDB::dbWriteTable(conn = mycon, name = "iris", value = iris, row.names = FALSE, overwrite = TRUE))
# 0.441 seconds

# Attempt 1 to append data to table
system.time(RMariaDB::dbAppendTable(conn = mycon, name = "iris", value = iris))
# Error in .local(conn, statement, ...) : 
#   unused argument (param = unname(as.list(value)))
# Timing stopped at: 0.005 0 0.005

# Attemp 2 to append data to table, using RMySQL
system.time(RMySQL::dbWriteTable(conn = mycon, name = "iris", value = iris, row.names = FALSE, overwrite = FALSE, append = TRUE))
# 0.206 seconds

My questions are

1. Should it be possible to write data to a MySQL database on Google Cloud in a reasonable amount of time? (Perhaps I am trying to do something that was not intended to be done.)
2. As shown by the error above, I cannot append data to a table using RMariaDB with a MySQL driver. Is this a bug?

For now I am stuck using RMySQL :/

Thanks, I really appreciate all the hard work done on this package!

R version 3.5.1 (2018-07-02)
Platform: x86_64-apple-darwin15.6.0 (64-bit)
Running under: macOS High Sierra 10.13.5
RMariaDB_1.0.6

[m-saenger]

I ran a script with a DBI::dbWrite statement for different number of records n.records. Once from my local machine (macOS) and once from the Ubuntu 16.04 server hosted at Digitalocean. Target was a MySQL database (InnoDB engine) hosted at Digitalocean.

Using a RMariaDB() connection results in a much slower writing speed for n.records > 10^3 on Ubuntu. On macOS performance of RMariaDB() is poor even for low n.records.

Results Mac macOS

n.records time.maria time.mysql
1: 1e+00      0.669      0.216
2: 1e+01      0.647      0.242
3: 1e+02      2.680      0.238
4: 1e+03     23.017      0.413
5: 1e+04    236.299      0.507
6: 1e+05   2196.433      2.191

Results Ubuntu 16.04:

n.records time.maria time.mysql
1: 1e+00      0.130      0.038
2: 1e+01      0.119      0.110
3: 1e+02      0.204      0.039
4: 1e+03      0.124      0.119
5: 1e+04      0.832      0.189
6: 1e+05      7.183      0.996
7: 1e+06     71.885      9.135
8: 1e+07    724.093    122.916

Let me know if you need further information.

Best, Matt

Script (not a reprex):

library(DBI)
library(RMariaDB)
library(RMySQL)
library(data.table)

n.records <- 10^(0:5)
tbl <- "test"
rmysql.settingsfile <- path.expand("~/.my.cnf")

l <- lapply(n.records, function(n){
  df <- data.frame(x = 1:n, y = rep("a", n), z = seq(Sys.time(), Sys.time() - 3600, length.out = n))
  
  time.maria <- system.time({
    conn <- dbConnect(RMariaDB::MariaDB(), default.file = rmysql.settingsfile, group = "dbadmin")
    dbWriteTable(conn, tbl, df, overwrite = T, row.names = F)
    dbDisconnect(conn)
  })
  print(time.maria)
  time.mysql <- system.time({
    conn <- dbConnect(RMySQL::MySQL(), default.file = rmysql.settingsfile, group = "dbadmin")
    dbWriteTable(conn, tbl, df, overwrite = T, row.names = F)
    dbDisconnect(conn)
  })
  print(time.mysql)
  data.table(n, time.maria = time.maria[3], time.mysql = time.mysql[3])
})
dt <- rbindlist(l)
print(dt)

macOS (local):

R version 3.5.0 (2018-04-23)
Platform: x86_64-apple-darwin15.6.0 (64-bit)
Running under: macOS High Sierra 10.13.6
RMariaDB_1.0.6
DBI_1.0.0

mariadbClientLibraryVersions()
10.2.9 10.2.9 
100209 100209 

Ubuntu

R version 3.4.4 (2018-03-15)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 16.04.4 LTS
RMariaDB_1.0.6
RMySQL_0.10.15
DBI_1.0.0

mariadbClientLibraryVersions()
5.7.23 5.7.23 
 50723  50723 

[krlmlr]

Thanks. dbAppendTable() should be fixed now in the dev version of DBI. In your tests, OS X seems to run with MariaDB libraries, Ubuntu with MySQL. Can you confirm? How do the results look like with dbAppendTable() ?

To the original question: This really should work faster on RMariaDB.

[ben519]

@krlmlr thanks for the response. The timings in my post are still accurate. Do you have any suggestions on how I can debug this issue or make it easier for you to help me diagnose the problem?

[m-saenger]

Timings from my previous post are still valid, too. I used the dev version:

Package: DBI
Version: 1.0.0.9000

dbWriteTable and dbAppendTable produce similar results

OS X seems to run with MariaDB libraries, Ubuntu with MySQL.

I don't quite understand what you mean with that. Is it what mariadbClientLibraryVersions() shows? I ran the exact same script both on OS X and Ubuntu.

Happy to help you further.

Best, Matt

[krlmlr]

It's about the database connector -- MySQL and MariaDB have different system libraries ("Connector-C"), both of which can be used with RMariaDB. Maybe this explains the difference?

I'll try replicating this on my machine with both libraries.

[krlmlr]

Do the timing differences also occur with a local or on-premises database?

[ben519]

The timing differences don't occur for me using a local database (although I have mariadb installed on my laptop, not mysql).

[krlmlr]

Thanks. Is the local CPU busy when the queries are run against GCE?

[ben519]

No noticeable increase in CPU load while the queries are running against GCE.

[rspreafico-vir]

Confirming huge performance difference between RMySQL and RMariaDB

[mabe0033]

I have seen huge performance differences, too.

AFAICS, for writing a data.frame, RMySQL writes a temporary file and uses the SQL statement "LOAD DATA LOCAL INFILE" (which is recommended for performance reasons) whereas RMariaDB (after splitting the 'write' task into a 'create' and 'append' task) constructs an "INSERT" statement in the 'append' subtask (while defaulting to sqlAppendTableTemplate in DBI).

Maybe, the behaviour of RMySQL should be adopted in this situation (writing or appending a data.frame).