DBA TO CLOUD AND BIGDATA SOLUTION: Performance Tuning Considerations

As per Performance Tuning concerns, we have below Components :-

Secondary indexes - If you consider any NoSQL database, it will not support secondary indexes. But Mongodb will support many type of indexes, It can be single field index, multiple fields index, multi key index and index text index. Secondary index will use to use the improve the performance of the database.

Replication - You can have maximum 11 replication copies of nodes.

Primary will act as master.

Secondary will act as slave

Normal Configuration with out replication :

Read and Write operation will happen in same database.

Replication Configuration:

Primary for read but it can be used for read/write also. But primarily used for read operation in primary. Secondary server used for write operations.

Sharding : When ever the collection is having huge volume of data. Entire MongoDB can be shard with that only I/O will be distributed.

When ever a collection is having huge volume of data we can make a shard for individual database and sharding can be done for collection. The rebalancing will happen automatically in Sharding that we have to care .

Storage Engine:

Two types of Engine : WiredTiger, MMAPv1

Depending on operation we can choose the storage engine and we can configure our storage engine. We have option flexibility of tuning in mongoDB

*Log Rotate: Log-rotate command will be executed, the log file will be backed up and then it will be purged. So that your log-file will become smaller in size. So that process can write faster. When users will do very short transaction randomly, in that case the process taking time to perform an activity to write to log file will also affect the user performing activity in that case we can purge the log-file using log rotate option.

Steps: (logfile rotation activity)

[mdashok@MongoDB log]$ pwd

/data/log

[mdashok@MongoDB log]$

[mdashok@MongoDB log]$ du -sh mongo.log

4.0K mongo.log

[mdashok@MongoDB log]$

[mdashok@MongoDB log]$ du -sb monogo.log

1482 monogo.log

[mdashok@MongoDB log]$

# This below command will do the logRotate command activity

MongoDB Enterprise > db.runCommand ({ logRotate : 1 });

{ "ok" : 1 } ----------------> true

MongoDB Enterprise >

# Logfile backup is generated with extension current date "2018-09-14T21-36-28"

[mdashok@MongoDB log]$ du -sb monogo.log*

1076 monogo.log -----------> original logfile

1551 monogo.log.2018-09-14T21-36-28 -----------> backup file

[mdashok@MongoDB log]$ date

Sun Oct 14 07:32:44 IST 2018

[mdashok@MongoDB log]$

Statement Tuning

1. Profile :
For a mongod instance, the command enables, disables, or configures the Database Profiler. The profiler captures and records data on the performance of write operations, cursors, and database commands on a running mongod instance. If the profiler is disabled, the command sets the slowms and sampleRate for logging slow operations to the diagnostic log.

MongoDB Enterprise > db.getProfilingStatus()

{ "was" : 0, "slowms" : 100, "sampleRate" : 1 }

MongoDB Enterprise > show profile

db.system.profile is empty

Use db.setProfilingLevel(2) will enable profiling

Use db.system.profile.find() to show raw profile entries

MongoDB Enterprise >

MongoDB Enterprise > db.setProfilingLevel(2,50);

{ "was" : 0, "slowms" : 100, "sampleRate" : 1, "ok" : 1 }

MongoDB Enterprise > db.getProfilingStatus();

{ "was" : 2, "slowms" : 50, "sampleRate" : 1 }

MongoDB Enterprise >

MongoDB Enterprise > show profile

db.system.profile is empty

Use db.setProfilingLevel(2) will enable profiling

Use db.system.profile.find() to show raw profile entries

MongoDB Enterprise >

MongoDB Enterprise > db.system.profile.find();

MongoDB Enterprise > db.system.profile.find()

MongoDB Enterprise > db.setProfilingLevel(2);

{ "was" : 0, "slowms" : 50, "sampleRate" : 1, "ok" : 1 }

MongoDB Enterprise > db.system.profile.find();

MongoDB Enterprise > show collections

MongoDB Enterprise > db

test

MongoDB Enterprise >

Profile is saved in capped collection#

MongoDB Enterprise > db.system.profile.stats()

{

"ns" : "test.system.profile",

"size" : 749,

"count" : 1,

"avgObjSize" : 749,

"storageSize" : 16384,

"capped" : true,

"max" : -1,

"maxSize" : 1048576,

"sleepCount" : 0,

"sleepMS" : 0,

"wiredTiger" : {

"metadata" : {

"formatVersion" : 1

Drop profile#

MongoDB Enterprise > db.systen.profile.drop()

false

MongoDB Enterprise >

We need to set the profile first.

MongoDB Enterprise > db.getProfilingStatus()

{ "was" : 2, "slowms" : 50, "sampleRate" : 1 }

MongoDB Enterprise >

MongoDB Enterprise > db.setProfilingLevel(0,50);

{ "was" : 2, "slowms" : 50, "sampleRate" : 1, "ok" : 1 }

MongoDB Enterprise >

MongoDB Enterprise > db.system.profile.drop()

true

MongoDB Enterprise >

MongoDB Enterprise > show collections

MongoDB Enterprise >

MongoDB Enterprise > db.createCollection("system.profile", { capped: true,size: 20 * 1024 * 1024} );

{ "ok" : 1 }

MongoDB Enterprise >

{

"ns" : "test.system.profile",

"size" : 0,

"count" : 0,

"storageSize" : 4096,

"capped" : true,

"max" : -1,

"maxSize" : 20971520,

"sleepCount" : 0,

"sleepMS" : 0,

"wiredTiger" : {

"metadata" : {

"formatVersion" : 1

MongoDB Enterprise >

MongoDB Enterprise > db.getProfilingStatus()

{ "was" : 0, "slowms" : 50, "sampleRate" : 1 }

MongoDB Enterprise >

MongoDB Enterprise > db.system.profile.stats();

{

"ns" : "test.system.profile",

"size" : 0,

"count" : 0, -------------------> no query is exectuted

I )

MongoDB Enterprise > db.system.profile.aggregate ({$group: {_id: "$op", count: {$sum:1}, "max response time" : {$max: "$millis"}, "avg response time": {$avg: "$millis"}}}) ;

{ "_id" : "query", "count" : 2, "max response time" : 0, "avg response time" : 0 }

{ "_id" : "command", "count" : 3, "max response time" : 0, "avg response time" : 0 }

MongoDB Enterprise >

MongoDB Enterprise > db.createCollection("doccollect");

{ "ok" : 1 }

MongoDB Enterprise > for (var i=1; i <=500; i++) db.docccollect.insert ( { SL_No: i})

WriteResult({ "nInserted" : 1 })

MongoDB Enterprise > for (var i=1; i <=500; i++) db.docccollect.insert ( { SL_No: i})

WriteResult({ "nInserted" : 1 })

MongoDB Enterprise > db.docccollect.find().count();

1000

MongoDB Enterprise >

II)

MongoDB Enterprise > db.docccollect.find();

{ "_id" : ObjectId("5bc2b77dec41e927c4064a6a"), "SL_No" : 1 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a6b"), "SL_No" : 2 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a6c"), "SL_No" : 3 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a6d"), "SL_No" : 4 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a6e"), "SL_No" : 5 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a6f"), "SL_No" : 6 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a70"), "SL_No" : 7 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a71"), "SL_No" : 8 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a72"), "SL_No" : 9 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a73"), "SL_No" : 10 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a74"), "SL_No" : 11 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a75"), "SL_No" : 12 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a76"), "SL_No" : 13 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a77"), "SL_No" : 14 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a78"), "SL_No" : 15 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a79"), "SL_No" : 16 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a7a"), "SL_No" : 17 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a7b"), "SL_No" : 18 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a7c"), "SL_No" : 19 }

{ "_id" : ObjectId("5bc2b77dec41e927c4064a7d"), "SL_No" : 20 }

Type "it" for more

MongoDB Enterprise > db.system.profile.aggregate ({$group: {_id: "$op", count: {$sum:1}, "max response time" : {$max: "$millis"}, "avg response time": {$avg: "$millis"}}}) ;

{ "_id" : "insert", "count" : 1000, "max response time" : 27, "avg response time" : 0.027 }

{ "_id" : "query", "count" : 4, "max response time" : 0, "avg response time" : 0 }

{ "_id" : "command", "count" : 8, "max response time" : 29, "avg response time" : 3.625 }