MongoDB World 2019 - The Sights (and Smells) of a Bad Query

01 - README.md

The Sights (and Smells) of a Bad Query

This Gist contains links to the tools and templates used during my talk at MongoDB World 2019. If you have any questions about the content or feel I've missed something, please feel free to shoot me a note at alex@alexbevi.com or leave a comment below.

Tools

MongoDB Atlas

MongoDB Atlas delivers the world’s leading database for modern applications as a fully automated cloud service engineered and run by the same team that builds the database. Proven operational and security practices are built in, automating time-consuming administration tasks such as infrastructure provisioning, database setup, ensuring availability, global distribution, backups, and more.

MongoDB Compass

The GUI for MongoDB. Visually explore your data. Run ad hoc queries in seconds. Interact with your data with full CRUD functionality. View and optimize your query performance. Available on Linux, Mac, or Windows. Compass empowers you to make smarter decisions about indexing, document validation, and more.

mgeneratejs

mgeneratejs generates structured, semi-random JSON data according to a template object. Can be used alongside mongoimport to seed a database with content.

mongoimport

The mongoimport tool imports content from an Extended JSON, CSV, or TSV export created by mongoexport, or potentially, another third-party export tool.

Setup

Setting up and seeding our database was done as follows:

1. Create a New Atlas Cluster
2. Get the connection string to be used in the mongo shell
3. Import data using our template (template.json) as follows:

mgeneratejs template.json -n 1000000 | \
mongoimport --uri  "mongodb+srv://<user>:<pass>@<cluster>.mongodb.net/data" -c users

Replace user, pass and cluster with the values found in step #2

This will import 1M documents. Repeat the process as often as you like to generate more content.

Log Analysis

Once the logs have been downloaded from Atlas (as a tar.gz or gz archive) and extracted, we will use mtools to filter and chart the logs.

As we may have multiple log files we will use mlogfilter to merge the logs together (and strip them of markers). mlogfilter can also be used to limit the logs to a specific date/time range though we will not be filtering by date in the following examples.

Charting by Execution Time

mlogfilter *.log --markers none  | mplotqueries --logscale

Although not specified, the default mplotqueries plot type is the scatter plot with a yaxis set to duration, which we've used above.

Charting by Documents Scanned vs. Number Returned

mlogfilter *.log --markers none  | mplotqueries --type docsExamined/n

02 - template.json

{
    "name": "$name",
    "age": "$age",
    "ssn": "$ssn",
    "company": "$company",
    "title": "$profession",
    "location": "$coordinates",
    "address": {
        "street": "$address",
        "city": "$city",
        "state": "$state",
        "zip": "$zip"
    },
    "emails":  {"$array": {"of": "$email", "number": {"$integer": {"min": 1, "max": 5}} }},
    "rating": {"$integer": {"min": 1, "max": 1000}},
    "status": {"$choose": {"from": ["read", "unread", "deleted"], "weights": [2, 1, 1]}},
    "enabled": "$bool",
    "last_logged_in_from": "$ip",
    "last_logged_in_at": {"$date": {"min": "2015-01-01", "max": "2018-12-31T23:59:59.999Z"}}
}

99 - REFERENCES.md

Presentation References

Additional documentation is provided below to allow you do continue learning about the topics discussed during the presentation.

Code Smells

• Code smell
• Write clean code and get rid of code smells with real life examples

Equality-Sort-Range

• Optimizing MongoDB Compound Indexes

Tools

• mtools
  • mlogfilter
  • mplotqueries

Other

• How To Write Resilient MongoDB Applications