Find slow queries with the Query Profiler

MongoDB can record the queries that take longer than a threshold in each database’s profiler log. The Query Profiler switches that recording on and off, groups the recorded find and aggregate queries, and shows how often each ran and how long it took. From any execution you can read its code, open it in an editor, or see its plan.

Open the Query Profiler

Right-click a database (for example acme_store) and choose Query Profiler, or select a database and click Query Profiler in the global toolbar. From a collection, Query Profiler opens the profiler of its whole database. A Query Profiler - acme_store tab opens; if one is already open for that database, it comes to the front.

Switch the profiler on

1. While the profiler is off, a bar says Profiler is OFF. Switch it on to collect data about slow queries.
2. Turn the Profiler switch ON. A confirmation, Enabling database profiler, explains that find and aggregate queries slower than 100 ms will be recorded, and that profiling slows the database down and uses disk space. Click OK.
3. Use your application as usual. Slow queries appear after the next Refresh.
4. When you are done, turn the switch OFF. Nothing is asked; the entries already collected stay listed.

Find the slowest queries

1. Pick the period under Results for last and, if needed, one Collection (default: All collections).
2. Choose Group by:
   • Exact query groups executions with the same fields and the same values;
   • Query shape groups queries that differ only by their values (the server’s query hash), for example every find({ "customer_id": … }) on orders.
3. Click a column header to sort by it (descending first, click again to reverse). Sort by Sum of durations to find what costs the most overall, or by Max duration for the worst single run.
4. Select a row. The bottom-left pane lists its executions, newest first, with User, Operation category, Timestamp, Duration and Keys Examined.
5. Select an execution. Query Code shows its query as shell code; Explain plan shows its Visual Explain (find queries only).

Act on an execution

Reference

Tips

• Switch the profiler on for a limited time (during a load test, or while users report slowness), then off again.
• Group by Query shape to find a query pattern that needs an index, then confirm with Explain plan and create the index (see Manage indexes).
• For what is running right now rather than over time, use Current Operations: see Monitor a server.

Troubleshooting