반응형

Database, Collection, Index 정보 조회 (기본 인덱스인 _id_ 정보는 제외)

 

Database Info

 

dbcnt = 1

dbTotalcnt = db.getMongo().getDBNames().length

db.adminCommand({ "listDatabases": 1}).databases.forEach(function(d) {

   mdb = db.getSiblingDB(d.name);

   dbobj = mdb.stats(1024*1024);

   if ( dbcnt == 1 ) {

      print( '----------------------------------------------------------------------------------------------------------------------------------------------------') ;

      print( ' Summary ') ;

      print( '----------------------------------------------------------------------------------------------------------------------------------------------------') ;

   }

   print( ' DB : ' , dbobj.db.padEnd(20,' ') ,

          ' | Collections : ' , dbobj.collections ,

          ' | Views : ' , dbobj.views ,

          ' | Indexes : ' , dbobj.indexes ,

          ' | DataSize(M) : ' , dbobj.dataSize.toFixed(7) ,

          ' | IndexSize(M) : ' , dbobj.indexSize.toFixed(7));

   if ( dbcnt == dbTotalcnt) {

      print( '----------------------------------------------------------------------------------------------------------------------------------------------------') ;

   }

   dbcnt += 1 ;

});

 

출력 결과

 

----------------------------------------------------------------------------------------------------------------------------------------------------

 Summary

----------------------------------------------------------------------------------------------------------------------------------------------------

 DB :  100YWeatherSmall      | Collections :  1  | Views :  0  | Indexes :  1  | DataSize(M) :  327.7806273  | IndexSize(M) :  2.1914063

 DB :  3db                   | Collections :  1  | Views :  0  | Indexes :  1  | DataSize(M) :  0.0000324  | IndexSize(M) :  0.0195313

 DB :  admin                 | Collections :  3  | Views :  0  | Indexes :  3  | DataSize(M) :  0.0029860  | IndexSize(M) :  0.1054688

 DB :  citibike              | Collections :  1  | Views :  0  | Indexes :  1  | DataSize(M) :  834.7753258  | IndexSize(M) :  19.1250000

 DB :  city                  | Collections :  1  | Views :  0  | Indexes :  1  | DataSize(M) :  3.2328215  | IndexSize(M) :  0.0195313

 DB :  config                | Collections :  1  | Views :  0  | Indexes :  2  | DataSize(M) :  0.0001249  | IndexSize(M) :  0.0585938

 DB :  local                 | Collections :  1  | Views :  0  | Indexes :  1  | DataSize(M) :  0.0851641  | IndexSize(M) :  0.0351563

 DB :  m201                  | Collections :  2  | Views :  0  | Indexes :  4  | DataSize(M) :  191.6917276  | IndexSize(M) :  11.8007813

 DB :  m312                  | Collections :  2  | Views :  0  | Indexes :  2  | DataSize(M) :  1.0167627  | IndexSize(M) :  0.0429688

 DB :  results               | Collections :  2  | Views :  0  | Indexes :  2  | DataSize(M) :  0.1049337  | IndexSize(M) :  0.0429688

 DB :  scott                 | Collections :  4  | Views :  0  | Indexes :  4  | DataSize(M) :  0.0055571  | IndexSize(M) :  0.0781250

 DB :  ships                 | Collections :  1  | Views :  0  | Indexes :  1  | DataSize(M) :  3.6389389  | IndexSize(M) :  0.1210938

 DB :  test                  | Collections :  16  | Views :  0  | Indexes :  20  | DataSize(M) :  7.0239134  | IndexSize(M) :  2.1640625

 DB :  testdb                | Collections :  2  | Views :  0  | Indexes :  2  | DataSize(M) :  0.0001926  | IndexSize(M) :  0.0546875

 DB :  tpcc                  | Collections :  8  | Views :  0  | Indexes :  19  | DataSize(M) :  523.4558897  | IndexSize(M) :  28.7070313

 DB :  video                 | Collections :  2  | Views :  0  | Indexes :  2  | DataSize(M) :  1.5150375  | IndexSize(M) :  0.0742188

----------------------------------------------------------------------------------------------------------------------------------------------------

 

Collection Info

 

db.adminCommand({ "listDatabases": 1}).databases.forEach(function(d) {

   mdb = db.getSiblingDB(d.name);

   print( ' ') ;

   print( '--------------------' ) ;

   print( ' ' , mdb ) ;

   print( '--------------------' ) ;

   mdb.getCollectionNames().forEach(function(col) {

      colstat = mdb[col].stats(1024*1024) ;

      colsize = colstat.size ;

      colcnt = colstat.count ;

      colindexcnt = colstat.nindexes ;

      print( ' ' );

      print( '  ' , col , '\n    Size(M) : ' , colsize , ' documentcount : ' , colcnt , '\n    IndexCount : ' , colindexcnt );

      mdb[col].getIndexes().forEach(function(idx) {

         iname = idx.name ;

         ikey = tojson(idx.key) ;

         if ( iname !== '_id_') {

            print( '      - name : ' , iname , ' keys : ' , ikey);

         }

      });

   });

});

 

출력결과

 

--------------------

  100YWeatherSmall

--------------------

 

   data

    Size(M) :  327  documentcount :  250000

    IndexCount :  1

 

--------------------

  3db

--------------------

 

   tab1

    Size(M) :  0  documentcount :  1

    IndexCount :  1

 

--------------------

  admin

--------------------

 

   system.profile

    Size(M) :  0  documentcount :  1

    IndexCount :  0

 

   system.users

    Size(M) :  0  documentcount :  4

    IndexCount :  2

      - name :  user_1_db_1  keys :  { "user" : 1, "db" : 1 }

 

   system.version

    Size(M) :  0  documentcount :  2

    IndexCount :  1

 

--------------------

  citibike

--------------------

 

   trips

    Size(M) :  834  documentcount :  1990273

    IndexCount :  1

 

--------------------

  city

--------------------

 

   neighborhoods

    Size(M) :  3  documentcount :  195

    IndexCount :  1

 

--------------------

  config

--------------------

 

   system.sessions

    Size(M) :  0  documentcount :  1

    IndexCount :  2

      - name :  lsidTTLIndex  keys :  { "lastUse" : 1 }

 

--------------------

  local

--------------------

 

   startup_log

    Size(M) :  0  documentcount :  51

    IndexCount :  1

 

--------------------

  m201

--------------------

 

   people

    Size(M) :  18  documentcount :  50474

    IndexCount :  3

      - name :  ssn_1  keys :  { "ssn" : 1 }

      - name :  ssn_1_birthday_1  keys :  { "ssn" : 1, "birthday" : 1 }

 

   restaurants

    Size(M) :  173  documentcount :  1000000

    IndexCount :  1

 

--------------------

  m312

--------------------

 

   sampleWorkload

    Size(M) :  0  documentcount :  262

    IndexCount :  2

      - name :  firstNumber_1  keys :  { "firstNumber" : 1 }

 

   system.profile

    Size(M) :  0  documentcount :  791

    IndexCount :  0

 

--------------------

  results

--------------------

 

   scores

    Size(M) :  0  documentcount :  1127

    IndexCount :  1

 

   surveys

    Size(M) :  0  documentcount :  424

    IndexCount :  1

 

--------------------

  scott

--------------------

 

   dept

    Size(M) :  0  documentcount :  4

    IndexCount :  1

 

   emp

    Size(M) :  0  documentcount :  14

    IndexCount :  1

 

   empjoindept

    Size(M) :  0  documentcount :  14

    IndexCount :  1

 

   salgrade

    Size(M) :  0  documentcount :  5

    IndexCount :  1

 

--------------------

  ships

--------------------

 

   shipwrecks

    Size(M) :  3  documentcount :  11095

    IndexCount :  1

 

--------------------

  test

--------------------

 

   arraytest

    Size(M) :  0  documentcount :  2

    IndexCount :  1

 

   arraytest1

    Size(M) :  0  documentcount :  4

    IndexCount :  1

 

   blog

    Size(M) :  0  documentcount :  2

    IndexCount :  1

 

   colCapped

    Size(M) :  0  documentcount :  4

    IndexCount :  1

 

   exists

    Size(M) :  0  documentcount :  4

    IndexCount :  1

 

   intest

    Size(M) :  0  documentcount :  3

    IndexCount :  1

 

   myMovies

    Size(M) :  0  documentcount :  4

    IndexCount :  1

 

   retest

    Size(M) :  0  documentcount :  2

    IndexCount :  1

 

   signs

    Size(M) :  0  documentcount :  0

    IndexCount :  2

      - name :  sign_text_1  keys :  { "sign_text" : 1 }

 

   sorttest

    Size(M) :  0  documentcount :  5

    IndexCount :  1

 

   sorttest1

    Size(M) :  0  documentcount :  3

    IndexCount :  1

 

   sorttest2

    Size(M) :  0  documentcount :  4

    IndexCount :  1

 

   test

    Size(M) :  7  documentcount :  74907

    IndexCount :  3

      - name :  name_1_member_1  keys :  { "name" : 1, "member" : 1 }

      - name :  member2_1  keys :  { "member2" : 1 }

 

   test1

    Size(M) :  0  documentcount :  4

    IndexCount :  1

 

   text

    Size(M) :  0  documentcount :  1

    IndexCount :  2

      - name :  desc_text  keys :  { "_fts" : "text", "_ftsx" : 1 }

 

   upsert

    Size(M) :  0  documentcount :  4

    IndexCount :  1

 

--------------------

  testdb

--------------------

 

   test1

    Size(M) :  0  documentcount :  1

    IndexCount :  1

 

   testdbtab1

    Size(M) :  0  documentcount :  3

    IndexCount :  1

 

--------------------

  tpcc

--------------------

 

   CUSTOMER

    Size(M) :  97  documentcount :  120000

    IndexCount :  4

      - name :  C_W_ID_1_C_D_ID_1_C_ID_1  keys :  { "C_W_ID" : 1, "C_D_ID" : 1, "C_ID" : 1 }

      - name :  C_W_ID_1_C_D_ID_1_C_LAST_1  keys :  { "C_W_ID" : 1, "C_D_ID" : 1, "C_LAST" : 1 }

      - name :  C_CITY_1  keys :  { "C_CITY" : 1 }

 

   DISTRICT

    Size(M) :  0  documentcount :  40

    IndexCount :  2

      - name :  D_W_ID_1_D_ID_1_D_NEXT_O_ID_1_D_TAX_1  keys :  { "D_W_ID" : 1, "D_ID" : 1, "D_NEXT_O_ID" : 1, "D_TAX" : 1 }

 

   HISTORY

    Size(M) :  17  documentcount :  120000

    IndexCount :  1

 

   ITEM

    Size(M) :  14  documentcount :  100000

    IndexCount :  2

      - name :  I_W_ID_1_I_ID_1  keys :  { "I_W_ID" : 1, "I_ID" : 1 }

 

   NEW_ORDER

    Size(M) :  2  documentcount :  36000

    IndexCount :  2

      - name :  NO_W_ID_1_NO_D_ID_1_NO_O_ID_1  keys :  { "NO_W_ID" : 1, "NO_D_ID" : 1, "NO_O_ID" : 1 }

 

   ORDERS

    Size(M) :  178  documentcount :  120000

    IndexCount :  3

      - name :  O_W_ID_1_O_D_ID_1_O_ID_1_O_C_ID_1  keys :  { "O_W_ID" : 1, "O_D_ID" : 1, "O_ID" : 1, "O_C_ID" : 1 }

      - name :  O_C_ID_1_O_D_ID_1_O_W_ID_1_O_ID_-1_O_CARRIER_ID_1_O_ENTRY_ID_1  keys :  {

"O_C_ID" : 1,

"O_D_ID" : 1,

"O_W_ID" : 1,

"O_ID" : -1,

"O_CARRIER_ID" : 1,

"O_ENTRY_ID" : 1

}

 

   STOCK

    Size(M) :  213  documentcount :  400000

    IndexCount :  3

      - name :  S_W_ID_1_S_I_ID_1_S_QUANTITY_1  keys :  { "S_W_ID" : 1, "S_I_ID" : 1, "S_QUANTITY" : 1 }

      - name :  S_I_ID_1  keys :  { "S_I_ID" : 1 }

 

   WAREHOUSE

    Size(M) :  0  documentcount :  4

    IndexCount :  2

      - name :  W_ID_1_W_TAX_1  keys :  { "W_ID" : 1, "W_TAX" : 1 }

 

--------------------

  video

--------------------

 

   movieDetails

    Size(M) :  1  documentcount :  2295

    IndexCount :  1

 

   reviews

    Size(M) :  0  documentcount :  20

    IndexCount :  1

 

 

Index Create Statement

 

indexcnt = 1

beforeDB = ''

db.adminCommand({ "listDatabases": 1}).databases.forEach(function(d) {

   mdb = db.getSiblingDB(d.name);

   if ( indexcnt == 1 ) {

      print( '-------------------------------------------' );

  print( ' Index Create Scrtipts (exclude _id_ index)' );

  print( '-------------------------------------------' );

   }

   nowDB = mdb ;

   if ( mdb == 'admin' ) { return true; }

   if ( mdb == 'local' ) { return true; }

   if ( mdb == 'config' ) { return true; }

   mdb.getCollectionNames().forEach(function(col) {

      mdb[col].getIndexes().forEach(function(idx) {

         iname = idx.name ;

         ikey = tojson(idx.key) ;

         if ( iname !== '_id_') {

            if ( beforeDB !== nowDB ) print(' DB : ' , mdb ) ;

          print( '   use ' + mdb , '\n   db.' + col + '.createIndex( ' + ikey + ' ) ');

          beforeDB = mdb ;

         }

      });

 

   });

   indexcnt += 1 ;

});

 

출력 결과

 

-------------------------------------------

 Index Create Scrtipts (exclude _id_ index)

-------------------------------------------

 DB :  m201

   use m201

   db.people.createIndex( { "ssn" : 1 } )

   use m201

   db.people.createIndex( { "ssn" : 1, "birthday" : 1 } )

 DB :  m312

   use m312

   db.sampleWorkload.createIndex( { "firstNumber" : 1 } )

 DB :  test

   use test

   db.signs.createIndex( { "sign_text" : 1 } )

   use test

   db.test.createIndex( { "name" : 1, "member" : 1 } )

   use test

   db.test.createIndex( { "member2" : 1 } )

   use test

   db.text.createIndex( { "_fts" : "text", "_ftsx" : 1 } )

 DB :  tpcc

   use tpcc

   db.CUSTOMER.createIndex( { "C_W_ID" : 1, "C_D_ID" : 1, "C_ID" : 1 } )

   use tpcc

   db.CUSTOMER.createIndex( { "C_W_ID" : 1, "C_D_ID" : 1, "C_LAST" : 1 } )

   use tpcc

   db.CUSTOMER.createIndex( { "C_CITY" : 1 } )

   use tpcc

   db.DISTRICT.createIndex( { "D_W_ID" : 1, "D_ID" : 1, "D_NEXT_O_ID" : 1, "D_TAX" : 1 } )

   use tpcc

   db.ITEM.createIndex( { "I_W_ID" : 1, "I_ID" : 1 } )

   use tpcc

   db.NEW_ORDER.createIndex( { "NO_W_ID" : 1, "NO_D_ID" : 1, "NO_O_ID" : 1 } )

   use tpcc

   db.ORDERS.createIndex( { "O_W_ID" : 1, "O_D_ID" : 1, "O_ID" : 1, "O_C_ID" : 1 } )

   use tpcc

   db.ORDERS.createIndex( {

"O_C_ID" : 1,

"O_D_ID" : 1,

"O_W_ID" : 1,

"O_ID" : -1,

"O_CARRIER_ID" : 1,

"O_ENTRY_ID" : 1

} )

   use tpcc

   db.STOCK.createIndex( { "S_W_ID" : 1, "S_I_ID" : 1, "S_QUANTITY" : 1 } )

   use tpcc

   db.STOCK.createIndex( { "S_I_ID" : 1 } )

   use tpcc

   db.WAREHOUSE.createIndex( { "W_ID" : 1, "W_TAX" : 1 } )

 

반응형

+ Recent posts