MongoDB - Logging write & read queries

Question

We're trying to log read & write operations in MongoDB. We want the actual queries to be logged to the file. We're using the following configuration (in the primary node in the replica set):

logpath=/.../mongodb.log
logappend=true
profile=2
slowms=1
diaglog=3
verbose=true
vvvv=true

To my understanding, this should work. However, in the log we can only see what was the type of query (drop/insert/update etc.) and the collection, not the actual query that was executed.

EDIT: To clarify: We want to log the read/write queries to the regular mongodb log file (same as you can do in MySQL for example).

What could be the problem? Anyone has any ideas? Thanks!

Answer 1

First of all please note:

Because the database profiler can negatively impact performance, only enable profiling for strategic intervals and as minimally as possible on production systems.

You can also enable profiler per database to reduce the profiling impact on the mongo instance. slowms=1 is redundant in your case profile=2 will log all queries no matter how long they take.

Here's a simple test to check that no additional configration required to view profile data:

db.setProfilingLevel(2)
db.testProf.insert({x : 1})
db.testProf.update({x : 1}, {$set : {x : 2}})
db.testProf.find({x : 2})
db.testProf.remove({x : 2})
db.setProfilingLevel(0)

Now you can query profile data for detailed info:

db.system.profile.find().pretty()

This should display the runtime info for each operation run above. Here's an extract from update operation details:

"op" : "update",
    "ns" : "profDb.testProf",
    "query" : {
            "x" : 1
    },
    "updateobj" : {
            "$set" : {
                    "x" : 2
            }
    },
    "nscanned" : 1,
    "nupdated" : 1,
    "fastmod" : true,
    "keyUpdates" : 0,
    "numYield" : 0,
    "lockStats" : {
            "timeLockedMicros" : {
                    "r" : NumberLong(0),
                    "w" : NumberLong(1131)
            },
            "timeAcquiringMicros" : {
                    "r" : NumberLong(0),
                    "w" : NumberLong(5)
            }
    }

The query field contains the update criteria while the updateobj field contains the update operation run against the document. If you do the same stuff and the query field is still empty probably you run update, remove, etc for all documents.

For more info on how to interpret profile data please refer to the docs - Database Profiler Output

EDIT: Just noticed diaglog=3 configuration. This one is mostly used for recording and replaying operation. This keeps a very verbose logs and also negatively impacts performance, I think you should skip it. Profiling is enough to achive want you want.

EDIT: Not sure why you need profile data saved into a file as I think it's much more convinient to have it in a collection. You can query it as any other collection for the data you are interested in. I'm not aware of any way to stream profile data directly to a file but you can export it any time like that:

mongoexport --db profDb --collection system.profile --out profiler.json

You are right, diaglog writes all data to a file, but this one is of internal format, which is not user friendly and hard to read.

diaglog is for internal use and not intended for most users.

Hope it helps!