4

We are migrating our single Mongo instance to Mongo cluster with sharding. I haven't started sharding yet. However, I found out when I try to add an index, I can't query any collection anymore. We don't have this issue in our current Mongo instance. At our current Mongo, we can apply indexing and query the collection at the same time.

This is the command I run to create index on new cluster:

mongos> db.lc_data.ensureIndex({"name": 1})

Now, I open another terminal and query the collection or other collections in the same database.

mongos> db.lc_other.find()

The query will hang! Why does this happen?

3

As at MongoDB 3.2, this is expected behaviour for foreground index builds:

By default, creating an index blocks all other operations on a database. When building an index on a collection, the database that holds the collection is unavailable for read or write operations until the index build completes. Any operation that requires a read or write lock on all databases (e.g. listDatabases) will wait for the foreground index build to complete.

To avoid this behaviour when building new indexes on collections with significant existing data, you should build the index in the background instead:

mongos> db.lc_data.ensureIndex({"name": 1}, {background:true})

MongoDB 2.4+ can build multiple indexes in the background. For more information, see: Background Index Builds.

You may also wish to upvote/watch SERVER-20960: Default index build option support in config/runtime in the MongoDB issue tracker, which is a feature request to make the default index build type configurable (eg. default to background index builds).

  • But as per the mongodb document, it will not block any operation if you are creating single index at a time and if you do in the background it is even better. Instead of using, createIndexes we should use createIndex. try to use createIndex instead of ensureIndex at the time of creating index first time. – Ankit Vishwakarma Jul 25 '18 at 19:36
2

Background indexes have their own issues as well. If you are running a replica set I would recommend to do a "rolling" build

  1. Take a secondary out of the replica set
  2. Build the index
  3. Bring the secondary back into the replica set and wait for it to sync up.

I have found this to be the safest option to build large indexes in production. Before you start this in production make sure your replication window is big enough. More details in my blog post -

  • I am upvoting this because I used to do this all the time for MongoDB 2.4.x. It was not until I discovered that from MongoDb 2.6.x on, background indexes are also performed in the background on all secondaries. – RolandoMySQLDBA Sep 4 '17 at 20:17
0

As per good practice while creating new indexes we should use background option if MongoDB is running in standalone mode.Also it is advisable to use Sparse index.Also have a look into following link,as it is very helpful Indexing strategies.Also creating background indexes may be useful if you are using mongorestore during disaster recovery.Hope this helps.

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.