How to find queries not using indexes or slow in m

问题:

is there a way to find queries in mongodb that are not using Indexes or are SLOW? In MySQL that is possible with the following settings inside configuration file:

log-queries-not-using-indexes = 1
log_slow_queries = /tmp/slowmysql.log

回答1:

The equivalent approach in MongoDB would be to use the query profiler to track and diagnose slow queries.

With profiling enabled for a database, slow operations are written to the system.profile capped collection (which by default is 1Mb in size). You can adjust the threshold for slow operations (by default 100ms) using the slowms parameter.

回答2:

First, you must set up your profiling, specifying what the log level that you want. The 3 options are:

• 0 - logger off
• 1 - log slow queries
• 2 - log all queries

You do this by running your mongod deamon with the --profile options:

mongod --profile 2 --slowms 20

With this, the logs will be written to the system.profile collection, on which you can perform queries as follows:

• find all logs in some collection, ordering by ascending timestamp:

db.system.profile.find( { ns:/<db>.<collection>/ } ).sort( { ts: 1 } );

• looking for logs of queries with more than 5 milliseconds:

db.system.profile.find( {millis : { $gt : 5 } } ).sort( { ts: 1} );

回答3:

You can use the following two mongod options. The first option fails queries not using index (V 2.4 only), the second records queries slower than some ms threshold (default is 100ms)

--notablescan

Forbids operations that require a table scan.

--slowms <value>

Defines the value of “slow,” for the --profile option. The database logs all slow queries to the log, even when the profiler is not turned on. When the database profiler is on, mongod the profiler writes to the system.profile collection. See the profile command for more information on the database profiler.

回答4:

You can use the command line tool mongotail to read the log from the profiler within a console and with a more readable format.

First activate the profiler and set the threshold in milliseconds for the profile to consider an operation to be slow. In the following example the threshold is set to 10 milliseconds for a database named "sales":

$ mongotail sales -l 1
Profiling level set to level 1
$ mongotail sales -s 10
Threshold profiling set to 10 milliseconds

Then, to see in "real time" the slow queries, with some extra information like the time each query took, or how many registries it need to "walk" to find a particular result:

$ mongotail sales -f -m millis nscanned docsExamined
2016-08-11 15:09:10.930 QUERY   [ops] : {"deleted": {"$exists": false}, "prod_id": "367133"}. 8 returned. nscanned: 344502. millis: 12
2016-08-11 15:09:10.981 QUERY   [ops] : {"deleted": {"$exists": false}, "prod_id": "367440"}. 6 returned. nscanned: 345444. millis: 12
....

回答5:

In case somebody ends up here from Google on this old question, I found that explain really helped me fix specific queries that I could see were causing COLLSCANs from the logs.

Example:

db.collection.find().explain()

This will let you know if the query is using a COLLSCAN (Basic Cursor) or an index (BTree), among other things.

https://docs.mongodb.com/manual/reference/method/cursor.explain/

回答6:

While you can obviously use Profiler a very neat feature of Mongo DB due to which I actually fall in love with it is Mongo DB MMS. Takes less than 60 seconds and can manage from anywhere. I am sure you will Love it. https://mms.mongodb.com/