MongoDB Profiler

MongoDB Profiler

database profiler is the process of examining the data available in  existing database(s) and collecting statistics information about the database related action.

Link: http://docs.mongodb.org/manual/tutorial/manage-the-database-profiler/

Profiling Stages,

You can retrieve  update , remove and select queries but you will not be able to get more details about insert queries

The following profiling levels are available:
Level Setting
0       Off. No profiling
1       Only includes “slow” operations ( default 100ms)
2       Includes all operations


This link will tell you profiler out put result details

http://docs.mongodb.org/manual/reference/database-profiler/

Here is the deal

How we can get latest or all operation query details from system.profiler collection

As an example,i'm using "test" database and "sample"

Limit(number) will limit your out put result. if you wan to get all out put remove ".limit()" function from the command.

Enable Profiler on test database

1) check existing profiler status
db.getProfilingLevel()
      You will get 0,1 or 2

2) set profiling for all operations
db.setProfilingLevel(2)
      { "was" : 0, "slowms" : 100, "ok" : 1 }

3) check ur profiling state again,
db.getProfilingLevel()

Now I'm inserting data.

use test
db.sample.insert({_id:1 ,"text":"Sample Text"})


Here is there record in system.profile collection related to latest insert operation

     db.system.profile.find({"op":"insert"}).sort({ts:-1}).limit(1).pretty()

{
        "op" : "insert",
        "ns" : "test.sample",
        "ninserted" : 1,
        "keyUpdates" : 0,
        "numYield" : 0,
        "lockStats" : {
                "timeLockedMicros" : {
                        "r" : NumberLong(0),
                        "w" : NumberLong(1283230)
                },
                "timeAcquiringMicros" : {
                        "r" : NumberLong(0),
                        "w" : NumberLong(94889)
                }
        },
        "millis" : 1291,
        "ts" : ISODate("2014-07-15T05:19:57.958Z"),
        "client" : "127.0.0.1",
        "allUsers" : [ ],
        "user" : ""
}



Now updating

db.sample.update({_id:1},{"Text":"Sample Text New"})

This what captured in system.profile collection

{
        "op" : "update",
        "ns" : "test.sample",
        "query" : {
                "_id" : 1
        },
        "updateobj" : {
                "Text" : "Sample Text New"
        },
        "idhack" : true,
        "moved" : true,
        "nmoved" : 1,
        "nupdated" : 1,
        "keyUpdates" : 0,
        "numYield" : 0,
        "lockStats" : {
                "timeLockedMicros" : {
                        "r" : NumberLong(0),
                        "w" : NumberLong(28005)
                },
                "timeAcquiringMicros" : {
                        "r" : NumberLong(0),
                        "w" : NumberLong(19)
                }
        },
        "millis" : 28,
        "ts" : ISODate("2014-07-15T05:57:32.157Z"),
        "client" : "127.0.0.1",
        "allUsers" : [ ],
        "user" : ""
}


Remove document

db.sample.remove({_id:2})

db.system.profile.find({"op":"remove"}).sort({ts:-1}).limit(1).pretty()

{
        "op" : "remove",
        "ns" : "test.sample",
        "query" : {
                "_id" : 2
        },
        "ndeleted" : 1,
        "keyUpdates" : 0,
        "numYield" : 1,
        "lockStats" : {
                "timeLockedMicros" : {
                        "r" : NumberLong(0),
                        "w" : NumberLong(85442)
                },
                "timeAcquiringMicros" : {
                        "r" : NumberLong(0),
                        "w" : NumberLong(43545)
                }
        },
        "millis" : 43,
        "ts" : ISODate("2014-07-15T06:35:24.784Z"),
        "client" : "127.0.0.1",
        "allUsers" : [ ],
        "user" : ""
}


Query collection

db.sample.find({_id:1})

db.system.profile.find({"op":"query"}).sort({ts:-2}).limit(1).pretty()

{
        "op" : "query",
        "ns" : "test.system.profile",
        "query" : {
                "query" : {
                        "op" : "query"
                },
                "orderby" : {
                        "ts" : -2
                }
        },
        "ntoreturn" : 1,
        "ntoskip" : 0,
        "nscanned" : 114,
        "scanAndOrder" : true,
        "keyUpdates" : 0,
        "numYield" : 0,
        "lockStats" : {
                "timeLockedMicros" : {
                        "r" : NumberLong(931),
                        "w" : NumberLong(0)
                },
                "timeAcquiringMicros" : {
                        "r" : NumberLong(7),
                        "w" : NumberLong(6)
                }
        },
        "nreturned" : 1,
        "responseLength" : 400,
        "millis" : 0,
        "ts" : ISODate("2014-07-15T06:53:58.221Z"),
        "client" : "127.0.0.1",
        "allUsers" : [ ],
        "user" : ""
}

Point in Time Recovery - MongoDB

In production environment database management system should have this feature since there can be situation where you need to recover your database for given time( database looks like same state for given point )

This is well-defined and valuable process for current database management system. MS SQL Server , Oracle DB2 etc  have good steps to do point in time recovery and you can find lot of information regarding these type of RDBMS.

But with NoSQL Technology very less resources are available for this topic and today I'm going to explain how we can do Point in time recovery in MongoDB.

Important : 

When you get stuck with problem in mongo databases better to start with new mongodb instance and do all the restoring to that instance and test/validate. if everything looks good for you then you can transfer data to appropriate place like primary server and allow replication propagate the corrected records to the secondaries. 

Problem : 

The backupDB database has one collection, backupColl. At midnight every night, the system is backed up with a mongodump. 

Your server continued taking writes for a few hours, until 02:46:39. At that point, someone (not you) ran the command:

 db.backupColl.drop()

Your job is to put your database back into the state it was in immediately before the database was dropped, 

Answer :

Step 1 :  Restore your latest backup file into new mongodb instance. this is server going to be your test server.

mongorestore -h <hostname:port> <backup file path>

Step 2 : Take oplog backup from existing server ( better to take it form Primary or large oplog file among member servers). Oplog file keep all operations and it is capped fire but it is not a BACKUP file.

mongodump -h <hostname:port> -d local -c oplog.rs -o oplogD

Step 3 : Move and rename this "oplog.rs.bson" file to "oplog.bson"

mkdir oplogR
mv oplogD/local/oplog.rs.bson oplogR/oplog.bson

Step 4:  Then you have to find exact timestamps for delete operation happened. for that you can convert your backup oplog.bson file. you can use dumpmongo command to convert this file to human readable format.

bsondump oplog.bson > oplog.txt  

bsondump oplog.bson > oplog.json

Then you can use grep command or simple find mechanism to find "drop" keyword. if not you can you your existing database to find this 

db.oplog.rs.find()

Your goal would be find "ts" field for given keyword ("drop")

"ts" : Timestamp( 1398778745, 1 )

Please make this value like this : 1398778745:1

Step 5: Note that the mongorestore command has two options, one called --oplogReplay and the other called oplogLimit. You will now replay this oplog on the restored stand-alone server BUT you will stop before this offending update / delete operations. ( basically server should not be modified )

mongorestore -h <hostname:port> --oplogReplay --oplogLimit 1398778745:1 oplogR

This will restore each operation from the oplog.bson file in oplogR directory stopping right before the entry with ts value Timestamp( 1398778745, 1 )

Step 6: Once you have verified it then you can write the restored records to the appropriate place in the real primary (and allow replication propagate the corrected records to the secondaries).

High Availability on MongoDB

This is article completely summary of official mongo site : http://docs.mongodb.org/manual/core/replica-set-high-availability/

Replica Set

• Replica set provides High Availability using automatic failover. Failover allows secondary members to become primary if primary is unavailable. Failover, in most situations does not require manual intervention.
•  Replicas set members keep the same data set but they are independent.
• To select Primary Server there will be an election between other servers. Place a majority of voting members and all the members that can become primary in this facility.

Replica Set Elections

•          Replica sets use elections to determine which set member will become primary. Election occurs after initiating a replica set, and also any time the primary becomes unavailable
•          The primary is the only member in the set that can accept write operations.
•          Elections are part of the failover process
•          While an election is in process, the replica set has no primary and cannot accept writes.

Factors and Conditions that Affect Elections

Heartbeats

•          Replica set members send heartbeats (pings) to each other every two seconds. If a heartbeat does not return within 10 seconds, the other members mark the delinquent member as inaccessible.

Priority Comparisons

•          The Priority setting affects elections. Priorities specify higher values to make a member more eligible to become primary and lower values to make the member less eligible to become primary.
•          Priority 0 = Cannot become primary and do not seek election

Optime

•          The timestamp of the last operation that a member applied from the oplog.

      Connections

•    A replica set member cannot become primary unless it can connect to a majority of the members in the replica set

Election Triggering Events

•          Replica sets hold an election any time there is no primary. Specifically, the following:
•          The initiation of new replica set.
•          A secondary loses contact with a primary. Secondaries call for elections when they cannot see a primary.
•          A primary step down.

o   After receiving the replSetStepDown command

o   If one of the current secondaries is eligible for election and has a higher priority

o   If primary cannot contact a majority of the members of the replica set.

Note: When a primary steps down, it closes all open client connections, so that client don’t attempt to write data to a secondary. This helps client maintain an accurate view of the replica set and helps prevent rollbacks.

Participation in Elections

• By default, all members have a priority of 1 and have an equal chance of becoming primary. In the default, all members also can trigger an election.
• Only members in the following states can vote: PRIMARY, SECONDARY, RECOVERING, ARBITER, and ROLLBACK.

Non-Voting Members

• Non-voting members hold copies of the replica set’s data and can accept read operations from client applications
• Non-voting members do not vote in elections, but can “veto” an election and become primary.

It is about MongoDB indexes.

No need to re-write again. Yeah this article is so simple to read and understand.

Link : http://docs.mongodb.org/manual/core/indexes-introduction/

Thank you very much 10gen come up with good articles. seems like MongoDB documentation is getting more easy to read and understand.



 

sys.sysindexes

Today, I’m going to discuss about one of the system view called “sys.sysindexes”. This contains one row for each index  in the current database.

• To Get All the Index in Current Database

         SELECT * FROM sysindexes

• To Get All the Indexes on “Response” table

         SELECT * FROM sysindexes where id = object_id('Response')

• To get the row count in “Response” Table

         SELECT rowcnt FROM sysindexes where id = object_id('Response') and indid < 2 

what is the mean about "indid " :
Answer : ID of the index:
                    0 = Heap
                    1 = Clustered index
                    >1 = Nonclustered index

Even though you are trying to select the 2 rows (indid < 2 ) , you will never get it. Because your table ('Response') is either Heap or Clusted/non-clusted index table.

You can use this view to get the row count easily without putting weight on the table.

Keep in mind :

XML indexes are not supported

Partitioned tables and indexed are not fully supported

More info : http://technet.microsoft.com/en-us/library/ms190283.aspx

Top 10 New Features in SQL 2012

http://blogs.technet.com/b/jweston/archive/2012/04/18/top-10-new-features-in-sql-2012.aspx

The Logical Query Processing in T-SQL

Today I’m going to discuss about the Logical Query Processing in T-SQL

Logical Query Processing:  The conceptual interpretation of the query that explain what the correct result of the query is

Physical Query Processing:  Processing of the query by database engine. Produce the result defined by logical query processing.

Following are the Main Query Clauses in the order that you are supposed to type (known as “keyed-in-order”)

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY

But the Logical Query Processing order, which is the Conceptual interpretation order, is different

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Because of this logical order you won’t get expected result for below sample query,

SELECT Name , YEAR(Joindate) AS JoinDate

FROM dbo.Emplyees

WHERE JoinDate > 2000

This should be,

SELECT Name , YEAR(Joindate) AS JoinDate

FROM dbo.Emplyees

WHERE YEAR(Joindate)  > 2000