Finding slow queries in MongoDB
Posted By : Ankit Arora | 25-Dec-2016
Finding slow queries in MongoDB.
Database Profiling
MongoDB Profiler is a db profiling system that can help identify inefficient
or slow queries and operations.
Levels of profiles available are:
Level |
Setting |
0 |
Off. & No profiling |
1 |
On & only includes slow operations |
2 |
On & Includes all operations |
We can enable it by setting the Profile level value using the following
command in mongo shell :
"db.setProfilingLevel(1)"
By default, mongod records slow queries to its log, as defined by slowOpThresholdMs.
NOTE
Enabling database profiler puts negative impact on MongoDB’s performance.
It’s better to enable it for specific intervals & minimal on Production Servers.
We can enable profiling on a mongod basis but This setting will not propagate
across a replica set and sharded cluster.
We can view the output in the system.profile collection in mongo shell using show profile command, or using following:
“db.system.profile.find( { millis : { $gt : 200 } } )”
Command returns operations that took longer than 200 ms. Similarly we
can change the values as per our need.
Enabling profile for an entire mongod instance.
For the purpose of development in testing, we can enable database profiling/settings for an
entire mongod instance. The profiling level will be applied to all databases.
NOTE:
We can't enable the profiling settings on a mongos instance. To enable the profiling in
shard clusters, we have to enable/start profiling for each mongod instance in cluster.
Query for the recent 10 entries
“db.system.profile.find().limit(10).sort( { ts : 1 } ).pretty()”
Collection with the slowest queries(No. Of queries)
“db.system.profile.group({key: {ns: true}, initial: {count: 0}, reduce: function(obj,prev){ prev.count++;}})”
Collection with the slowest queries(No. Of millis spent)
“db.system.profile.group({key: {ns: true}, initial: {millis: 0}, reduce: function(obj, prev){ prev.millis += obj.millis;}})”
Most recent slow query
“db.system.profile.find().sort({$natural: -1}).limit(1)”
Single slowest query(Right now)
“db.system.profile.find().sort({millis: -1}).limit(1)”
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on View Cookie Settings to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.
About Author
Ankit Arora
Ankit is a Redhat Certified Engineer and Cloud Engineer.