MongoDB Process List In MySQL Way

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.

Screen Shot 2017-09-21 at 12.08.35 PM.png

  1. Check the queries running inside MongoDB:
# python mongo_process_checker -H  -u  -p 

Screen Shot 2017-09-21 at 12.22.48 PM.png

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.

  1. 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.

Screen Shot 2017-09-21 at 12.38.07 PM.png

Screen Shot 2017-09-21 at 12.43.39 PM.png

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.

  1. 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.

Screen Shot 2017-09-21 at 1.02.37 PM.png

  1. Kill beyond threshold:
# python mongo_process_checker -H  -u  -p  -k 

Screen Shot 2017-09-21 at 12.55.28 PM.png

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.

Screen Shot 2017-09-21 at 1.08.37 PM.png

  1. 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.

Screen Shot 2017-09-21 at 1.20.39 PM.png

  1. 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.

Screen Shot 2017-09-21 at 1.29.42 PM.png

Replication information contains, Oplog size, Duration of writes stored on Oplog, Replication lag details of each member of the replica set.

  1. 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.

Screen Shot 2017-09-21 at 1.40.27 PM.png

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.

 

Advertisements

9 thoughts on “MongoDB Process List In MySQL Way

  1. 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 ?

    Like

  2. 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

    Like

  3. 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

    Like

  4. 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

    Liked by 1 person

  5. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s