Our production cluster is experiencing significant spikes in CPU usage during peak hours. I’ve checked our indexes, but some queries still seem to be doing collection scans. What tools or strategies should I use to find the "bottleneck" queries? Is there a way to automate the indexing process, or do I need to manually analyze every explain plan for our aggregate pipelines?
3 answers
You should start by using the MongoDB Atlas Performance Advisor. It’s an incredible tool that automatically monitors your slow queries and actually suggests the exact index you need to build. In a project I managed in mid-2024, we reduced our CPU load by 60% just by following those suggestions. Also, make sure you are using the .explain("executionStats") method on your cursor. It tells you exactly how many documents were scanned versus how many were returned. If docsExamined is much higher than nReturned, you’ve found your culprit!
Are you using a lot of $lookup or $graphLookup stages in your aggregation pipelines? Those stages are notoriously resource-heavy and can often be avoided by restructuring your data to be more "NoSQL-friendly".
Check your "Workingset." If your indexes don't fit in RAM, MongoDB has to swap to disk, which is 100x slower. Increasing your RAM might be a quick fix.
Gary is spot on. Monitoring your "Page Faults" in Atlas will tell you if you're hitting disk too often. Sometimes more hardware is the only answer for huge datasets.
Ryan, yes, we are using $lookup quite a bit to pull user profiles into our activity feed. I suspect that's where the CPU spikes are coming from. I’m considering denormalizing the data and storing the basic user info directly in the activity documents. Cynthia’s advice about the Performance Advisor is great too; I’ll enable it tonight to see if we have any missing indexes on those foreign keys before I start refactoring the whole database.