{"id":154,"date":"2017-04-13T17:07:50","date_gmt":"2017-04-13T09:07:50","guid":{"rendered":"https:\/\/blog.espnlol.com\/?p=154"},"modified":"2017-04-13T17:07:50","modified_gmt":"2017-04-13T09:07:50","slug":"mongodb%e6%85%a2%e6%9f%a5%e8%af%a2mongodb-slow-query-log","status":"publish","type":"post","link":"https:\/\/blog.espnlol.com\/?p=154","title":{"rendered":"Mongodb\u6162\u67e5\u8be2(Mongodb slow query log)"},"content":{"rendered":"

#\u8bbe\u7f6e\u6162\u67e5\u8be2<\/p>\n

Profiling Levels\uff1a<\/p>\n

1\u00a0resume
\n0 – the profiler is off, does not collect any data.
\n1 – collects profiling data for slow operations only. By default slow operations are those slower than 100 milliseconds.
\nYou can modify the threshold for \u201cslow\u201d operations with the slowms runtime option or the setParameter command. See the Specify the Threshold for Slow Operations section for more information.
\n2 – collects profiling data for all database operations.<\/p>\n

db.setProfilingLevel(1,500);\r\ndb.setProfilingLevel(2,500);\r\nPRIMARY> db.setProfilingLevel(1,500);\r\n{ \"was\" : 0, \"slowms\" : 500, \"ok\" : 1 }\r\nPRIMARY> \r\nPRIMARY> \r\nPRIMARY> \r\nPRIMARY> db.setProfilingLevel(1,500);\r\n{ \"was\" : 1, \"slowms\" : 500, \"ok\" : 1 }\r\nPRIMARY> \r\nPRIMARY> \r\nPRIMARY><\/pre>\n
\u4e3a\u4ec0\u4e48\u9700\u8981set2\u6b21\u624d\u80fd\u751f\u6548\uff1f
\n\u89e3\u91ca\uff1a
\n0\u4ee3\u8868\u5173\u95ed\uff0c1\u4ee3\u8868\u53ea\u8bb0\u5f55slowlog\uff0c2\u4ee3\u8868\u8bb0\u5f55\u6240\u6709\u64cd\u4f5c\uff0c\u8fd9\u91cc\u8bbe\u7f6e\u6210\u4e86500\uff0c\u5373500ms\u3002<\/p>\n
#\u68c0\u67e5slowlog\u72b6\u6001<\/p>\n
PRIMARY> db.getProfilingStatus();\r\n{ \"was\" : 2, \"slowms\" : 500 }\r\nPRIMARY> db.getProfilingLevel();\r\nPRIMARY><\/pre>\n
#\u67e5\u770b\u65e5\u5fd7<\/p>\n
db.system.profile.find().sort({$natural:-1})  \r\nPRIMARY> db.system.profile.find().sort({$natural:-1})  \r\n{ \"ts\" : ISODate(\"2013-05-14T08:13:37.098Z\"), \"op\" : \"insert\", \"ns\" : \"test.tickets\", \"millis\" : 0, \"client\" : \"127.0.0.1\", \"user\" : \"\" }\r\n{ \"ts\" : ISODate(\"2013-05-14T08:13:37.098Z\"), \"op\" : \"insert\", \"ns\" : \"test.tickets\", \"millis\" : 0, \"client\" : \"127.0.0.1\", \"user\" : \"\" }\r\n{ \"ts\" : ISODate(\"2013-05-14T08:13:37.098Z\"), \"op\" : \"insert\", \"ns\" : \"test.tickets\", \"millis\" : 0, \"client\" : \"127.0.0.1\", \"user\" : \"\" }\r\nPRIMARY><\/pre>\n
ts\uff1a\u65f6\u95f4\u6233
\nop: \u64cd\u4f5c\u7c7b\u578b
\nns\uff1a\u6267\u884c\u64cd\u4f5c\u7684\u5bf9\u8c61\u96c6\u5408
\nmillis\uff1a\u64cd\u4f5c\u6240\u82b1\u65f6\u95f4\uff0c\u6beb\u79d2
\nclient: \u6267\u884c\u64cd\u4f5c\u7684\u5ba2\u6237\u7aef
\nuser: \u6267\u884c\u64cd\u4f5c\u7684mongodb\u8fde\u63a5\u7528\u6237<\/p>\n
#\u5728mongodb\u542f\u52a8\u4e4b\u521d\u8bbe\u7f6eslow<\/p>\n
\u76f4\u63a5\u52a0\u5728\u542f\u52a8\u547d\u4ee4\u4e2d\uff1amongod –profile=1 –slowms=1000\u6216\u5728\u914d\u7f6e\u6587\u4ef6\u4e2d\u5199\u597d \u7528-f\u5f3a\u5236\u52a0\u8f7d\u914d\u7f6e\u6587\u4ef6\u542f\u52a8mongodb<\/p>\n
profile=1\r\nslowms=1000<\/pre>\n
[] \u5728replicaset\u4e2d\uff0c\u5fc5\u987b\u628a\u4e00\u4e2a\u4e2ainstance\u90fd\u751f\u6548\u624d\u884c\u3002\u6bcf\u4e00\u4e2a\u90fd\u9700\u8981\u751f\u6548\u4e00\u6b21\u3002<\/p>\n
#\u770b\u65e5\u5fd7<\/p>\n
db.system.profile.find().limit(10).sort( { ts : -1 } ).pretty();\r\ndb.system.profile.find().limit(10).sort( { ts : 1 } ).pretty();<\/pre>\n
#Disable Profiling<\/p>\n
To disable profiling, use the following helper in the mongo shell: set the default value 0;\r\ndb.setProfilingLevel(0)<\/pre>\n
#\u5f53profile\u8868\u8fc7\u5c0f\u65f6\uff0c\u8c03\u6574\u8868\u5927\u5c0f\u4e3a4MB<\/p>\n
db.setProfilingLevel(0)   -- profile\u5931\u6548\r\ndb.system.profile.drop()   -- \u5220\u9664\r\ndb.createCollection( \"system.profile\", { capped: true, size:4000000 } )    -- \u91cd\u5efa\r\ndb.setProfilingLevel(1)  -- profile\u751f\u6548<\/pre>\n
#\u663e\u793a\u6700\u65b0\u76845\u6761\u64cd\u4f5c\u8bb0\u5f55<\/p>\n
db.system.profile.find().sort({millis:-1}).limit(10);\r\ndb.system.profile.find( { millis : { $gt : 5 } } ).pretty();\r\n{\r\n        \"ts\" : ISODate(\"2013-01-16T18:26:18.041Z\"),\r\n        \"op\" : \"query\",  -- \u6267\u884c\u7c7b\u578b\r\n        \"ns\" : \"ssodatabase.exchangetickets\", -- \u6267\u884ccollection\u540d\u79f0\r\n        \"query\" : {\r\n                \"xid\" : \"X15e1481688254bc9a94701b3aa9e7abc627971358360754783\"\r\n        },  -- \u6267\u884c\u7684\u5185\u5bb9\r\n        \"ntoreturn\" : 1,\r\n        \"nscanned\" : 382793,\r\n        \"nreturned\" : 1,\r\n        \"responseLength\" : 605,\r\n        \"millis\" : 5841, -- \u6267\u884c\u7684\u65f6\u95f4\r\n        \"client\" : \"10.100.10.161\",  -- \u6267\u884c\u7684\u5ba2\u6237\u7aef\r\n        \"user\" : \"\" -- \u6267\u884c\u7684mongo\u7528\u6237\r\n}<\/pre>\n
\u89c2\u5bdf\u7ed3\u679c\u4e2d\u7684”query”\u5b57\u6bb5\u3002\u00a0 \u6ca1\u6709\u76f4\u63a5db.test.insert({xxxxxxx…..})\u8fd9\u6837\u663e\u793a\u7684\uff0c\u9700\u8981\u4f60\u81ea\u5df1\u6839\u636equery\u5b57\u6bb5\u53bb\u62fc\u63a5\u53d6\u503c.<\/p>\n
#\u666e\u901a\u7684\u65f6\u95f4\u77ed\u67e5\u8be2<\/p>\n
db.system.profile.find(\r\n{\r\n ts : {\r\n       $gt : new ISODate(\"2013-05-09T03:00:00Z\") ,\r\n       $lt : new ISODate(\"2013-05-17T03:40:00Z\")\r\n      }\r\n}\r\n                      ).pretty();\r\n      run the command, follows:\r\n       PRIMARY> db.system.profile.find(\r\n... {\r\n...  ts : {\r\n...        $gt : new ISODate(\"2013-05-09T03:00:00Z\") ,\r\n...        $lt : new ISODate(\"2013-05-17T03:40:00Z\")\r\n...       }\r\n... }\r\n...).pretty();\r\n{\r\n        \"ts\" : ISODate(\"2013-05-14T08:36:58.691Z\"),\r\n        \"op\" : \"query\",\r\n        \"ns\" : \"ssodatabase.digitalriverorderdetails\",\r\n        \"query\" : {\r\n                \"invoiceId\" : \"15539232823\"\r\n        },\r\n        \"ntoreturn\" : 1,\r\n        \"nscanned\" : 1,\r\n        \"nreturned\" : 1,\r\n        \"responseLength\" : 1213,\r\n        \"millis\" : 663,\r\n        \"client\" : \"10.100.10.162\",\r\n        \"user\" : \"admin\"\r\n}\r\n{\r\n        \"ts\" : ISODate(\"2013-05-14T09:17:58.911Z\"),\r\n        \"op\" : \"insert\",\r\n        \"ns\" : \"ssodatabase.tickets\",\r\n        \"millis\" : 527,\r\n        \"client\" : \"10.100.10.154\",\r\n        \"user\" : \"admin\"\r\n}\r\n{\r\n        \"ts\" : ISODate(\"2013-05-14T09:20:58.648Z\"),\r\n        \"op\" : \"insert\",\r\n        \"ns\" : \"ssodatabase.tickets\",\r\n        \"millis\" : 529,\r\n        \"client\" : \"10.100.10.153\",\r\n        \"user\" : \"admin\"\r\n}<\/pre>\n
#\u5e26\u6267\u884c\u65f6\u95f4\u5012\u5e8f\u6392\u5e8f,\u5e76\u4e14\u53ea\u8f93\u51fa\u7528\u6237\u4fe1\u606f<\/p>\n
db.system.profile.find(\r\n{\r\n  ts : {\r\n        $gt : new ISODate(\"2013-05-09T03:00:00Z\")  ,\r\n        $lt : new ISODate(\"2013-05-17T09:40:00Z\")\r\n       }\r\n},\r\n{ user : 1 } -- \u53ea\u8f93\u51fa\u7528\u6237\u4fe1\u606f\r\n                      ).sort( { millis : -1 } ) -- \u5012\u5e8f\u6392\u5e8f\r\n \r\nPRIMARY> db.system.profile.find(\r\n... {\r\n...   ts : {\r\n...         $gt : new ISODate(\"2013-05-09T03:00:00Z\")  ,\r\n...         $lt : new ISODate(\"2013-05-17T09:40:00Z\")\r\n...        }\r\n... },\r\n... { user : 1 } \r\n...).sort( { millis : -1 } )\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }\r\n{ \"user\" : \"admin\" }<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
#\u8bbe\u7f6e\u6162\u67e5\u8be2 Profiling Levels\uff1a 1\u00a0resume 0 &#82 … \u7ee7\u7eed\u9605\u8bfb →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24],"tags":[],"class_list":["post-154","post","type-post","status-publish","format-standard","hentry","category-mongodb"],"_links":{"self":[{"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=\/wp\/v2\/posts\/154","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=154"}],"version-history":[{"count":1,"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=\/wp\/v2\/posts\/154\/revisions"}],"predecessor-version":[{"id":155,"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=\/wp\/v2\/posts\/154\/revisions\/155"}],"wp:attachment":[{"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=154"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=154"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.espnlol.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=154"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}