Use Keyhole to understand slow queries in MongoDB

Slow queries are a part of life. This post will show you how to use Keyhole to turn your MongoDB logs into a spreadsheet you can use to understand where to focus your efforts.

Keyhole

First of all, get Keyhole up and running. Please follow their instructions on how to do this.

I was able to do the following, but things change, so check out their docs for more relevant information.

git clone https://github.com/simagix/keyhole.git
cd keyhole
go build main/keyhole.go
./keyhole --version
simagix/keyhole devel-20221102

Logs

So now we want to download the logs from MongoDB. I’m going to use the logs from MongoDB Cloud Atlas. So first of all, make sure you’re logged into the platform and on the projects page.

From here, select the project you want to check out the slow queries for. Now you’re on the right project, you can click the three dots ... to get the “Download Logs” option.

Go ahead and click “Download Logs”, and then you can select the specific server (if you have more than one) to get the logs for.

Pick the time frame you’re interested in and then click “Download Logs”.

Generate spreadsheet

Now we are going to use Keyhole to take the downloaded log file, and convert it to something we can understand and manipulate.

Run this command:

cd keyhole
./keyhole -loginfo ~/Downloads/acme-prod-shard-00-00.bob4.mongodb.net_2022-11-02T07_22_43_2022-11-02T19_22_43_mongodb.log.gz

This will provide some pertinent information about the top ten issues.

You can now open the .tsv file in a spreadsheet application. I’ve done this by running:

open ./out/acme-prod-shard-00-00.bob4.mongodb.net_2022-11-02T07_22_43_2022-11-02T19_22_43_mongodb.tsv

This produces something like this.

I’ve replaced sensitive information with .. in the spreadsheet, but you can get the gist. From here on out, you’re dealing with a spreadsheet you can filter and manipulate. So you can sort by Count, or Avg time, etc.

Conclusion

This is just one small use case of what Keyhole can provide. So check out what else it can offer you.