Why does Mongo query hang for a long time when creating index?

Question

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?

Answer 1

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).

Answer 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 -

Answer 3

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.