MongoDB – Why Does Query Hang When Creating Index?

mongodbmongodb-3.2

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?

Best Answer

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