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
=== updated 1/3/2023 ================================================================ For MongoDB JSON logs analytics, use my improved version from https://github.com/simagix/hatchet . ================================================================================== 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 u
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

Maobi - Reports Generator for Keyhole

-
Image
The Maobi project was created to generate HTML reports by reading stats collected by Keyhole.  The word maobi is a phonetic translation of Chinese paintbrush or ink brushes, which are commonly used in Chinese calligraphy as well as Japanese calligraphy.  They are also used in Chinese painting and other brush painting styles.  When I was little, it was required in school to use an ink brush to write or to paint pictures.  It took practice and effort to create something pleasant to the eyes. The idea of naming the tool Maobi was a reflection of the time we all spent on preparing reports and presentations. Keyhole started as a command line data collecting and analytics tool. With more data to be presented, it became difficult to include all metrics within a terminal window. So, the project began. Maobi is like a coworker Keyhole can only dream of, one who is smart, friendly, stylish but not flashy, and has contributed greatly to Keyhole’s popularity. Installation The easiest way to
Read more