DBA TO CLOUD AND BIGDATA SOLUTION: October 2018

Tuesday, October 30, 2018

Shared Memory

hared Buffer

WAL Buffer

PostgreSQL has four process types.

Postmaster (Daemon) Process

Background Process

Backend Process

Client Process

Vacuum

Monday, October 29, 2018

Sharding Definition :

Sharding in MongoDB is the process of storing data records across multiple machines. It is an approach to meet the demands of data growth. As the data size increases, a single machine might not be sufficient for storing the data nor provides an acceptable read and write throughput. Sharding can solve the problem with horizontal scaling. With sharding, you can add more machines for supporting data growth and the demands of read and write operations.

Sharding Cluster Configuration

Config Servers − Config servers will store the cluster's metadata and this data contains a mapping of the cluster's data set to the shards. Query router will use this metadata to target operations for specific shards. In any production environment, sharded clusters will have exactly 3 config servers.

Query Routers − Query routers are mainly mongo instances that interface with client applications and will direct operations to the appropriate shard. Query router will process and target the operations to shards and will then return results to the clients. A sharded cluster consists of more than one query router for dividing the client request load and a client will send requests to one query router. Basically, a sharded cluster can have many query routers.

Shards Shards are used for storing data and they provide high availability and data consistency. In the production environment, each shard will be a separate replica set.

Friday, October 26, 2018

MongoDB padding factor (Performance Tuning)

If you modify a document in such a way that it grows the document size, there is a chance that MongoDB will have to move the document to some other place in the physical file. There is fixed cost involved in the move and Mongo may decide to grow the size of your data files to accommodate the move.

To account for this, Mongo maintains a discover on how often documents in each collection grow and then pads some extra space around the document in the physical file so that the document can grow a bit and not have to be moved. This discover is called the padding factor (or paddingFactor).

If the padding factor is higher for a collection, it indicates that the documents are growing.

Update/ReIndex to take care of the performance.

Friday, October 19, 2018

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 }