MongoDB Commands

-

Export collection without ID field:


mongo --host dfwlnpmgdb-03:27018 cpq_prod --quiet --eval "db.PricingTable.find({}, {_id: 0}).forEach(printjson)" > /home/kolguk01/export/out.json


Create/DROP Index:



Note: Always use background set to true while creating the indexes

db.css.dropIndex("ts_365_days")
db.css.dropIndex("url_1_src_1")
db.css.dropIndex("_id_")

db.css.createIndex( { "ts" : 1}, {"name" : "ts_365_days", "expireAfterSeconds" : 31536000,background : true})
db.css.createIndex( { "url" : 1, "src" : 1}, {"name" : "url_1_src_1", "unique" :true,background : true})
db.css.createIndex( { "_id" : 1}, {"name" : "_id_" ,background : true})


Select documents without the _id field



  1. db.collection.find({}, {_id: 0})


How to configure Replica set in MongoDB


Step 1 : In the configuration file, specify the replica set name

Step 2: Start Mongod 

Step 3: Run the below command to initiate the replica set

                rs.initiate()

Step 4: Add members to the replica set
           rs.add("servername:port")

Step 5: Check the Replica Status
               rs.status() 
#Sh.status() - to check sharding status

sudo /etc/init.d/mongod status - To check status
Ps -ef|grep mongo  - To check all the services status

sudo /etc/init.d/mongod start -  To start the process
sudo /etc/init.d/mongod stop


rs.remove("mongod3.example.net:27017")


rs.add("mongodb3.example.net")



db.dropDatabase()







mongo --host DFWLNDCPQDB-01 --port 27019
 mongo --host dfwlnpmgdb-03 --port 27018
mongo --host dfwlnpmgdb-02 --port 27018
mongo --host dfwlnpmgdb-01 --port 27018

************************************************************************************************************************


Check size

df -h /var/lib/mongo  - disk usage

************************************************************************************************************************


Read Log
tail -f /var/log/mongodb/mongod.log -- check log

************************************************************************************************************************


Set wiredtiger cache size:
--------------------------------------------------
db.adminCommand( { "setParameter": 1, "wiredTigerEngineRuntimeConfig": "cache_size=50G"})

************************************************************************************************************************


MongoDump

Mongodump  --db cpq_pm  --collection  Admin  --out  /mongo_dump/cpq_pm


mongorestore --host dfwlnqcpqdb-02 --port 27019 --db cpq_cfit --username administrator --password ohThohmiepoi0que  /mongo_temp1/cpq_prod



db.Quote.remove( { last_priced: { $lt: ISODate("2017-01-15T00:00:00Z") } } )


************************************************************************************************************************


Check the current operations on a DB

db.currentOp({"active" : true,"secs_running" : { "$gt" : 3 },"ns" : /^cpq_cfit\./})

db.currentOp({"active" : true,"secs_running" : { "$gt" : 3 },"ns" : /^dbname\./})

db.currentOp({$or: [{ op: "query", "query.createIndexes": { $exists: true } },{ op: "insert", ns: /\.system\.indexes\b/ } ] })  - Index progress

Check all idle connections

db.currentOp(true)


************************************************************************************************************************
Mongodump

mongodump --host <hostname-of-mongoserver> --db <db-name> --collection <collection-name> --username <dbuser-name> --password <password> --gzip --out </backup/location/>

Exclude collection while taking dump

mongodump --host dfwlnqcpqdb-01:27019 -d consumer  --out /home/kolguk01/consumer

************************************************************************************************************************


Run Restoration in Background
Mongo Restore

nohup mongorestore --host dfwlnqcpqdb-01 --port 27019 -u administrator -p ohThohmiepoi0que --db cpq_cfit /mongo_temp/cpq_refresh/cpq_prod &

mongorestore --host dfwlnqmgdb-03 --port 27018   --db  consumer /home/kolguk01/consumer

************************************************************************************************************************


Collection Size

Db.collectionname.stats()


************************************************************************************************************************


Transactions Count

mongostat --host dfwlnpmgdb-01 --port 27018
insert query update delete getmore command % dirty % used flushes vsize  res qr|qw ar|aw netIn netOut conn  set repl     time
     1  1318      7      6       8    34|0     0.2   79.6       0  3.6G 2.8G   0|0   2|0  112k   532k  136 prod  PRI 14:32:18
    *0  1255      3      6       3    22|0     0.2   79.6       0  3.6G 2.8G   0|0   2|0   99k   454k  136 prod  PRI 14:32:19
    *0  1327      4      8       4    22|0     0.2   79.6       0  3.6G 2.8G   0|0   1|0  106k   519k  136 prod  PRI 14:32:20
    *0  1263      3      6       3    20|0     0.2   79.6       0  3.6G 2.8G   0|0   1|0  100k   493k  136 prod  PRI 14:32:21
     1  1282      4      6       5    24|0     0.3   79.6       0  3.6G 2.8G   0|0   1|0  105k   498k  136 prod  PRI 14:32:22
    *0  1290      6      6       6    31|0     0.3   79.6       0  3.6G 2.8G   0|0   1|0  220k   644k  136 prod  PRI 14:32:23
     7  1312     10      6      11    36|0     0.3   79.6       0  3.6G 2.8G   0|0   1|0  126k   545k  136 prod  PRI 14:32:24
     2  1310      8      7       5    23|0     0.3   79.6       0  3.6G 2.8G   0|0   1|0  109k     2m  136 prod  PRI 14:32:25
     1  1279      3      6       4    25|0     0.3   79.6       0  3.6G 2.8G   0|0   1|0  103k     1m  136 prod  PRI 14:32:26
    *0  1284      4      6       4    25|0     0.3   79.6       0  3.6G 2.8G   0|0   1|0  103k   533k  136 prod  PRI 14:32:27

mongotop -h dfwlnqmgdb-02 --port 27018

mongotop -h dfwlnpmgdb-02 --port 27018

mongotop -h dfwlnqcpqdb-02 --port 27019 -u administrator-p ohThohmiepoi0que


                  ns    total    read    write    2017-07-28T14:53:12-05:00
graylog2.streamrules     70ms    70ms      0ms                            
    ucc_prod.storage     29ms     0ms     28ms                            
      graylog2.nodes     15ms     0ms     14ms                            
     graylog2.inputs      4ms     4ms      0ms                            
    graylog2.streams      2ms     2ms      0ms                            
      local.oplog.rs      1ms     1ms      0ms                            
      ucc_prod.users      1ms     1ms      0ms                            
  admin.system.roles      0ms     0ms      0ms                            
  admin.system.users      0ms     0ms      0ms                            
admin.system.version      0ms     0ms      0ms 





************************************************************************************************************************

USERS

Add Users


Add admin user -- 2.2 version

db.addUser( "user", "password", "readWrite");

Add admin user -- 3.0 version

  db.createUser(
  {
    user: "username",
    pwd: "password",
    roles: [
       { role:  "readWrite" ,db: "dbname"}

    ]
  }
)

db.updateUser( "appClient01",
               {
                 customData : { employeeId : "0x3039" },
                 roles : [
                           { role : "readWrite", db : "ucc_gridfs"  }
                         ]
                }
             )







db.removeUser('kalbhm01')


db.system.users.find({"user":"testkishore"})  -- Find a specific user from admin database

db.system.users.remove({"user":"testkishore"}) -- Remove a specific user using admin database


db.grantRolesToUser


db.grantRolesToUser(   "kumarv04",   [ "readWrite" , { role: "readWrite", db: "cpq_cfit" } ])


Lock User
db.lockUser("author")

db.unlockUser("author")

Db.dropUser("test")

db.changeUserPassword("accountUser", "SOh3TbYhx8ypJPxmt1oOfL")











************************************************************************************************************************


THP Status


[root@dfwlnqmgdb-03 ~]# egrep 'trans|thp' /proc/vmstat
nr_anon_transparent_hugepages 0
[root@dfwlnqmgdb-03 ~]# grep -e AnonHugePages  /proc/*/smaps | awk  '{ if($2>4) print $0} ' |  awk -F "/"  '{print $0; system("ps -fp " $3)} '
[root@dfwlnqmgdb-03 ~]# grep -i HugePages_Total /proc/meminfo
HugePages_Total:       0
[root@dfwlnqmgdb-03 ~]# cat /proc/sys/vm/nr_hugepages
0
[root@dfwlnqmgdb-03 ~]# sysctl vm.nr_hugepages
vm.nr_hugepages = 0
[root@dfwlnqmgdb-03 ~]#


************************************************************************************************************************


Current Operation for a database

db.currentOp({"ns" :/^cpq_prod\./}) - for a specific DB

Db.currentOp() - In general


************************************************************************************************************************


Create Database


No direct command to create database

    Use MyDB       -  this will create the database - it should followed by the next command

                             --Create Collection

     db.createCollection("new1", {   size : 5242  } )

Insert Collections

      db.name.insert ( {name : "kishore"})
      db.test2.insert({name: "kishore Kumar", Place: "Texas"})

Drop Collections

   db.test.drop()

Drop Database

 db.dropDatabase()  - to drop database


Copy collection

db.<collection_name>.find().forEach(function(d){ db.getSiblingDB('<new_database>')['<collection_name>'].insert(d); });

Capped Collections

db.createCollection("system.profile", { capped: true, size:400000000 } )  -- the collection will utilize the allowed space, once reaches to the max size, it will start override






************************************************************************************************************************


Make script executable:

chmod +x mongo_backup.sh

************************************************************************************************************************
DROP DATABASE

 mongo Kishore_test  --eval "printjson(db.dropDatabase())" -u administrator -p ohThohmiepoi0que --host dfwlnqcpqdb-02 --port 27019

************************************************************************************************************************

Nohup mongo  cpq_cfit  --eval "printjson(db.createCollection("test" ))" -u mongoadmin -p p@sswrd! --host dfwlnqcpqdb-02 --port 27019 &

mongo cpq_cfit --eval "printjson(db.getCollectionNames())" -u mongoadmin -p p@sswrd! --host dfwlnqcpqdb-02 --port 27019


db.test.insertOne(  { item: "canvas", qty: 100, tags: ["cotton"], size: { h: 28, w: 35.5, uom: "cm" } })

db.test.insert(  { item: "canvas", qty: 100, tags: ["cotton"], size: { h: 28, w: 35.5, uom: "cm" } })
db.test.insert(  { item: "iphone", qty: 100, tags: ["silk"], size: { h: 28, w: 35.5, uom: "cm" } })

db.test.remove( { item: "iphone"} )


mongo cpq_cfit --eval "printjson(db.test.drop( { item: "iphone"} ))" -u administrator-p ohThohmiepoi0que --host dfwlnqcpqdb-02 --port 27019


db.auth('administrator','ohThohmiepoi0que');


 mongo   admin  --eval "printjson(show dbs)"  -u administrator -p ohThohmiepoi0que --host  dfwlnqcpqdb-02 --port  27019

mongo cpq_cfit --eval "printjson(db.Quote.find( { last_priced: { $lt: ISODate("2017-01-15T00:00:00Z") } } ).count())" -u administrator-p ohThohmiepoi0que --host dfwlnqcpqdb-02 --port 27019

************************************************************************************************************************
IP ADDRESS

/sbin/ifconfig

************************************************************************************************************************

ServerStatus

#saving server status to an output file
mongo --host dfwlnpmgdb-03:27018 <<-EOF>>status.txt
do
db.serverStatus()
EOF

************************************************************************************************************************

IMPORT /EXPORT

mongoimport --db test --collection testimport --type csv --headerline --file /home/kolguk01/users.csv --host dfwlnqmgdb-03 --port 27018
mongoexport --db test --collection testusers  --type=csv -o /home/kolguk01/users.csv -h dfwlnqmgdb-03 --port 27018 --fields user,db

************************************************************************************************************************

Reverse Proxy server
MONGOSTAT  --   /spfs/hemi/mongodb-2.4.3/bin/mongostat -u hemi -p T3ddybrS
MONGOTOP  --   /spfs/hemi/mongodb-2.4.3/bin/mongotop -u hemi -p T3ddybrS

************************************************************************************************************************

Mongo Index Creation


Always create the indexes with background set to true - if not it will create issues for other databases
db.storage.createIndex( { "data.listingId": 1 } , { background: true } )

************************************************************************************************************************

Check connections

db.currentOp(true)

************************************************************************************************************************

Print mongo output to a text file

Vi  connections.js  ==> printjson( db.currentOp(true) )

Printjson (command to execute)


mongo --port 27018 /home/kolguk01/connections.js > connection.json

************************************************************************************************************************

MongoDB Profiling



Steps:

  1. Remove the node from Replica set
  2. Stop the mongod process
  1. Make changes to the config file

  1. Add the below lines
operationProfiling:
   slowOpThresholdMs: 100
   mode: slowOp

  1. Start the mongod process
  2. Now create the new system.profile  collection by dropping the existing one

db.setProfilingLevel(0)
db.system.profile.drop()
db.createCollection( "system.profile", { capped: true, size:400000000 } ) -- 400 MB
db.setProfilingLevel(1)
  1. Add the node to the replica set


Commands to use


Use db
Db.getProfilingLevel()

Use db
Db.setProfilingLevel(1) (2) (0)

This will create the system.profile() collection and captures the query information

Create the collection with 1 GB capped size:

db.createCollection( "system.profile", { capped: true, size: 1073741824 } )


Set Read Preferrence

db.getMongo().setReadPref('secondaryPreferred')

Comments

Post a Comment

Popular posts from this blog

Netezza Commands

-
Table Size  select tablename, used_bytes/pow(1024,4) as used_TB   from _v_table_storage_stat  where tablename  in (  select tablename from _v_table where tablename not like  '_VT_%' and tablename not like '_T_%'); Objects owned by a user: select o.objname, d.database, oc.classname, u.usename from _t_object o, _t_user u, _t_object_classes oc, _v_database d where u.usename='username' and u.usesysid=o.objowner and o.objclass=oc.objclass and o.objdb=d.objid; Tip: Use NZ_HELP to get   syntax of all commands Like -    nz_help | grep grant [nz@dfw86inzb-04 ~]$ nz_help | grep grant nz_db_group_access_listing           To show what groups have been granted access to what databases. nz_db_user_access_listing            To show what users have been granted access to what databases. nz_ddl_grant_group    ...
Read more

MS SQL Server Tips

-
Image
Unable to Shrink the DataFile If you are unable to shrink the datafile then try the following Error :  File ID 3 of database ID 8 cannot be shrunk as it is either being shrunk by another process or is empty. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Solution:   Add at least 2-3 MB to the data file and start shrinking again. Index Fragmentation Level SELECT distinct dbschemas.[name] as 'Schema', dbtables.[name] as 'Table',  dbindexes.[name] as 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count, i.rowcnt as 'totalrows' FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats....
Read more
-
Maintenacne Plan creation USE [msdb] GO DECLARE @sqlInstance varchar(20) SET @sqlInstance = '$(instanceName)' DECLARE @BackUpSubPlan sysname SET @BackUpSubPlan = 'Back Up Database Task' DECLARE @BackUpJobName sysname SET @BackUpJobName = 'System databases.' + @BackUpSubPlan DECLARE @ChkDbSubPlan sysname SET @ChkDbSubPlan = 'Check Database Integrity Task' DECLARE @ChkDbJobName sysname SET @ChkDbJobName = 'System databases.' + @ChkDbSubPlan DECLARE @RebuildIndexSubPlan sysname SET @RebuildIndexSubPlan = 'Rebuild Index Task' DECLARE @RebuildIndexJobName sysname SET @RebuildIndexJobName = 'System databases.' + @RebuildIndexSubPlan DECLARE @UpdStatsSubPlan sysname SET @UpdStatsSubPlan = 'Update Statistics Task' DECLARE @UpdStatsJobName sysname SET @UpdStatsJobName = 'System databases.' + @UpdStatsSubPlan DECLARE @HistClnSubPlan sysname SET @HistClnSubPlan = 'History Cleanup Task' ...
Read more