MongoDB logging all queries

Question

The question is as basic as it is simple... How do you log all queries in a "tail"able log file in mongodb?

I have tried:

• setting the profiling level
• setting the slow ms parameter starting
• mongod with the -vv option

The /var/log/mongodb/mongodb.log keeps showing just the current number of active connections...

Solution 1

I ended up solving this by starting mongod like this (hammered and ugly, yeah... but works for development environment):

mongod --profile=1 --slowms=1 &

This enables profiling and sets the threshold for "slow queries" as 1ms, causing all queries to be logged as "slow queries" to the file:

/var/log/mongodb/mongodb.log

Now I get continuous log outputs using the command:

tail -f /var/log/mongodb/mongodb.log

An example log:

Mon Mar  4 15:02:55 [conn1] query dendro.quads query: { graph: "u:http://example.org/people" } ntoreturn:0 ntoskip:0 nscanned:6 keyUpdates:0 locks(micros) r:73163 nreturned:6 reslen:9884 88ms

Solution 2

You can log all queries:

$ mongo
MongoDB shell version: 2.4.9
connecting to: test
> use myDb
switched to db myDb
> db.getProfilingLevel()
0
> db.setProfilingLevel(2)
{ "was" : 0, "slowms" : 1, "ok" : 1 }
> db.getProfilingLevel()
2
> db.system.profile.find().pretty()

Source: http://docs.mongodb.org/manual/reference/method/db.setProfilingLevel/

db.setProfilingLevel(2) means "log all operations".

Solution 3

Because its google first answer ...
For version 3

$ mongo
MongoDB shell version: 3.0.2
connecting to: test
> use myDb
switched to db
> db.setLogLevel(1)

http://docs.mongodb.org/manual/reference/method/db.setLogLevel/

Solution 4

MongoDB has a sophisticated feature of profiling. The logging happens in system.profile collection. The logs can be seen from:

db.system.profile.find()

There are 3 logging levels (source):

• Level 0 - the profiler is off, does not collect any data. mongod always writes operations longer than the slowOpThresholdMs threshold to its log. This is the default profiler level.
• Level 1 - collects profiling data for slow operations only. By default slow operations are those slower than 100 milliseconds. You can modify the threshold for “slow” operations with the slowOpThresholdMs runtime option or the setParameter command. See the Specify the Threshold for Slow Operations section for more information.
• Level 2 - collects profiling data for all database operations.

To see what profiling level the database is running in, use

db.getProfilingLevel()

and to see the status

db.getProfilingStatus()

To change the profiling status, use the command

db.setProfilingLevel(level, milliseconds)

Where level refers to the profiling level and milliseconds is the ms of which duration the queries needs to be logged. To turn off the logging, use

db.setProfilingLevel(0)

The query to look in the system profile collection for all queries that took longer than one second, ordered by timestamp descending will be

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

Solution 5

I made a command line tool to activate the profiler activity and see the logs in a "tail"able way --> "mongotail":

$ mongotail MYDATABASE
2015-02-24 19:17:01.194 QUERY  [Company] : {"_id": ObjectId("548b164144ae122dc430376b")}. 1 returned.
2015-02-24 19:17:01.195 QUERY  [User] : {"_id": ObjectId("549048806b5d3db78cf6f654")}. 1 returned.
2015-02-24 19:17:01.196 UPDATE [Activation] : {"_id": "AB524"}, {"_id": "AB524", "code": "f2cbad0c"}. 1 updated.
2015-02-24 19:17:10.729 COUNT  [User] : {"active": {"$exists": true}, "firstName": {"$regex": "mac"}}
...

But the more interesting feature (also like tail) is to see the changes in "real time" with the -f option, and occasionally filter the result with grep to find a particular operation.

See documentation and installation instructions in: https://github.com/mrsarm/mongotail

View more solutions

Comments

[fguillen]

mongod -vv worked for me

[João Rocha da Silva]

Yeah, after activating level 2 profiling, a collection is added to the database. However, having to reload a gui or running a command everytime I perform debugging is a PITA at the end of the day... Thats why i wanted a tailable log file.

[Andrew Magee]

Should this be equivalent to adding profile=1 and slowms=1 lines in /etc/mongodb.conf?

[auhuman]

I couldn't find /var/log/mongodb/mongodb.log but it was logging in the console, which I needed. Thanks

[propagated]

According to that page, it only works in UNIX enviros, and i don't have it in my bin dir in windows. Any recommended windows equiv?

[Daniel Williams]

Are you running on a remote windows server (azure cloud, etc) or locally on your pc? If it's all locally wireshark will be more than sufficient. To install it on windows you will need to build mongosniff.exe which is a bit undocumented. You follow the linux instructions but you need to install the development version of winpcap.

[propagated]

Thanks for the reply. I ended up able to get the information i needed out of the mongo profiler, but i'll keep wireshark in my pocket if something more serious arises again.

[Ehtesh Choudhury]

At a glance, it looks like this is a better answer than the accepted answer.

[inolasco]

Not better, given that the questions asks for a tailable log file, but definitely useful, in cases where you don't have access to the log files, only the mongo shell, like the one that brought me here :)

[toske]

You can just add --profile=2 to /etc/mongodb.conf according to official Mongo docs, any all operations will be logged.

[andresigualada]

I tried setting the profiling level to 2 but I also needed to set the second parameter to -1, like db.setProfilingLevel(2,-1)

[Half Blood Prince]

@auhuman Where to write "tail -f /var/log/mongodb/mongodb.log" command??

[Abhishek Gupta]

No need to restart you can simply use db.setProfilingLevel(level,slowms). For eg: db.setProfilingLevel(2,1) will set level to be 2 and slow query threshold to be 1ms.

[totymedli]

For those interested where the logs go, the doc states: mongod writes the output of the database profiler to the system.profile collection.

[kayn]

According to the documentation, Loglevel 0 does not mean "no logging" but it logs slow queries: "the profiler is off, does not collect any data. mongod always writes operations longer than the slowOpThresholdMs threshold to its log." src: docs.mongodb.com/v3.2/tutorial/manage-the-database-profiler/‌​…

[Luke W]

this is the most complete response to the OP. esp. regarding the 'tail-able' requirement.

[node_saini]

db.system.profile.find().pretty() gives nothing for me

[Ashish]

@node_saini Try db.setProfilingLevel(2, 1)

[Carlton]

I don't think adding "profile=1" to /etc/mongod.conf works anymore, believe that works in Mongo < 2.6. Config now uses YAML format.

[garlon4]

You can add operationProfiling: with the two values mode: slowOp slowOpThresholdMs: 0 below that to the YAML version of mongod.conf.

[4b0]

Welcome to Stack Overflow! While this code may solve the question, including an explanation really helps to improve the quality of your post.

[Soban Soundararajan]

Can someone explain the structure MongoDB follows when logging the profiler documents in the log message? For example, the field responseLength in the profiler document is written as resLen in MongoDB's log (as mentioned in docs.mongodb.com/manual/reference/database-profiler/… ). Want to know how and where this translation takes place.

[oceanBT]

when using username and password: mongo DATABASENAME -u DATABASEUSER -p DATABASEPASSWORD --authenticationDatabase admin

[Fusion]

Log file that can be found where... ?

[Nithin B]

You can file log file path info in the config file of MongoDB. Example: "C:\Program Files\MongoDB\Server\4.0\bin\mongod.cfg"