[R] RMySQL query: why result takes so much memory in R ?

[Duncan Murdoch]

Christoph Lehmann wrote:
> Hi
> I just started with RMySQL. I have a database with roughly 12 millions 
> rows/records and 8 columns/fields.
> 
>  From all 12 millions of records I want to import 3 fields only.
> The fields are specified as:id int(11), group char(15), measurement 
> float(4,2).
> Why does this take > 1G RAM? I run R on suse linux, with 1G RAM and with 
> the code below it even fills the whole 1G of swap. I just don't 
> understand how 12e6 * 3 can fill such a huge range of RAM? Thanks for 
> clarification and potential solutions.

Those fields are each 8 or 20 bytes in size, so you're talking 12e6 
times 36 or about nearly half a Gig for each copy.  Presumably the code 
is storing more than one or two copies of the data.

Why don't you use fetch() to get your records in more manageable chunks?

Duncan Murdoch

> 
> 
> ## my code
> library(RMySQL)
> drv <- dbDriver("MySQL")
> ch <- dbConnect(drv,dbname="testdb",
>                 user="root",password="mysql")
> testdb <- dbGetQuery(ch,
>        "select id, group, measurement from mydata")
> dbDisconnect(ch)
> dbUnloadDriver(drv)
> 
> ## end of my code
> 
> Cheers
> Christoph
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! 
> http://www.R-project.org/posting-guide.html