Database should have very good performance for the better production.To have better performance its necessary to optimize the long running queries which degrade the database.Is it possible to trace slow queries in MongoDB?How can we trace out the long running queries? Similar to the slow query log in the mysql,the mongodb supports a feature called Profiling.The database profiler collects fine grained data about MongoDB write operations, cursors, database commands on a running mongod instance.There are different levels in Profiling each serving a unique pattern.
Database Profiling Levels:
The mongodb database profiling mainly involves three levels such as Level 0,Level 1,Level 2.
- Level 0 : This is the first level in database profiling which means that the profiler is set off.It does not contain any data.
- Level 1: This level means that it is meant to trace the slow queries.It has a default value of tracing queries running more than 100 milliseconds.
- Level 2: This level means that it will trace all the database queries.In the production it will surely affect the performance.So based on your requirement you can choose the level of profiling that is to be initiated in the database.
Enabling Database Profiling:
By default the profiler will be disabled in the system.To set the profiler in the database make sure that you login as the root user with required privileges.Now issue the below command with the profiling level:
|
1 |
db.setProfilingLevel(1) |
The above command will enable the database profiling and trace only the slow queries more than 100 milliseconds since its defined for Level 1.
|
1 |
db.setProfilingLevel(2) |
This command will tend to log all the queries that are involved in the operation.Yeah,its also possible to trace the queries running more than some ‘x’ seconds.To trace the slow queries that running more than 500 milliseconds then follow the below command:
|
1 |
db.setProfilingLevel(1,500) |
Similarly the seconds can be varied based on the user input.Once the database profiler is enabled then there will be automatic creation of a collection named system.profile in the admin database.You can also view the slow queries by issuing the below query in the admin database:
|
1 |
db.system.profile.find() |
To sort by natural order (time in) then fire the below query:
|
1 |
db.system.profile.find({}).sort({$natural:-1}) |
So in order to sort by slow queries first fire the below query:
|
1 |
db.system.profile.find({}).sort({$millis:-1})Alimit(10); |
To view queries running greater than ‘x seconds try:
|
1 |
db.system.profile.find({"millis":{$gt:x}})’ |
To trace slow queries with specific namespace fire the below query:
|
1 |
db.system.profile.find( { "ns": /db.coll/ } ).sort({millis:-1,$ts:-1}) |
Checking current profiler level:
Its also easy to check the current database profiling level by issuing the below command:
|
1 |
db.getProfilingLevel() |
Yeah we are done in tracing the slow queries.Go ahead, it’s now time to hunt the poor performing queries and optimise them.
