Index Build Operations on a Populated Collection

By default, creating an index on a populated collection blocks all other operations on a database. When building an index on a populated 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.

Background Construction

Note

The following section refers to building indexes on a standalone. For a replica set or a sharded cluster, use a rolling index build. See Build Indexes on Replica Sets for details.

For potentially long running index building operations on standalone deployments, consider the background option so that the MongoDB database remains available during the index building operation.

For example, to create an index in the background of the zipcode field of the people collection, issue the following:

db.people.createIndex( { zipcode: 1 }, { background: true } )

By default, background is false for building MongoDB indexes.

You can combine the background option with other options, as in the following:

db.people.createIndex( { zipcode: 1 }, { background: true, sparse: true } )

Behavior

Background indexing operations run in the background so that other database operations can run while creating the index. However, the mongo shell session or connection where you are creating the index will block until the index build is complete. To continue issuing commands to the database, open another connection or mongo instance.

Queries will not use partially-built indexes: the index will only be usable once the index build is complete.

Note

If MongoDB is building an index in the background, you cannot perform other administrative operations involving that collection, including running repairDatabase, dropping the collection (i.e. db.collection.drop()), and running compact. These operations will return an error during background index builds.

Performance

The background index operation uses an incremental approach that is slower than the normal “foreground” index builds. If the index is larger than the available RAM, then the incremental process can take much longer than the foreground build.

Building an index can have a severe impact on the performance of the database. If possible, build indexes during designated maintenance windows.

Changed in version 3.4: You can build one or more indexes on a collection with the database command createIndexes. The default limit on memory usage for a createIndexes operation is 500 megabytes. You can override this limit by setting the maxIndexBuildMemoryUsageMegabytes server parameter.

createIndexes uses a combination of memory and temporary files on disk to complete index builds. Once the memory limit is reached, createIndexes uses temporary disk files in a subdirectory named _tmp within the --dbpath directory for additional scratch space. The higher the memory limit is set, the faster the index build can complete, but be careful not to set this limit too high relative to available RAM or your system can run out of free memory.

Interrupted Index Builds

If a background index build is in progress when a standalone (i.e. not part of a replica set) mongod process terminates, when the instance restarts the index build will restart as foreground index build. If the index build encounters any errors, such as a duplicate key error, the mongod will exit with an error.

To start the standalone mongod after a failed index build, use the storage.indexBuildRetry or --noIndexBuildRetry to skip the index build on start up.

storage.indexBuildRetry and --noIndexBuildRetry do not prevent replicated index builds.

Build Indexes on Replica Sets and Sharded Clusters

To minimize the impact of building an index on:

If not using the rolling index build procedure:

  • A foreground index build on a primary requires a DB lock. It replicates as a foreground index build on replica set secondaries, and the replication worker takes a global DB lock that queues reads and writes to all databases on the indexing server.
  • A background index build on a primary replicates as background index builds on secondaries. The replication worker does not take a global DB lock, and secondary reads are not affected.
  • For both foreground and background index builds on the primary, the index operations on replica set secondaries begin after the primary finishes building the index.

The amount of time required to build the index on a secondary must be within the window of the oplog, so that the secondary can catch up with the primary.

Index Names

The default name for an index is the concatenation of the indexed keys and each key’s direction in the index, 1 or -1.

Example

Issue the following command to create an index on item and quantity:

db.products.createIndex( { item: 1, quantity: -1 } )

The resulting index is named: item_1_quantity_-1.

Optionally, you can specify a name for an index instead of using the default name.

Example

Issue the following command to create an index on item and quantity and specify inventory as the index name:

db.products.createIndex( { item: 1, quantity: -1 } , { name: "inventory" } )

The resulting index has the name inventory.

To view the name of an index, use the getIndexes() method.

View Index Build Operations

To see the status of an index build operation, you can use the db.currentOp() method in the mongo shell. To filter the current operations for index creation operations, see Active Indexing Operations for an example.

The msg field will include the percent of the build that is complete.

Terminate Index Build Operation

To terminate an ongoing index build, use the db.killOp() method in the mongo shell. For index builds, the effects of db.killOp() may not be immediate and may occur well after much of the index build operation has completed.

You cannot terminate a replicated index build on secondary members of a replica set.

To minimize the impact of building an index on replica sets and sharded clusters with replica set shards, see: