We are happy to announce to the public the availability of our slow query analyzer for MongoDB! Using the slow query analyzer you can quickly identify slow queries on any of your servers over a particular period of time. By default, “slow queries” are defined as queries that take longer than 100ms.
Slow Query Heatmap
The heat map gives you a quick visual overview of the slow queries in your system. The vertical axis of the chart is exponential time. This means the higher the bubble is in the chart, the slower the query (by orders of magnitude) – so keep an eye on the bubbles that are consistently at the top of your chart. You can also click on a bubble to see the details of the underlying query.
Queries of the same type have the same color. This enables you to easily identify patterns in your slow queries. You can also select portions of the graph to zoom into a particular time range.
E.g. In the example above you can see a cluster of red dots that are taking 10 secs+. You can also see that a background job that is running once an hour is taking 28 secs to execute. You can click on the data point to get the actual query.
Slow Query Data Table
The slow query data table gives you a tabular view of all the slow query data. The data tables display several important metrics about your slow queries:
- Type – Type of the query – query, insert, update, remove etc
- Database & collection – The Db and collection of the query
- Query – The actual query with the query parameters removed
- Count – The number of times the query was run in the selected time period
- nScanned – The number of entries scanned in the index + number of objects scanned from the collection
- Duration(ms) – The average execution time of the query in ms
- Reponse length(bytes) – The average response length of the query result in bytes
- nReturned – The average number of documents returned per execution of the query
- Read lock(micro secs) – The average read lock time in micro secs
- Write lock(micro secs) – The average write lock time in micro secs
You can sort the slow query table by any of the above columns to build the view you need. The query data can also be downloaded into Excel for analysis. Click the “Download data as csv” link to download the slow query data and do your custom analysis in Excel.
In order to easily identify problem queries, we provide a number of built-in views.
Slow Queries Sorted by “Most Frequent”
This view presents (in descending order) the queries that are executed most frequently in the system. You want to pay particular attention to the top queries in the list. If their nScanned value is high or the duration is high you need to add the appropriate index for this query. This reduces the number of documents scanned from the disk.
Slow Queries sorted by “Max Docs/Indexes Scanned”
This view presents (in descending order) the queries that are scanning the most index entries or the most documents in a collection. A high ‘nScanned’ value implies that you don’t have an index or you don’t have the right index. It might not be possible to add an index for every query in the system – but you should ensure that at least your high-frequency queries are all well indexed.
Slow Queries Sorted by “Slowest Query”
This view presents (in descending order) the queries that are taking the maximum time to execute. If the query is running very infrequently then you can decide if you want to optimize it.
Slow Queries Sorted by “Max Docs Returned”
This view presents (in descending order) the queries that are returning the most results. Returning a lot of results often results in network load on the system. You should also ensure that you’re only returning the fields that you need from the document and not all the fields.
Slow Queries Sorted by “Read Lock”
This view presents (in descending order) the queries that are consuming maximum read lock time in micro-secs. If you’re using the WiredTiger storage engine this is typically not an issue since WiredTiger employs document level locking.
Slow Queries Sorted by “Write Lock”
This view presents (in descending order) the queries that are consuming maximum write lock time in micro-secs. If you’re using the WiredTiger storage engine this is typically not an issue since WiredTiger employs document level locking.
