In this blog post, we will walk through PMM-Query Analytics for MongoDB. We will see how to analyze MongoDB query performance; review the initial parameters that we need to check; and find out how to compare MongoDB query performance with and without indexes with the help of EXPLAIN plan.
The Percona Monitoring and Management QAN (PMM-QAN) dashboard helps DBAs and Developers to analyze database queries and identify performance issues more easily. Sometimes it is difficult to find issues by just enabling the profiler and tracking through mongo shell for all the slow queries.
Test Case Environment
We configured the test environment with PMM Server before we ran the test:
PMM Version:<span class="s1">1.11.0</span>
MongoDB Version: 3.4.10
3 Member Replicaset (1 Primary, 2 Secondaries)
Test Query: Find Test case: (with and without Index)
Indexed Field: "product"
Query count: 1000
Total count: 20000
Please Note: We have to enable profiler in the MongoDB environment to reflect the metrics in the QAN page. The QAN dashboard lists multiple queries, based on the threshold we have set in profiler. For this demonstration, we have set profiling level to 2 to get the query reflected in the dashboard.
Let’s analyze comparisons of the plans that were collected before and after the index was added to the collection.
The QAN dashboard, lists the FIND query for the “pro” collection:
After selecting this specific query, we will see its details just below the list.
Review parameter: “Query Time”
Here Query Time=18ms, we will check for the query count, docs returned and docs scanned in detail in the explain plan.
Now the Query Time=15ms, we have improved the query by creating an index, and MongoDB is no longer scanning the whole collection.
Analysis of the query from the QAN EXPLAIN Plan: You can use the toggle button “Expand All” to check the complete execution stats and plans, as this in case of the “test” database
COMPARISON OF PERFORMANCE
Here we make a comparison of query performance with and without the index, and take a look at the basics that need to be checked before and after index creation:
Stage =”COLLSCAN”, this means that MongoDB scans every document in order to fulfil the find query, so you need to create an index to improve query performance
Stage=”IXSCAN”, indicates that the optimizer is not scanning the whole document, but scanned only the indexed bound document
It scanned whole documents i.e. docsExamined:20000 and return nReturned:1000.
MongoDB uses the index and scans only the relevant documents .i.e. docsExamined:1000 and return nReturned:1000. We can see the performance improvement from adding an index.
This is how the query behaves after index creation. We can identify exactly which index is being used with QAN. We can discover whether that index is useful or not, and other performance related events, all with an easy UI.
As this blog post is specific to Query Analysis for MongoDB, QAN graphs and their attributes can be accessed from here, an excellent blog post written by my colleague Vinodh.