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.
Location: Atlanta, GA, USA

Comments

Post a Comment

Popular posts from this blog

Build and Download Keyhole

-
Image
Keyhole is an open source project and the source codes are available at GitHub .  It is recommended to build your own Keyhole executables from the source codes.  Alternatively, Keyhole executables can be downloaded from Docker Hub , and available executables are for Linux, macOS, and Windows. Build from GitHub Golang has to be installed before you can build.  The commands below show how to clone from GitHub and build a Keyhole executable: mkdir -p $GOPATH/src/github.com/simagix cd $GOPATH/src/github.com/simagix git clone --depth 1 https://github.com/simagix/keyhole.git cd keyhole go build main/keyhole.go I provide a build.sh script to build Keyhole on macOS or Linux.  Simply run the script: ./build.sh I encourage you to use the script to build.  It’s easier for me to debug based on the included version number if a reported bug ever comes to me. Cross Platform Builds To my surprise, it was not as easy as I thought for many users to build Keyhole executables.  How about Windows executab
Read more

MongoPush - Push-Based MongoDB Atlas Migration Tool

-
Image
MongoPush tool is another bold stroke in my serendipitous MongoDB career. It is a push based migration tool for MongoDB clusters. It provides high parallelism, supports topology transformation, is resumable, offers progress monitoring, and supports migrating subsets of data.  With MongoDB Atlas gaining popularity and the success of the Keyhole , I was frequently asked whether a migration feature can be added to the Keyhole. The other existing migration solutions often fail because of firewall restrictions or other limitations.  Keyhole serves a different purpose as an analysis tool, so rather than confuse its function, I created MongoPush as a separate tool.  Push-Based Solution My goal was simple, to develop a tool that can perform a sharded cluster migration when the Atlas Live Migration Service is not suitable.  MongoDB provides the mongomirror tool to push data to MongoDB Atlas from behind firewalls, but mongomirror can only migrate data between replica sets. It does not yet p
Read more

Survey Your Mongo Land

-
Image
Keyhole was still under active development after I published the Peek at your MongoDB Clusters like a Pro with Keyhole: Part 1 post in December 2019.  Since then, I have added many features and also changed the format of output files from json to bson.  I’ll use this post to summarize new features and modified functions not available in the previous post.  Recap A couple months into Keyhole development, a clear direction was surfacing: I needed a tool to fetch all information of a MongoDB cluster in one shot.  I joked about this function the “Know the enemy and hit them where it hurts” feature.  The -allinfo option is perhaps the most commonly used one among users.  Using the -allinfo option, Keyhole collects host and build info, replication stats, server status, size and storage stats of databases and collections, index details and usages, and sharding configurations.  The information was collected from the primary member of a replica set or a mongos of a sharded cluster.  Later,
Read more