20110514 mongo dbチューニング

20110514 mongo dbチューニング

• 1.
  MongoDB id:matsuou1
• 2.
  !   !   !  !   !   !   Tips !  
• 3.
  !   ID matsuou1 !   !   !   !   !   Oracle PostgreSQL !   MongoDB Hadoop R
• 4.
  1.  2.  3.  4. 
• 6.
  !   !  RDBMS !  
• 7.
  !   !  
• 8.
  MongoDB !   !   CPU !   !   JOIN !  
• 9.
  •  •  100 insert update delete •  •  nscanned 10 • 
• 10.
  db/queryoptimizer.cpp
• 12.
  !   explain > db.logs.find({uid:"a10Sty5jqqiyxxxx2"}).explain(); { "cursor" : "BasicCursor", "nscanned" : 2669511, "nscannedObjects" : 2669511, "n" : 3, "millis" : 6100, "nYields" : 0, "nChunkSkips" : 0, "isMultiKey" : false, "indexOnly" : false, "indexBounds" : { } }
• 13.
  cursor BasicCursor BasicCursor or BtreeCursor nscanned 14 nscannedObjects 14 n 14 millis 9 ms nYields 0 nChunkSkips 0 Chunk isMultiKey false indexOnly false indexBounds scanAndOrder allPlans
• 14.
  ( ) > db.logs.find({uid:"a10Sty5jqqiyxxxx2"}).explain(); { "cursor" : "BasicCursor", "nscanned" : 2669511, "nscannedObjects" : 2669511, "n" : 3, "millis" : 6100, "nYields" : 0, 6100ms "nChunkSkips" : 0, 3 266 "isMultiKey" : false, "indexOnly" : false, "indexBounds" : { } }
• 15.
  ( ) > db.logs.find({uid:"b3iVxMGIEc3xxxxY"}).explain(); { "cursor" : "BtreeCursor uid_1", "nscanned" : 3, "nscannedObjects" : 3, "n" : 3, "millis" : 6, ”uid_1” "nYields" : 0, 6ms "nChunkSkips" : 0, 3 3 "isMultiKey" : false, "indexOnly" : false, "indexBounds" : { "uid" : [ [ "b3iVxMGIEc3xxxxY", "b3iVxMGIEc3xxxxY" ] ] } }
• 17.
  !   ! slowms !   ! db.setProfilingLevel(level,slowms) !  
• 18.
  slowms !   mongod slowms ms mongod mongod --logpath=/var/log/mongodb/mongodb.log --logappend –slowms 500 Sat May 14 02:25:53 [conn16] query logdb.$cmd ntoreturn:1 command: { count: "logs", query: { path: "/index.html" }, fields: {} } reslen:64 3116ms
• 19.
  setProfilingLevel setProfilingLevel (level,slowms) > db.setProfilingLevel(1,500); { "was" : 1, "slowms" : 100, "ok" : 1 } 100ms
• 20.
  setProfilingLevel system.profile > db.system.profile.find(); { "ts" : ISODate("2011-05-01T03:40:04.462Z"), "info" : "query logdb.logs reslen:192 nscanned:2669511 nquery: { uid: "b2kwyYCXn3b5Scpr" } nreturned:1 6686ms", "millis" : 6686 } ts millis info query query query nscanned reslen nreturned
• 21.
  db.currentOp opid ID KillOp op query update ns query lockType waitingForLock client desc
• 22.
  > db.currentOp(); { "inprog" : [ { "opid" : 272, "active" : true, "lockType" : "read", "waitingForLock" : false, "secs_running" : 20, "op" : "query", "ns" : "logdb.logs", "query" : { "count" : "logs", "query" : { "month" : "2010/02" }, "fields" : { } }, "client" : "127.0.0.1:59394", "desc" : "conn" }]}
• 23.
  slowms db.setProfilingLevel collection system.profile capped collection db.currentOp MongoDB
• 24.
  Tips
• 25.
  !   • db.c.find( {x:10,y:20} ), using index {x:1,y:1} • db.c.find( {x:10,y:20} ), using index {x:1,y:-1} • db.c.find( {x:{$in:[10,20]},y:20} ), using index {x:1,y:1} • db.c.find().sort( {x:1,y:1} ), using index {x:1,y:1} • db.c.find().sort( {x:-1,y:1} ), using index {x:1,y:-1} • db.c.find( {x:10} ).sort( {y:1} ), using index {x: 1,y:1}
• 26.
  hint !  hint !  
• 27.
  hint > db.logs.find({uid:"f2RNwtj34m3xxxxB", path : "/index.html"}).hint({uid:1}).explain(); { "cursor" : "BtreeCursor uid_1", "nscanned" : 95, "nscannedObjects" : 95, "n" : 35, "millis" : 3, "nYields" : 0, "nChunkSkips" : 0, "isMultiKey" : false, "indexOnly" : false, "indexBounds" : { "uid" : [ [ "f2RNwtj34m3xxxxB", "f2RNwtj34m3xxxxB" ] ] } }
• 28.
  hint > db.logs.find({uid:"f2RNwtj34m3xxxxB", path : "/index.html"}).hint({uid:1 , path:1}).explain(); { "cursor" : "BtreeCursor uid_1_path_1", "nscanned" : 35, "nscannedObjects" : 35, "n" : 35, "millis" : 0, "nYields" : 0, "nChunkSkips" : 0, "isMultiKey" : false, "indexOnly" : false, "indexBounds" : { "uid" : [ [ "f2RNwtj34m3xxxxB", "f2RNwtj34m3xxxxB" ] ], ( ) } }
• 29.
  mongostat mongostat
• 30.
  mongostat inserts insert / query query / update update / delete delete / getmore getmore / command command / flushes fsync flush / mapped Map MB visze MB res MB faults / locked Idx miss qr|qw read | write ar|aw read | write netIn In bits netOut Out bits conn
• 31.
  collection !   getIndexKeys() > db.logs.getIndexKeys(); [ { "_id" : 1 }, { "uid" : 1 }, { "uid" : 1, "path" : 1 } ]
• 32.
  !   db.currentOp opid db.killOp(opid) > db.currentOp(); { "inprog" : [ { "opid" : 308, "active" : true, "lockType" : "read", "waitingForLock" : false, "secs_running" : 11, "op" : "query", "ns" : "logdb.logs", ( ) } ] } > db.killOp(308); { "info" : "attempting to kill op" }
• 33.
  !   !   !   explain !   !   slowms !   setProfilingLevel !   db.currentOP !   Tips !   !   Hint !   mongostat