Query extremely slow when fetching many rows

[renkun-ken]

DB Server: MySQL Server 5.7-15 on Fedora 24
Client: RMariaDB built against libmariadb-client-lgpl-dev on Ubuntu 16.04.3

When I query a table of more than 8M rows and 23 columns, dbReadTable() will hang forever, but querying with limit 100 will return in milliseconds or limit 100000 in 4 seconds while limit 1000000 in 400 seconds. To query the whole table, it will cost a ridiculous amount of time. But with v0.11-5 and former RMySQL, querying this very table will only cost less than a minute.

Following are tests of query the same table with different limit using each recent versions:

devtools::install_github("rstats-db/RMariaDB@v0.11-5"):

> library(DBI)
> con <- dbConnect(RMySQL::MySQL(), ...)
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 100"))
   user  system elapsed 
  0.000   0.000   0.003 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 1000"))
   user  system elapsed 
  0.008   0.000   0.010 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 10000"))
   user  system elapsed 
  0.056   0.004   0.065 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 100000"))
   user  system elapsed 
  0.696   0.008   0.721 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 1000000"))
   user  system elapsed 
  5.284   0.332   5.634 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES"))
   user  system elapsed 
 56.372   3.040  59.463 

devtools::install_github("rstats-db/RMariaDB@v0.11-6"):

> library(DBI)
> con <- dbConnect(RMariaDB::MariaDB(), ...)
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 100"))
   user  system elapsed 
  0.004   0.000   0.004 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 1000"))
   user  system elapsed 
  0.004   0.000   0.010 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 10000"))
   user  system elapsed 
  0.052   0.008   0.062 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 100000"))
   user  system elapsed 
  0.692   0.024   0.762 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 1000000"))
   user  system elapsed 
  5.788   0.224   6.103 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES"))
   user  system elapsed 
 59.632   2.532  62.270 

devtools::install_github("rstats-db/RMariaDB@v0.11-7"):

> library(DBI)
> con <- dbConnect(RMariaDB::MariaDB(), ...)
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 100"))
   user  system elapsed 
  0.000   0.000   0.005 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 1000"))
   user  system elapsed 
  0.008   0.000   0.010 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 10000"))
   user  system elapsed 
  0.056   0.000   0.065 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 100000"))
   user  system elapsed 
  0.680   0.024   0.756 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 1000000"))
   user  system elapsed 
  5.468   0.276   5.799 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES"))
   user  system elapsed 
 59.200   2.920  62.183 

devtools::install_github("rstats-db/RMariaDB@v0.11-8"):

> library(DBI)
> con <- dbConnect(RMariaDB::MariaDB(), ...)
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 100"))
   user  system elapsed 
  0.000   0.004   0.005 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 1000"))
   user  system elapsed 
  0.008   0.000   0.016 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 10000"))
   user  system elapsed 
  0.068   0.004   0.108 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 100000"))
   user  system elapsed 
  4.744   0.040   4.820 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES limit 1000000"))
   user  system elapsed 
423.176   0.448 423.683 
> system.time(data <- dbGetQuery(con, "select * from ASHAREEODPRICES"))
<hangs for more than 10 minutes>

[krlmlr]

Thanks, this and #60 gave me enough reason to turn off result prefetching. This means that we can't reliably use Connector/C until the fix hits the repos, which may take a while. Please open a new issue if problems persist.

[renkun-ken]

I test with the v0.11-9 against libmysqlclient-dev and the query performance is good now. Thanks!

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.