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:
# python mongo_process_checker -H -u -p
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:
Format: _queries_.txt
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:
# python mongo_process_checker -H -u -p -i
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:
# python mongo_process_checker -H -u -p -k
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:
Format: _killed_.txt
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:
# python mongo_process_checker -H -u -p -r
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:
# python mongo_process_checker -H -u -p -v
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.
When trying to execute your script on Linux machine with following commond , getting below error:
/usr/bin/python mongodb_process_checker.py -H -u **** -p
Traceback (most recent call last):
File “mongodb_process_checker.py”, line 8, in
from prettytable import PrettyTable
ImportError: No module named prettytable
Can you please assist ?
LikeLike
Dear Bhushan Lipare,
you have to install the below python dependencies as i have stated in the blog.
“pymongo, argparse, getpass, urllib, commands, sys, time, re, prettytable, bson.json_util”
these dependencies can be easily installed through python pip.
if you dont have python pip installed, use the following link: https://www.tecmint.com/install-pip-in-linux/ to install pip
after pip installation, you can install the dependencies
# sudo pip install pymongo argparse getpass urllib commands sys time re prettytable bson
LikeLike
Thanks for sharing the blog on cron.weekly.
LikeLike
Hi Vinoth,
Thanks for help.
Is that script only display processlist only if queries running on application DBs or it’s capture processlist of all database like local.oplog , admin database also?
because when i am running script on one our load env. Its showing me result like “No Queries To Display” . is it because currently there query running on local.oplog?
Thanks
Bhushan L
LikeLike
Dear Bhushan Lipare,
This tool displays all the active queries running inside your mongoDB server across all the databases except the system threads (replication threads / local.oplog -> which is used for replication) etc,. So it gives a neat picture.
“No Queries To Display” means, most of your queries are running in ms or server processed all the queries. Meaning during the check execution there was no queries running on the server except the system threads.
This tool internally fires db.currentOp() to capture the process list information and display it, So this behaviour can be related to that of currentOp commnd behaviour.
https://docs.mongodb.com/manual/reference/method/db.currentOp/
Hope this is helpful.
Thanks,
Vinoth Kanna RS
LikeLiked by 1 person
Hey thanks Vinoth for such valuable information about script.
LikeLike
Hi Vinod,
is script displays information about “Command ” query because in our mongo log we are getting queries which are in command state but its not displaying using script .
Any idea??
Thanks
Bhushan L
LikeLike
@vinoth
can you add linux uptime commmand in to it.
currently you are displaying only timestamp instead of this if you use uptime will get load info also .
LikeLiked by 1 person
I have two problems
python –version
Python 3.6.3
pip install pymongo argparse getpass urllib commands sys time re prettytable bson
Collecting getpass
Could not find a version that satisfies the requirement getpass (from versions: )
No matching distribution found for getpass
python mongo_process_checker.py -H xxx -P xxx -u xxx -p
File “mongo_process_checker.py”, line 297
except pymongo.errors.PyMongoError, e:
^
SyntaxError: invalid syntax
I am looking forward to your helping
LikeLike
Hi Lee,
Thanks for bringing it to notice, This program is only compatible with Python 2
We will try to check the compatibility issue, But I don’t have guaranteed ETA on this. Any contribution to the code are really welcome.
LikeLike
HI Vinoth,
Good job with this tool. The tool looks promising but not able to have it installed in 2.7.5. Can you please upload the requirements.txt using the below command in your git repo. I too had several dependency issues while setting up the environment to test this out.
pip freeze > requirements.txt
Thanks,
Santhinesh
LikeLike
Thanks for giving it a try Santhinesh,
Please find the requirements in the following link.
LikeLike
Awesome work done, many thanks to Vinoth. This script would defiantly ease lots of DBAs day to day activities related to monitoring and also to gather other information.
LikeLike
Seems very helpful script Vinoth, thanks for broadcasting!!
I have an issue with dependencies for bson.json_util while running the script. Have taken the reference link you provided in your previous comment but I cannot do pip installation as I don’t have internet on the server.
Have installed bson tar package i.e bson-0.5.8 but no success and getting below error
Error:
from bson.json_util import dumps
importError: cannot import name dumps
Can you please assist with a workaround to resolve this problem, appreciate your kind response.
Thanks,
Sameer
LikeLike
Hi Vinoth,
This is an awesome innovation and would definitely like to experience it.
I had followed all the steps and reviewed the comments as well but am unable to run the script as it’s giving me error as
“FIle ‘mongodb_process_checker.py’, line 9, in from bson.json_util import dmps ImportError: cannot import name dumps”
I followed the solutions provided on google but couldn’t find the tar for dumps dependencies.
Can you please assist here and direct me to the link from where I can resolve this dependency.
Looking forward to hearing back from you, thanks in advance!
Thanks,
Sameer
LikeLike