2

I have a large number of records (~1 billion) that I need to load into MongoDB (actually TokuMX, but whatever). I have about 6 different indices I need to create on the collection. Is it always faster to load the data, and then create the indices? When I look at Mongo's logfile, It seems like Mongo does some kind of large operation (maybe a row count?) before actually starting index creation, and it does this for every index I create.

Will it always be faster to create the indices after loading the data?

If I wait until after loading the data, would it be faster to create each index in the background at the same time rather creating them than one-by-one?

  • Why don't you just simply perform a test of your system with your indexes (and disk, RAM, CPU...) and find out? It does appear to be an option that many choose for bulk loading - i.e. disable/enable indexes - but only you can check on your own system. The same remark applies to your second question. Just as a final note, there is surely a big difference between TokuDB and MongoDB - one is a relational engine that utilises some special indexing techniques, whereas the other is a JSON based document store. – Vérace Jun 29 '14 at 22:39
  • 1
    Sorry, I meant TokuMX, not TokuDB (edited). Regarding doing all the tests myself, the loads take 30+ hours each. Testing each configuration would take days, which is why I posted here in hopes of getting a good answer in a shorter amount of time – Thomas Johnson Jun 30 '14 at 1:19
2

Back in the day we would bulk load our data in this way:

  1. Drop indexes
  2. Load data in the order for which the clustered index would be built (i.e., you export the data in a precise way)
  3. After the load is completed, create the clustered index
  4. Next, create any additional non-clustered indexes
  5. Miller time (this was before I could afford decent beer)

That method always proved faster than leaving the indexes in place. However, this was for Sybase and SQL Server. I imagine other systems would be similar, but I can't say for certain.

  • 1
    In my experience, if you are doing the bulk insert in more or less the order of the clustered index as a bulk operation (i.e. not many smaller inserts) then creating the clusters index first can be more efficient. Though generally the above is good advice, especially the beer step if you have good stuff handy as for large tables you could be waiting a while whatever order your perform the work in. – David Spillett Jul 1 '14 at 21:15
1

If you are doing a large, load operation it is faster to utilize the TokuMX bulk loader, as it only requires one pass over the data to create both the primary key index and any secondary indexes. More information is available in the documentation at http://docs.tokutek.com/tokumx/tokumx-commands.html#tokumx-new-commands-loader

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.