I have a 7 giga confidential dataset which I want to use for a machine learning application.

I tried :

Every package recommanded for efficient dataset management in R like :

  • data.table,
  • ff
  • and sqldf with no success.

Data.table needs to load all the data in the memory from what I read, so it's obvious that it will not work since my computer has only 4g RAM. Ff leads to a memory error too.

So I decided to turn to sgdb and I tried :

  • Mysql which managed to load my dataset in 2 hours and 21'. Then I began my requests (I have a few requests to do to prepare my data before I export a smaller set in R for machine learning application), and then I had to wait for hours before I got the following message "The total number of locks exceeds the lock table size" (my request was just an update to extract the month from a date for each tuple).
  • I read that postgre was similar to mysql in performance so I didn't try
  • I read that redis was really performant but not at all adapted to massive importation like I want to do here so I didn't try
  • I tried mongoDb, the nosql upraising solution that I heard everywhere about. Not only I find rather disturbing that mongoimport is so limited in options (I had to change all semi-colon in commas using sed before I can import the data), but It seems to be less performant that mysql since I launched the loading yesterday and it is still running.

What I can't try : data are confidential so I don't really want to rent some space on Azure or Amazon clouding solution. I am not sure that it is that big that I have to turn to Hadoop solution but maybe I am wrong about that.

Is there an open-source performant solution that I didn't try that you would recommend to perform some sql-like requests on a biggish dataset ?

Edit : Some more details about what I want to do with these data for you to visualize. These are events with a timestamp and a geolocalisation. I have 8 billions of lines. One example of what I want to do :

  • standardize series identified by geolocalisation (I need to compute mean grouping by geolocalisation for example),
  • compute average count of events by type of season, day... (usual group by sql request)...


As a beginning of answer for those who have limited hardware like me, rSQLite seems to be a possibility. I am still interested in other people experiences.

  • $\begingroup$ I suspect the answer to this question will depend somewhat on the analysis method you're hoping to use; so more detail there would be helpful. $\endgroup$ – conjectures Jun 22 '15 at 15:07
  • $\begingroup$ Ok I gave some examples, thank you for your interest @conjectures $\endgroup$ – Stéphanie C Jun 22 '15 at 15:27
  • $\begingroup$ out of interest, with mongodb did you try using ensureIndex: tutorialspoint.com/mongodb/mongodb_indexing.htm (the indexing process itself takes a while, but speeds up queries afterwards) $\endgroup$ – conjectures Jun 22 '15 at 15:43
  • $\begingroup$ also the second bullet is totally possible via 1 line at a time reading of file and adding a number to whichever groups the line belongs to and corresponding group counts. $\endgroup$ – conjectures Jun 22 '15 at 15:45
  • 1
    $\begingroup$ Why not buy more RAM? $\endgroup$ – reinierpost Jun 24 '15 at 9:37

analyzing 8 billion lines on a 4gb computer is pretty silly, but you can try


| improve this answer | |
  • $\begingroup$ Thank you for your answer, I will check that. I hope I am not that silly, I am a statistician and still have a lot to learn about computers on a hardware point of view (it is not clear for me why RAM is necessary when you don't need to have access to all the data at the same time, but maybe speed of writing and reading is essentially based on RAM..). I have limited ressources at work and at home, so I am wondering : how do people who participate to challenges like kaggle as a hobby cope with these issues ? Are they necessarily very well equipped ? $\endgroup$ – Stéphanie C Jun 23 '15 at 9:10
  • $\begingroup$ I tried Monetdb with no success, but I succeeded in loading at least with rSQLite, thank you for answering though. $\endgroup$ – Stéphanie C Jun 24 '15 at 16:21
  • $\begingroup$ You are correct: the reason to add RAM is access speed, primarily for writing. $\endgroup$ – reinierpost Jun 25 '15 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.