Using MongoDB $indexStats to identify and remove unused indexes

Available for Dedicated plans on mLab*

Maintaining proper indexing is critical to database performance. A single unindexed query is enough to cause significant performance degradation. It is relatively easy to spot a missing index using mLab's Slow Query Analyzer, but there isn't an obvious way to identify and remove indexes that aren't actually being used. Because unused indexes impact write performance and consume valuable resources, periodic index review and maintenance is recommended. MongoDB 3.2 has introduced a new feature to help identify unused indexes.

The $indexStats operator

The $indexStats aggregation pipeline operator displays a running tally of index usage since the server was last started. The operator reports on the usage for all of the indexes within a specified collection, and (with additional aggregation operators) can be targeted at a particular index. Knowing if and how often indexes are being used allows an administrator to make informed decisions on which indexes are providing benefit.

How to analyze index usage with $indexStats

It's not necessarily obvious which collections might contain unused indexes. To obtain a comprehensive list of all index usage, you'll need to run $indexStats on each collection.

The $indexStats operator is available starting in MongoDB 3.2. If your deployment's MongoDB version is 3.2.13 or later (including 3.4.x), you can use any database user to connect via the mongo shell.

> mongo ds123456-a0.mlab.com:23456/myDatabase -u <dbUser> -p <dbPassword>

If you are using version 3.2.12 or earlier, you will need a create a user with special permissions. To run the $indexStats command, you will need to first connect to the "admin" database with this admin user. The following command will use your "admin" user credentials to authenticate to the "admin" database, then switch over to the target database ("myDatabase").

> mongo ds123456-a0.mlab.com:23456/myDatabase -u <adminUser> -p <adminPassword> --authenticationDatabase admin

Running $indexStats on an entire collection

Now that you are connected to the target database, the $indexStats command can be run using the MongoDB shell:

> db.myColl.aggregate( { $indexStats: { } } )

{
	"name" : "color_1",
	"key" : {
		"color" : 1
	},
	"host" : "examplehost.local:27017",
	"accesses" : {
		"ops" : NumberLong(50),
		"since" : ISODate("2017-01-06T16:52:50.744Z")
	}
}
{
	"name" : "type_1",
	"key" : {
		"type" : 1,
	},
	"host" : "examplehost.local:27017",
	"accesses" : {
		"ops" : NumberLong(0),
		"since" : ISODate("2017-01-06T16:52:48.362Z")
	}
}
{
	"name" : "name_1",
	"key" : {
		"name" : 1
	},
	"host" : "examplehost.local:27017",
	"accesses" : {
		"ops" : NumberLong(100),
		"since" : ISODate("2017-01-06T16:32:44.609Z")
	}
}

The return document includes the following fields:

Output Field Description
name Index name
key Index key specification
host The hostname and port of the mongod process
accesses.ops The number of operations that used the index
accesses.since The time from which MongoDB started gathering the index usage statistics

Source: https://docs.mongodb.com/manual/reference/operator/aggregation/indexStats/

Interpreting $indexStats

Every database query, update, or command that uses an index will be counted toward that index's usage statistics.

The "name", "key", and "host" output fields provide the metadata for each index.

The "accesses.ops" value displays the number of operations that have used the index. Any indexes with zero access operations suggest a potentially unused index which can potentially be deleted.

The "accesses.since" value is the point in time from which MongoDB began gathering the index statistics. This value is set either upon index creation or as of a mongod server restart. Keep in mind that a mongod server restart can occur during maintenance events, upgrades, intentional cluster restarts, or as a part of unexpected failures.

It is important to note that $indexStats shows index data as of the last database server process restart. Therefore, these running tallies are wiped out and restarted with each server restart. If you would like a fresh set of statistics you can choose to perform an intentional cluster restart.

If you would prefer not to perform a cluster restart, you can use $indexStats to sample from two points in time and calculate the difference in access operations over time for each index.

Running $indexStats for a particular index

You can use the $match operator within the aggregation pipeline to specify a particular index. This allows you to match indexes based on index name or index key. You can run the following commands in the MongoDB shell:

Based on index name:

> db.myColl.aggregate([{$indexStats: {}}, {$match: {"name": "a_1"}}])

Based on index key:

> db.myColl.aggregate([{$indexStats: {}}, {$match: {"key": {"a": 1}}}])

The return document only displays the index statistics for the particular key.

{
        "name" : "a_1",
        "key" : {
                "a" : 1
        },
        "host" : "examplehost.local:27017",
        "accesses" : {
                "ops" : NumberLong(0),
                "since" : ISODate("2017-01-06T19:08:20.627Z")
        }
}

Removing unused indexes

Proceed with caution

As with all delete operations on the database, always err on the side of caution when removing an index.

  • Do not drop an index if there is any uncertainty surrounding its use.
  • Accidentally removing a necessary index can result in significant performance degradation.
  • Closely monitor database performance immediately after making index changes.

In addition, here are some checks to perform before removing an unused index:

  • Are there infrequent operations which require the index?
  • Are there query patterns that are failing to use the index?
  • Are there plans to use the index in the near future?

More information about indexing can be found in our documentation: http://docs.mlab.com/indexing/.

Take a backup first (optional)

An additional precaution is to take a backup before dropping a series of unused indexes.

Drop the unused index

After reviewing the considerations above, you can proceed with removing any unused indexes.

To drop an index, perform the following command in the MongoDB shell:

> db.myColl.dropIndex( { index_name: 1 } );

If you have questions on how to run $indexStats and safely remove an index, please email our team at support@mlab.com for help.

*This feature will also be available on mLab's Sandbox and Shared plans once SERVER-26734 has been resolved.

, ,

Comments are closed.