Index Responsibly


Indexes make the execution of queries efficient in MongoDB because without indexes, MongoDB must scan every document in a collection. However, having indexes adds overhead to update indexes for write operations. Keyhole prints the details of all indexes and their usages using the
-index option. This is useful to identify redundant and/or unused indexes of collections.

Indexes and Usages

Below is an example of showing indexes and their usages of collection mdb.numbers from a 2-shard sharded cluster.  Keyhole highlights redundant indexes in red with a leading ‘x’ and unused indexes since the mongo server started in blue with a leading ‘?’.  A shard key is with a leading ‘*’ character.  Usually, redundant indexes are those that can be covered by another compound index.  On the other hand, before dropping any unused indexes, check with the development teams on the purpose of creating those in the first place. See example outputs below:

$ keyhole --index "mongodb+srv://user:xxxxxx@hostname.local/"

mdb.numbers:

  { _id: 1 }

    host: shard1-0.local:27017, ops: 1238, since: 2019-05-06 13:39:49.003 +0000 UTC

    host: shard2-0.local:27017, ops: 1234, since: 2019-05-06 13:46:37.15 +0000 UTC

x { a: 1 }

    host: shard1-0.local:27017, ops: 0, since: 2019-05-06 13:40:29.677 +0000 UTC

    host: shard2-0.local:27017, ops: 0, since: 2019-05-06 13:46:37.172 +0000 UTC

x { a: 1, b: -1 }

    host: shard1-0.local:27017, ops: 0, since: 2019-05-06 13:40:26.085 +0000 UTC

    host: shard2-0.local:27017, ops: 0, since: 2019-05-06 13:46:37.168 +0000 UTC

x { a: -1, b: 1, c: -1 }

    host: shard1-0.local:27017, ops: 2, since: 2019-05-06 13:40:07.3 +0000 UTC

    host: shard2-0.local:27017, ops: 2, since: 2019-05-06 13:46:37.16 +0000 UTC

? { a: 1, b: 1, c: 1 }

    host: shard1-0.local:27017, ops: 0, since: 2019-05-06 13:40:13.979 +0000 UTC

    host: shard2-0.local:27017, ops: 0, since: 2019-05-06 13:46:37.164 +0000 UTC

x { a: 1, c: 1 }

    host: shard1-0.local:27017, ops: 0, since: 2019-05-06 13:40:43.344 +0000 UTC

    host: shard2-0.local:27017, ops: 0, since: 2019-05-06 13:46:37.177 +0000 UTC

* { b: 1 }

    host: shard1-0.local:27017, ops: 0, since: 2019-05-06 13:39:49.007 +0000 UTC

    host: shard2-0.local:27017, ops: 0, since: 2019-05-06 13:46:37.154 +0000 UTC

? { b: 1, c: 1 }

    host: shard1-0.local:27017, ops: 0, since: 2019-05-06 13:40:55.617 +0000 UTC

    host: shard2-0.local:27017, ops: 0, since: 2019-05-06 13:46:37.184 +0000 UTC

  { c: 1 }

    host: shard1-0.local:27017, ops: 129556, since: 2019-05-06 13:40:47.214 +0000 UTC

    host: shard2-0.local:27017, ops: 169830, since: 2019-05-06 13:46:37.18 +0000 UTC


Indexes info is written to shard2-0.local-index.bson.gz


You can drag the output file to Maobi to create an HTML report.  Maobi produces a nicer presentation than viewing all index info in a terminal window.

Mirror Indexes to Another Cluster

Another useful feature is to duplicate all indexes from a MongoDB cluster to another.  The command with -index option outputs a file with a -index.bson.gz suffix.  By reading the output file and with a destination cluster’s connection string, Keyhole, with the -createIndex option, duplicates indexes to the destination cluster. For example:.

keyhole --createIndex shard2-0.local-index.bson.gz \

"mongodb+srv://user:xxxxxx@hostname.mongodb.net/"

Find Words

The information of indexes and their usages are also collected by Keyhole with the -allinfo option.  Checkout the Survey Your Mongo Land for details.

Comments

Popular posts from this blog

Build and Download Keyhole

MongoPush - Push-Based MongoDB Atlas Migration Tool

Survey Your Mongo Land