Performance transaction script

Ops and Admin
,
1

Hi Team,

We need db.current(true ) output send to mail if any query more than 1 second run send to mail else do not send to mail

above condition how to write shell script so any body please help me script full detail.

2

Hi @hari_dba,

Firstly, I assume you mean db.currentOp(true)?
Secondly, I think I would use the profiler db.setProfilingLevel(1, { slowms: 1000 }). This will add slow queries in the collection system.profile and provide some information about them.

Exemple:

{
    op: 'insert',
    ns: 'test.users',
    command: {
        insert: 'users',
        documents: [{
            name: 'Max',
            _id: ObjectId("62262bf2036c3d3f383580d6")
        }],
        ordered: true,
        lsid: {
            id: UUID("e6760d4b-7f0b-4b30-809c-5febe4c27a3b")
        },
        txnNumber: Long("10"),
        '$clusterTime': {
            clusterTime: Timestamp({
                t: 1646668785,
                i: 3
            }),
            signature: {
                hash: Binary(Buffer.from("0000000000000000000000000000000000000000", "hex"), 0),
                keyId: Long("0")
            }
        },
        '$db': 'test'
    },
    ninserted: 1,
    keysInserted: 1,
    numYield: 0,
    locks: {
        ParallelBatchWriterMode: {
            acquireCount: {
                r: Long("2")
            }
        },
        ReplicationStateTransition: {
            acquireCount: {
                w: Long("5")
            }
        },
        Global: {
            acquireCount: {
                r: Long("2"),
                w: Long("2")
            }
        },
        Database: {
            acquireCount: {
                w: Long("2")
            }
        },
        Collection: {
            acquireCount: {
                w: Long("2")
            }
        },
        Mutex: {
            acquireCount: {
                r: Long("2")
            }
        }
    },
    flowControl: {
        acquireCount: Long("1"),
        timeAcquiringMicros: Long("1")
    },
    readConcern: {
        level: 'local',
        provenance: 'implicitDefault'
    },
    writeConcern: {
        w: 'majority',
        wtimeout: 0,
        provenance: 'implicitDefault'
    },
    storage: {},
    responseLength: 230,
    protocol: 'op_msg',
    millis: 6,
    ts: ISODate("2022-03-07T15:59:46.175Z"),
    client: '127.0.0.1',
    appName: 'mongosh 1.1.9',
    allUsers: [],
    user: ''
}

Sadly, you can’t use Change Streams on system collections in MongoDB so using a Change Stream on system.profile would have been cool, but it’s not possible.

So I would use CRON in this case and run a query every X minutes to see if a new slow query was added in the past X minutes (based on the “ts” field) in this collection and then send an email if something was found.

Cheers,
Maxime.

3

Yes correct i am looking " db.currentOp(true) "

I need script and run in CRON. So please send me script details.

4

I never wrote a script like this and I don’t have time to write it. But please share it with the community when you come up with something that the community could reuse.

5

Hi Team,

Any body have script please share me this is important my project.
My team was created one script below as mentioned what we are excepted that out put not generated. at least could you guide below script are we modify anything ?

MONGO_HOME=/hom/bin

$MONGO_HOME/mongo --host mongodb:27017 -u ‘hari’ -p ‘xxxx’
–authenticationDatabase admin --quiet < perf.js > perf.txt
if [ $? -eq 0 ]
then
mailx -s “QUERY TAKING MORE THAN 1 SECOND” hari@xxx.com < perf.txt
else
echo “Unable to connect to MongoDB” | mail -s “Issue while connecting to ADB DB” hari@xxx.com
fi

======================================================================================================

$ cat perf.js
var result=db.currentOp({“active” : true,“secs_running” : { “$gt” : 1 }})
printjson(result)

My management excepted conditions like this :

  1. at any query executed more than 1 second send to mail or do not send mail
  2. once primary not available then which secondary become as primary from primary server executed same script

How to setup that script and deploy ?

6

Hi Team,

Please any suggestion …

7

I need schedule wise out put of the db.currentOp({“active” : true,“secs_running” : { “$gt” : 1 }})

9

It works for me, i found special characters i your command. the double quote is the culprit. try this,

var result=db.currentOp({“active” : true,“secs_running” : { “$gt” : 1 }});
printjson(result);