I have been working a while with customers, supporting both MongoDB and MySQL technologies. Most of the time when an issue arises, the customers working with MySQL collect most of the information happening in the DB server, including all the queries running that particular time, using “show full processlist;” This information would help us to look at the problem, like which queries are taking the time and where it was spending the time.
But for MongoDB, most of the time we don’t receive this (in-progress operations) information. And we had to check with long queries logged into the MongoDB log file and, of course, it writes most of the things like planSummary (whether it used the index or not), documents/index scanned, time to complete, etc. It’s like doing a postmortem rather than checking the issue happening in real-time. Actually collecting the information about operations taking the time or finding a problematic query while the issue is happening could help you find the right one to kill (to release the pressure) or check the situation of the database.
The in-progress operations in MongoDB can be checked via the database command currentOp(). The level of information can be controlled via the options passed through it. Most of the time, the output from it is not that interesting to check because it contains a lot of information, making it difficult to spot the ones we need. However, MongoDB knows this and has included many options to filter the operations using currentOp over multiple versions easily. Some of the information regarding this is mentioned in the below release notes:
https://docs.mongodb.com/manual/release-notes/3.6/#new-aggregation-stages
https://docs.mongodb.com/manual/release-notes/4.0/#id26
https://docs.mongodb.com/manual/release-notes/4.2/#currentop
In this blog, I will share some tricks to work with this command and fetch the operations that we need to check. This would help a person check the ongoing operations and if necessary, kill the problematic command – if they wish.
Introduction
The database command ` provides information about the ongoing/currently running operations in the database. It must be run against the admin database. On servers that run with authorization, you need the inprog privilege action to view operations for all users. This is included in the built-in clusterMonitor role.
Use Cases
The command to see all the active connections:
db.currentOp()
The user that has no inprog privilege can view its own operations, without this privilege, with the below command:
db.currentOp( { "$ownOps": true } )
To see the connections in the background, and idle connections, you can use either one of the below commands:
db.currentOp(true)
db.currentOp( { "$all": true } )
As I said before, you can use filters here to check the operations you need, like a command running for more than a few seconds, waiting for a lock, active/inactive connections, running on a particular namespace, etc. Let’s see some examples from my test environment.
The below command provides information about all active connections.
mongos> db.currentOp()
{
"inprog" : [
{
"shard" : "shard01",
"host" : "bm-support01.bm.int.percona.com:54012",
"desc" : "conn52",
"connectionId" : 52,
"client_s" : "127.0.0.1:53338",
"appName" : "MongoDB Shell",
"clientMetadata" : {
"application" : {
"name" : "MongoDB Shell"
},
"driver" : {
"name" : "MongoDB Internal Client",
"version" : "4.0.19-12"
},
"os" : {
"type" : "Linux",
"name" : "CentOS Linux release 7.9.2009 (Core)",
"architecture" : "x86_64",
"version" : "Kernel 5.10.13-1.el7.elrepo.x86_64"
},
"mongos" : {
"host" : "bm-support01.bm.int.percona.com:54010",
"client" : "127.0.0.1:36018",
"version" : "4.0.19-12"
}
},
"active" : true,
"currentOpTime" : "2021-03-21T23:41:48.206-0400",
"opid" : "shard01:1404",
"lsid" : {
"id" : UUID("6bd7549b-0c89-40b5-b59f-af765199bbcf"),
"uid" : BinData(0,"47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=")
},
"secs_running" : NumberLong(0),
"microsecs_running" : NumberLong(180),
"op" : "getmore",
"ns" : "admin.$cmd",
"command" : {
"getMore" : NumberLong("8620961729688473960"),
"collection" : "$cmd.aggregate",
"batchSize" : NumberLong(101),
"lsid" : {
"id" : UUID("6bd7549b-0c89-40b5-b59f-af765199bbcf"),
"uid" : BinData(0,"47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=")
},
"$clusterTime" : {
"clusterTime" : Timestamp(1616384506, 2),
"signature" : {
"hash" : BinData(0,"z/r5Z/DxrxaeH1VIKOzeok06YxY="),
"keyId" : NumberLong("6942317981145759774")
}
},
"$client" : {
"application" : {
"name" : "MongoDB Shell"
},
"driver" : {
"name" : "MongoDB Internal Client",
"version" : "4.0.19-12"
},
"os" : {
"type" : "Linux",
"name" : "CentOS Linux release 7.9.2009 (Core)",
"architecture" : "x86_64",
"version" : "Kernel 5.10.13-1.el7.elrepo.x86_64"
},
"mongos" : {
"host" : "bm-support01.bm.int.percona.com:54010",
"client" : "127.0.0.1:36018",
"version" : "4.0.19-12"
}
},
"$configServerState" : {
"opTime" : {
"ts" : Timestamp(1616384506, 2),
"t" : NumberLong(1)
}
},
"$db" : "admin"
},
"originatingCommand" : {
"aggregate" : 1,
"pipeline" : [
{
"$currentOp" : {
"allUsers" : true,
"truncateOps" : true
}
},
{
"$sort" : {
"shard" : 1
}
}
],
"fromMongos" : true,
"needsMerge" : true,
"mergeByPBRT" : false,
"cursor" : {
"batchSize" : 0
},
"allowImplicitCollectionCreation" : true,
"lsid" : {
"id" : UUID("6bd7549b-0c89-40b5-b59f-af765199bbcf"),
"uid" : BinData(0,"47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=")
},
"$clusterTime" : {
"clusterTime" : Timestamp(1616384506, 2),
"signature" : {
"hash" : BinData(0,"z/r5Z/DxrxaeH1VIKOzeok06YxY="),
"keyId" : NumberLong("6942317981145759774")
}
},
"$client" : {
"application" : {
"name" : "MongoDB Shell"
},
"driver" : {
"name" : "MongoDB Internal Client",
"version" : "4.0.19-12"
},
"os" : {
"type" : "Linux",
"name" : "CentOS Linux release 7.9.2009 (Core)",
"architecture" : "x86_64",
"version" : "Kernel 5.10.13-1.el7.elrepo.x86_64"
},
"mongos" : {
"host" : "bm-support01.bm.int.percona.com:54010",
"client" : "127.0.0.1:36018",
"version" : "4.0.19-12"
}
},
"$configServerState" : {
"opTime" : {
"ts" : Timestamp(1616384506, 2),
"t" : NumberLong(1)
}
},
"$db" : "admin"
},
"numYields" : 0,
"locks" : {
},
"waitingForLock" : false,
"lockStats" : {
}
},
{
"shard" : "shard01",
"host" : "bm-support01.bm.int.percona.com:54012",
"desc" : "monitoring keys for HMAC",
…
...
Some of the important parameters that we may need to focus on from the output are as follows. I provide this information here as we will use these parameters to filter for the operations that we need.
| PARAMETER | DESCRIPTION |
| host | The host that the operation is running |
| opid | The operation id (it is used to kill that operation) |
| active | The connection’s status. True if it is running and false if it is idle |
| client | Host/IP information about where the operation originated |
| clientMetadata | Provides more information about client connection |
| shard | Which shard is connected if it is sharded cluster environment |
| appName | Information about the type of client |
| currentOpTime | Start time of the operation |
| ns | Namespace (details about the DB and collection) |
| command | A document with the full command object associated with the operation |
| secs_running / microsecs_running | How many seconds/microseconds that the particular operation is running |
| op | Operation type like insert, update, find, delete etc |
| planSummary | Whether the command uses the index IXSCAN or collection scan COLLSCAN (disk read) |
| cursor | Cursor information for getmore operations |
| locks | Type and mode of the lock. See here for more details |
| waitingForLock | True if the operation waiting for a lock, false if it has required lock |
| msg | A message that describes the status and progress of the operation |
| killPending | Whether the operation is currently flagged for termination |
| numYields | Is a counter that reports the number of times the operation has yielded to allow other operation |
The raw currentOp output can be processed by the javascript forEach function method in the mongo shell, so we can use it to do many operations. For example, I want to take counts of the output or number of active connections. Then I can use the below one:
mongos> var c=1;
mongos> db.currentOp().inprog.forEach(
... function(doc){
... c=c+1
... }
... )
mongos> print("The total number of active connections are: "+c)
The total number of active connections are: 16
To find the number of active and inactive connections:
mongos> var active=1; var inactive=1;
mongos> db.currentOp(true).inprog.forEach( function(doc){ if(doc.active){ active=active+1 } else if(!doc.active){ inactive=inactive+1 } } )
mongos> print("The number of active connections are: "+active+"\nThe number of inactive connections are: "+inactive)
The number of active connections are: 16
The number of inactive connections are: 118
To find the operations running (importing job) more than 1000 microseconds (for seconds, use secs_running) and with a specific namespace vinodh.testColl:
mongos> db.currentOp(true).inprog.forEach( function(doc){ if(doc.microsecs_running>1000 && doc.ns == "vinodh.testColl") {print("\nop: "+doc.op+", namespace: "+doc.ns+", \ncommand: ");printjson(doc.command)} } )
op: insert, namespace: vinodh.testColl,
command:
{
"$truncated" : "{ insert: \"testColl\", bypassDocumentValidation: false, ordered: false, documents: [ { _id: ObjectId('605a1ab05c15f7d2046d5d26'), id: 49004, name: \"Vernon Drake\", age: 19, emails: [ \"fetome@liek.gh\", \"noddo@ve.kh\", \"wunin@cu.ci\" ], born_in: \"1973\", ip_addresses: [ \"212.199.110.72\" ], blob: BinData(0, 4736735553674F6E6825) }, { _id: ObjectId('605a1ab05c15f7d2046d5d27'), id: 49003, name: \"Rhoda Burke\", age: 64, emails: [ \"zog@borvelaj.pa\", \"hoz@ni.do\", \"abfad@borup.cl\" ], born_in: \"1976\", ip_addresses: [ \"12.190.161.2\", \"16.63.87.211\" ], blob: BinData(0, 244C586A683244744F54) }, { _id: ObjectId('605a1ab05c15f7d2046d5d28'), id: 49002, name: \"Alberta Mack\", age: 25, emails: [ \"sibef@nuvaki.sn\", \"erusu@dimpu.ag\", \"miumurup@se.ir\" ], born_in: \"1971\", ip_addresses: [ \"250.239.181.203\", \"192.240.119.122\", \"196.13.33.240\" ], blob: BinData(0, 7A63566B42732659236D) }, { _id: ObjectId('605a1ab05c15f7d2046d5d29'), id: 49005, name: \"Minnie Chapman\", age: 33, emails: [ \"jirgenor@esevepu.edu\", \"jo@m..."
}
But this command can be easily written without forEach as follows directly as well:
mongos> db.currentOp({ "active": true, "microsecs_running": {$gt: 1000}, "ns": /^vinodh.testColl/ })
{
"inprog" : [
{
"shard" : "shard01",
"host" : "bm-support01.bm.int.percona.com:54012",
"desc" : "conn268",
"connectionId" : 268,
"client_s" : "127.0.0.1:55480",
"active" : true,
"currentOpTime" : "2021-03-23T13:05:32.550-0400",
"opid" : "shard01:689582",
"secs_running" : NumberLong(0),
"microsecs_running" : NumberLong(44996),
"op" : "insert",
"ns" : "vinodh.testColl",
"command" : {
"$truncated" : "{ insert: \"testColl\", bypassDocumentValidation: false, ordered: false, documents: [ { _id: ObjectId('605a1fdc5c15f7d2047ee04e'), id: 16002, name: \"Linnie Walsh\", age: 25, emails: [ \"evoludecu@logejvi.ai\", \"ilahubfep@ud.mc\", \"siujo@pipazvo.ht\" ], born_in: \"1982\", ip_addresses: [ \"198.117.218.117\" ], blob: BinData(0, 244A6E702A5047405149) }, { _id: ObjectId('605a1fdc5c15f7d2047ee04f'), id: 16004, name: \"Larry Watts\", age: 47, emails: [ \"sa@hulub.gy\", \"wepo@ruvnuhej.om\", \"jorvohki@nobajmo.hr\" ], born_in: \"1989\", ip_addresses: [], blob: BinData(0, 50507461366B6F766C40) }, { _id: ObjectId('605a1fdc5c15f7d2047ee050'), id: 16003, name: \"Alejandro Jacobs\", age: 61, emails: [ \"enijaze@hihen.et\", \"gekesaco@kockod.fk\", \"rohovus@il.az\" ], born_in: \"1988\", ip_addresses: [ \"239.139.123.44\", \"168.34.26.236\", \"123.230.33.251\", \"132.222.43.251\" ], blob: BinData(0, 32213574705938385077) }, { _id: ObjectId('605a1fdc5c15f7d2047ee051'), id: 16005, name: \"Mildred French\", age: 20, emails: [ \"totfi@su.mn\"..."
},
"numYields" : 0,
"locks" : {
},
"waitingForLock" : false,
"lockStats" : {
"Global" : {
"acquireCount" : {
"r" : NumberLong(16),
"w" : NumberLong(16)
}
},
"Database" : {
"acquireCount" : {
"w" : NumberLong(16)
}
…
The operations waiting for the lock on a specific namespace (ns) / operation (op) can be filtered as follows, and you can alter the parameters to filter as you wish:
db.currentOp(
{
"waitingForLock" : true,
"ns": /^vinodh.testColl/,
$or: [
{ "op" : { "$in" : [ "insert", "update", "remove" ] } },
{ "command.findandmodify": { $exists: true } }
]
}
)
Aggregate – currentOp():
Starting with MongoDB 3.6, currentOp method is supported in aggregation. So checking the currentOp is even easier with this method. Also, the aggregation pipeline doesn’t have a 16MB result size limit as well. The usage is:
{ $currentOp: { allUsers: <boolean>, idleConnections: <boolean>, idleCursors: <boolean>, idleSessions: <boolean>, localOps: <boolean> } }
Note:
Options/Features added, version-wise, to currentOp()
- allUsers, idleConnections – available from 3.6,
- idleCursors – available from 4.2
- idleSessions, localOps – available from 4.0
Let’s see an example of the same. Count all connections including idle connections with shard02:
mongos> db.aggregate( [ { $currentOp : { allUsers: true, idleConnections: true } },
... { $match : { shard: "shard02" }}, {$group: {_id:"shard02", count: {$sum: 1}} } ] )
{ "_id" : "shard02", "count" : 65 }
Now using the same import job, finding the operation as follows:
mongos> db.aggregate( [ { $currentOp : { allUsers: true, idleConnections: false } },
... { $match : { "ns": "vinodh.testColl" }} ] )
{ "shard" : "shard01", "host" : "bm-support01.bm.int.percona.com:54012", "desc" : "conn279", "connectionId" : 279, "client_s" : "127.0.0.1:38564", "active" : true, "currentOpTime" : "2021-03-23T13:33:57.225-0400", "opid" : "shard01:722388", "secs_running" : NumberLong(0), "microsecs_running" : NumberLong(24668), "op" : "insert", "ns" : "vinodh.testColl", "command" : { "insert" : "testColl", "bypassDocumentValidation" : false, "ordered" : false, "documents" : [ { "_id" : ObjectId("605a26855c15f7d20484d217"), "id" : 12020, "name" : "Dora Watson",....tId("000000000000000000000000") ], "writeConcern" : { "getLastError" : 1, "w" : "majority" }, "allowImplicitCollectionCreation" : false, "$clusterTime" : { "clusterTime" : Timestamp(1616520837, 1000), "signature" : { "hash" : BinData(0,"yze8dSs12MUKlnb7rpw5h2YblFI="), "keyId" : NumberLong("6942317981145759774") } }, "$configServerState" : { "opTime" : { "ts" : Timestamp(1616520835, 10), "t" : NumberLong(2) } }, "$db" : "vinodh" }, "numYields" : 0, "locks" : { "Global" : "w", "Database" : "w", "Collection" : "w" }, "waitingForLock" : false, "lockStats" : { "Global" : { "acquireCount" : { "r" : NumberLong(8), "w" : NumberLong(8) } }, "Database" : { "acquireCount" : { "w" : NumberLong(8) } }, "Collection" : { "acquireCount" : { "w" : NumberLong(8) } } } }
To reduce the output and project only some fields in the output:
mongos> db.aggregate( [
... { $currentOp : { allUsers: true, idleConnections: false } },
... { $match : { ns: "vinodh.testColl", microsecs_running: {$gt: 10000} }},
... {$project: { _id:0, host:1, opid:1, secs_running: 1, op:1, ns:1, waitingForLock: 1, numYields: 1 } } ] )
{ "host" : "bm-support01.bm.int.percona.com:54012", "opid" : "shard01:777387", "secs_running" : NumberLong(0), "op" : "insert", "ns" : "vinodh.testColl", "numYields" : 0, "waitingForLock" : false }
To see the output in fantasy mode, used to be pretty ?
mongos> db.aggregate( [ { $currentOp : { allUsers: true, idleConnections: false } }, { $match : { ns: "vinodh.testColl", microsecs_running: {$gt: 10000} }}, {$project: { _id:0, host:1, opid:1, secs_running: 1, op:1, ns:1, waitingForLock: 1, numYields: 1 } } ] ).pretty()
{
"host" : "bm-support01.bm.int.percona.com:54012",
"opid" : "shard01:801285",
"secs_running" : NumberLong(0),
"op" : "insert",
"ns" : "vinodh.testColl",
"numYields" : 0,
"waitingForLock" : false
}
I hope now you will have some idea on using currentOp() to check the ongoing operations.
Let’s imagine you want to kill an operation running for a long time. From the same currentOp document you identified it with, you can take the opid and kill it using killOp() method. In the example below, I used the sharded environment and so the opid is in a “shard_no:opid” format. See here for more details.
mongos> db.aggregate( [ { $currentOp : { allUsers: true, idleConnections: false } }, { $match : { ns: "vinodh.testColl" }}, {$project: { _id:0, host:1, opid:1, microsecs_running: 1, op:1, ns:1, waitingForLock: 1, numYields: 1 } } ] ).pretty()
{
"host" : "bm-support01.bm.int.percona.com:54012",
"opid" : "shard01:1355440",
"microsecs_running" : NumberLong(39200),
"op" : "insert",
"ns" : "vinodh.testColl",
"numYields" : 0,
"waitingForLock" : false
}
mongos> db.killOp("shard01:1355440")
{
"shard" : "shard01",
"shardid" : 1355440,
"ok" : 1,
"operationTime" : Timestamp(1616525284, 1),
"$clusterTime" : {
"clusterTime" : Timestamp(1616525284, 1),
"signature" : {
"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
"keyId" : NumberLong(0)
}
}
}
Conclusion
So the next time when you want to check the ongoing operations, you can use these techniques for filtering operations waiting for a lock, running on a namespace, running more than a specified time, specific operation or specific shard, etc. Also, comment here if you have any other ideas on this topic. I am happy to learn/see that as well.
Percona Distribution for MongoDB is the only truly open-source solution powerful enough for enterprise applications.