MongoDB Processlist:
As a DBA from MySQL background. I always wanted to quickly visualise what process / queries running inside my MongoDB server just like i do “show processlist” in MySQL.
db.currentOp():
https://docs.mongodb.com/manual/reference/method/db.currentOp/
- MongoDB command “db.currentOp()” can be used for this purpose.
- But the command returns a lengthy JSON which is hard to quickly grasp the output.
- The output also includes replication threads, system threads, Which i least care about.
MongoDB Compass:
https://www.mongodb.com/products/compass
- If you have remote access to MongoDB database, You can use MongoDB Compass GUI tools, tool to see the queries running inside the server, But it has few drawbacks.
- we cannot set a custom refresh rate.
- As the refresh rate is high, It is very hard to copy the queries if want to capture it. MongoDB error log can be used for it, But it’s just a another job for me.
Problem Statement:
Though we had multiple tools including some of them i highlighted above, Those didn’t served my purpose. Finally i decided to pen down the problem statement / my expectations for effective MongoDB administration.
- To have quick look on queries running inside the MongoDB server.
- To set custom refresh interval for processlist check.
- Capture the queries i see on the processlist.
- Highlight the queries if it’s in a lock state.
- Ability to kill queries beyond certain threshold.
- Log the killed queries or perform Dry run and capture the queries running beyond certain threshold.
- Ability to check the MongoDB document / operation stats, Just like we see from mongostat, integrated along with the processlist.
- To check for replication lag information for the provided replica set.
MongoDB Process Checker:
We created a python based utility for MongoDB ‘mongo_process_checker’ that aims to solve the problem statements i have listed.
- Check the queries running inside MongoDB:
By passing the host, user, password details, mongo_process_checker connects to the corresponding MongoDB server and check for the current process and displays them in a readable format.
- Save what you see:
Prefix – hostname or last two octets in case of ip address.
Suffix – datetime in format DD-Mon-HH-MM
By default this utility saves / appends the information you see this screen in the file prefixed by the MongoDB hostname or the last two octets of the ip address and suffixed by the time (hour-min) we started the utility.
In our example, We have used 10.0.10.100 IP address.
As we’re saving the information that is displayed during the run time of the tools, It’s easier to look back in the file to see if find something interesting.
- Set custom refresh interval:
By default output of process list refreshes every 4 sec, It can be tweaked by setting refresh interval to the required value.
- Kill beyond threshold:
As by default, this utility checks and prints the process every 4 secs and kill queries that is running beyond 5 secs.
To perform check at custom interval, you can club args -k along with -i.
- Save what is Killed:
Prefix – hostname or last two octets in case of ip address.
Suffix – datetime in format DD-Mon-HH-MM
Just like capturing the queries in processlist, This utility saves the queries killed by default in the file in above format.
- Check for replication lag:
By passing -r argument, It displays the replication information along with the processlist details.
Replication information contains, Oplog size, Duration of writes stored on Oplog, Replication lag details of each member of the replica set.
- Check for Document, Operation, Network, Connection Details:
We can able to get the same stats for document, operations, network from ‘mongostat’ tool too, But with this utility we can able to visualise those stats along with the queries running inside the servers, That helps us to easily co-relate things.
This can also be clubbed with other options like -r to display replication stats along with that.
Download:
https://github.com/mydbops/Mydbops_toolset/blob/master/mongodb_process_checker.py
Python Dependencies To Meet:
pymongo argparse getpass urllib commands sys time re prettytable bson.json_util
Note:
I have written this code with limited python skills, Any suggestions on improvements are welcome.